UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Help With Acces 2016 Search Button, Access 2016    
 
   
Adrian_Corn
post Apr 15 2020, 08:55 AM
Post#1



Posts: 17
Joined: 15-April 20




Good Afternoon

I have created a database that compiles histories of individual Aircraft. All Civil Aircraft carry a "registration", similar to a car registration, and this may change when the aircraft is sold.

On my Database I have a field called "Regn" which is the aircraft's current registration, and then field's called "Prev_Reg1" to "Prev_Reg20" to record the last twenty registration that the aircraft has had

What I would like is one command button on my form, that searches a text box for the field's "Regn" and all the "Prev_Reg1" to "Prev_Reg20" field's ( basically to find a registration either currently used or previously used by an aircraft )

I have looked in the example searches supplied on this site, but cant seem to convert them to work on my database.....

Very Many Thanks

Adrian
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2020, 09:08 AM
Post#2


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


First, Welcome to UtterAccess.

This is an unfortunate start, though: "... field's called "Prev_Reg1" to "Prev_Reg20" to record the last twenty registration that the aircraft has had..."

What you have now is a spreadsheet. It is NOT a relational database application table. That's a common misstep for people new to relational database applications, so we've had a lot of experience helping people recover and correct course.

Here's a series of excellent starter articles on the topic of Normalization.

Here are some blog articles on the problem of Repeating Groups in "spreadsheet style" tables.

Once you have set up a group of properly normalized tables, this search problem will simply go away.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Adrian_Corn
post Apr 15 2020, 09:54 AM
Post#3



Posts: 17
Joined: 15-April 20




Many thanks for the reply

Having read your attached articles i do not think that a database would be the best solution after all, so back to the speadsheet drawing board !!

Many Thanks

Adrian
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2020, 09:56 AM
Post#4


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


On the contrary. A relational database application is precisely the right tool here. It does require an investment in learning, but the rewards are far greater.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Larry Larsen
post Apr 15 2020, 10:24 AM
Post#5


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Adrian

Totally agree with George's recommendations, and yes Access would & will be the right tool..

Post up a image/picture of your table structure and relationships and let's see if we can possibly put right those awful "repeating groups"
welcome2UA.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Adrian_Corn
post Apr 15 2020, 12:00 PM
Post#6



Posts: 17
Joined: 15-April 20




The Database structure is a follow's :-
all are "Short Text" apart from ID which is numeric

ID
Regn
Type
C/No
L/No
Date_Rgd
Owner
Operator
Date_Canx
Reason_Canx
Source
Source_Date
Remarks
Prev_Reg1
Prev_Date1
Prev_Owner1
Prev_Cx1
Prev_Rsn1

Repeated field's until :-

Prev_Reg20
Prev_Date20
Prev_Owner20
Prev_Cx20
Prev_Rsn20
Photo_Link1

to

Photo_Link20
Photo_True1

to

Photo_True20


basically the prev_xxx fields are used to create a history line on the form (see attached)

The photo link fields are for hyperlinks activated by the "Image" button next to the airframe history

I had to add the Photo_True fields to display a simple "x" on the form to show that the image button contained a hyperlink or not

very clunky and very slow.....

Many Thanks

Adrian
Attached File(s)
Attached File  Capture.JPG ( 144.31K )Number of downloads: 5
 
Go to the top of the page
 
Adrian_Corn
post Apr 15 2020, 12:12 PM
Post#7



Posts: 17
Joined: 15-April 20




This attachment gives a clearer picture of the field layouts on the form to illustrate how an airframe record is created...
Attached File(s)
Attached File  Capture_2.JPG ( 196.69K )Number of downloads: 3
 
Go to the top of the page
 
Larry Larsen
post Apr 15 2020, 12:46 PM
Post#8


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


HI Adrian

Many thanks for the details, but if possible I'd like to see what the picture is of your tables & relationships..

In the menu Database Tools / Relationships..

Here you can see those objects, and it's this view/picture that we need to see.. (grab the screen and post it up)

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Adrian_Corn
post Apr 15 2020, 01:48 PM
Post#9



Posts: 17
Joined: 15-April 20



Hi Larry,

Are These what you mean ?

The Tables Tab Shows the screenshots attached, the queries tab is blank, and the both tab shows just Corporate Jets again

Best Regards

Adrian
This post has been edited by Adrian_Corn: Apr 15 2020, 01:50 PM
Attached File(s)
Attached File  Capture_3.JPG ( 67.63K )Number of downloads: 4
Attached File  Capture_4.JPG ( 61.95K )Number of downloads: 1
Attached File  Capture_5.JPG ( 73.51K )Number of downloads: 0
Attached File  Capture_6.JPG ( 39.67K )Number of downloads: 0
 
Go to the top of the page
 
Larry Larsen
post Apr 15 2020, 02:15 PM
Post#10


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Adrian

No... sorry I'm looking to see if you have set up the tables and relationships..

What I was hoping too see was soothing like the attached image..

Or if you like just post the tables from your db.. (zip them up and attached to the post)

thumbup.gif
Attached File(s)
Attached File  2020_04_15_20_11_52.jpg ( 184.78K )Number of downloads: 6
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Adrian_Corn
post Apr 15 2020, 03:21 PM
Post#11



Posts: 17
Joined: 15-April 20




Hi Larry

Please find attached database & form as requested

Regards

Adrian


Attached File(s)
Attached File  Database.zip ( 1.25MB )Number of downloads: 9
 
Go to the top of the page
 
Larry Larsen
post Apr 16 2020, 10:48 AM
Post#12


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Adrian

Sorry I have been long in replying had to "nip" into work this morning..

Thanks for the db, it has helped in some ways to getting to a very basic table & relationship structure..

I have attached a couple of images that show a small detail about how I'm setting up the T&R..

There are a couple of fields in your "big" table that I don't understand with regards to aircraft..

Could you give some details so I can think about how they fit in..

thumbup.gif
Attached File(s)
Attached File  2020_04_16_16_41_48.jpg ( 44.66K )Number of downloads: 3
Attached File  2020_04_16_16_40_56.jpg ( 14.09K )Number of downloads: 1
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Adrian_Corn
post Apr 16 2020, 11:47 AM
Post#13



Posts: 17
Joined: 15-April 20



Hi Larry, Many Thanks for your continued help

Basically explained ( I Hope... )

When an aircraft is built it receives from the manufacturer something called a Construction Number (C/No), this stays with the aircraft throughout it's entire life and never changes, some manufacturers also allocate a line number as well (L/No), but this is only to allocate positions
on the production Line.t

The aircraft also receives a "Registration", throughout the aircraft's life this may change multiple times, for example if the aircraft is sold, if it changes country or even just for Vanities sake.
it is worth noting that an aircraft can change owner and not have to change the registration.

The purpose of all these PREV_ fields is to record each registration change, just to complicate things an aircraft may be sold from one person to another and keep the same registration.

so as an example :-

Bombardier Global Express C/No 9067

Registration Date Owner Date_Canx Reason_Canx
C-GGPZ 12/03/1990 Bombardier Aerospace 12/04/1990 Exported as N700BK { Prev_Reg1,Prev_Date1,Prev_Owner1,Prev_CX1,Prev_Rsn1 }
N700BK 12/04/1990 Some Company 22/08/1991 Change of Owner { Prev_Reg2,Prev_Date2,Prev_Owner2,Prev_CX2,Prev_Rsn2 }
N700BK 22/08/1991 Another Company 20/10/1991 Exported as G-CYEL { Prev_Reg3,Prev_Date3,Prev_Owner3,Prev_CX3,Prev_Rsn3 }
G-CYEL 20/10/1991 Yet Another Company { Prev_Reg4,Prev_Date4,Prev_Owner4,Prev_CX4,Prev_Rsn4 }

Because G-CYEL is the last entry (that is the current Registration), the Date_Canx & Reason_Canx will be blank, because it has not happened yet

So essentially Prev_Reg1 will be the first registration the aircraft had leaving the factory & Prev_Reg20 will be current

Obviously there can be more than 20 events, but 20 is all i could fit on the screen !

I am not sure why there are xx's in the Prev_Reg20 you show in the screenshot, probably leant on the keyboard !

Very Best Regards

Adrian
This post has been edited by Adrian_Corn: Apr 16 2020, 11:48 AM
Go to the top of the page
 
GroverParkGeorge
post Apr 16 2020, 11:59 AM
Post#14


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


Please invest some time studying Roger's series of blogs on correcting the problem of repeating groups. This is exactly the problem you have with registrations. Roger and Larry are trying to explain how to correct it.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Adrian_Corn
post Apr 16 2020, 11:59 AM
Post#15



Posts: 17
Joined: 15-April 20




A more clearer format than Above :-




Attached File(s)
Attached File  Capture.JPG ( 85.24K )Number of downloads: 5
 
Go to the top of the page
 
Larry Larsen
post Apr 16 2020, 04:24 PM
Post#16


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Beginning to get the basic structure, trying to normalize you flat data..

Had to step back a few paces when I found those "hidden" fields..

Will continue tomorrow, lets not worry we got another (3) weeks..
LOL
thumbup.gif
Attached File(s)
Attached File  2020_04_16_22_21_02.jpg ( 96.88K )Number of downloads: 4
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Larry Larsen
post Apr 16 2020, 04:38 PM
Post#17


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Again what do this mean..??
thumbup.gif iconfused.gif
Attached File(s)
Attached File  2020_04_16_22_37_33.jpg ( 10.25K )Number of downloads: 0
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Adrian_Corn
post Apr 17 2020, 02:21 AM
Post#18



Posts: 17
Joined: 15-April 20




Morning Larry,

Again very many thank's for all your help.

This is indicating that the 6th cancellation event in the aircraft’s life was that it was cancelled on the 26/12/2012 (Prev_CX6), and the reason for Cancellation was exported as SP-MRD (Prev_Rsn6)

That basically means that the aircraft has been sold to Poland on the 26/12/2012 as SP-MRD (SP- is the country code for Poland )

SP-MRD is a new registration for this aircraft, (the 7th event in the aircraft’s life) so PREV_Reg7 should now be SP-MRD

Hope This Helps

Best Regards

Adrian
Go to the top of the page
 
Larry Larsen
post Apr 17 2020, 09:27 AM
Post#19


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


HI Adrian

What difference to see between Photo Link & True Photo..??
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Adrian_Corn
post Apr 17 2020, 10:03 AM
Post#20



Posts: 17
Joined: 15-April 20



Hi Larry,

Photo_Link is a Hyperlink field, which is linked to a command button on the form called "Image", this is located to the left of the aircraft history line on the form, the idea being that you could click the image button to display an image for that particular line item

The Photo_True field is quite honestly a bit of a bodge !

I had no way of showing whether I had a picture of that registration or not, so if i pressed the image button, something may or may not happen depending if there was a hyperlink in the photo_Link field or not, so to get around this I created a Photo_True field to display on the form, and if i put an"X" in the field, then i knew that it was worth pressing the image button, if Photo_True was blank on the form then i knew i did not have a photo, so no point in pressing the image button !

an easier way would be to grey out the Image button if no hyperlink existed in Photo_Link, but i did not know how to do this !!

Best Regards

Adrian
This post has been edited by Adrian_Corn: Apr 17 2020, 10:04 AM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 05:07 AM