faraoosiris
asked on
use query on YES/No field ACCESS 2010
I've been given the table tblAltn. It lists destinations for aircraft and the alternate airports. These are different for various aircraft either the runway not long enough or the bearing strenght too low.
I made a very simple form frmAltnSel to get me started and get my bearings. I want to filter the table by destination and aircraft type.
For example for 744, destination EHAM the alternates are EHRD EBBR EHBK EDDG where the B737 has the alternates EHRD EHEH EHGG EBBR.
I started with the TRUE statement in the criteria field of the 744 column in the Query3 design.This gave the wanted result. Changing the criteria in the 744 column to false and True in the 737 column gave a not wanted result.
I think the problem lies in the fact that the frmAltnSel has the same input as the column 744 however both being a different parameter. Text versusYES/No field. I tried iif statements but seemto be lost at the moment how to make this query work.
Any help to get me started would be welcome
I made a very simple form frmAltnSel to get me started and get my bearings. I want to filter the table by destination and aircraft type.
For example for 744, destination EHAM the alternates are EHRD EBBR EHBK EDDG where the B737 has the alternates EHRD EHEH EHGG EBBR.
I started with the TRUE statement in the criteria field of the 744 column in the Query3 design.This gave the wanted result. Changing the criteria in the 744 column to false and True in the 737 column gave a not wanted result.
I think the problem lies in the fact that the frmAltnSel has the same input as the column 744 however both being a different parameter. Text versusYES/No field. I tried iif statements but seemto be lost at the moment how to make this query work.
Any help to get me started would be welcome
Actually, alternates should be one per row and so would be in a separate table. You don't want to define columns that contain multiple values.
ASKER
Hallo Dale,
I attached a sample of the "inherited" table tblAltn to the initial posting.
I attached a sample of the "inherited" table tblAltn to the initial posting.
ASKER
Hallo Pat,
Do you mean I should make an alternate group for all different types instead of putting YES/NO boxes behind one alternate? Can I draw the alternates belonging to one specific type from the initial table? This woukd save considerable time.
Do you mean I should make an alternate group for all different types instead of putting YES/NO boxes behind one alternate? Can I draw the alternates belonging to one specific type from the initial table? This woukd save considerable time.
The destination and alternate tables should look like this:
tblPlaneDestinations
Plane (PK field 1)
Destination (PK field 2)
Sample Data:
Plane, Destination
744, EHAM
737, EHAM
TblAlternateDestinations
Plane (PK field 1, FK to tblPlaneDestinations)
Destination (PK field 2, FK to tblPlaneDestinations)
Alternate (PK field 3)
Sample Data:
Plane, Destination, Alternate
744, EHAM, EHRD
744, EHAM, EBBR
744, EHAM, EHBK
744, EHAM, EDDG
737, EHAM, EHRD
737, EHAM, EHEH
737, EHAM, EHGG
737, EHAM, EBBR
tblPlaneDestinations
Plane (PK field 1)
Destination (PK field 2)
Sample Data:
Plane, Destination
744, EHAM
737, EHAM
TblAlternateDestinations
Plane (PK field 1, FK to tblPlaneDestinations)
Destination (PK field 2, FK to tblPlaneDestinations)
Alternate (PK field 3)
Sample Data:
Plane, Destination, Alternate
744, EHAM, EHRD
744, EHAM, EBBR
744, EHAM, EHBK
744, EHAM, EDDG
737, EHAM, EHRD
737, EHAM, EHEH
737, EHAM, EHGG
737, EHAM, EBBR
ASKER
Good morning Pat,
This morning @Home a gave a glance at your answer and tried to work it out.
PK and FK do mean Primary and Foreign Key correct? In 2 table's I have 2 one to many relationships with 2 primary keys.
I don't get it right. Maybe you can eloborate a little more on the setup of the table's and their relations.
This morning @Home a gave a glance at your answer and tried to work it out.
PK and FK do mean Primary and Foreign Key correct? In 2 table's I have 2 one to many relationships with 2 primary keys.
I don't get it right. Maybe you can eloborate a little more on the setup of the table's and their relations.
PK and FK are primary key and foreign key. The numbers indicate that the PK and FK are multiple columns. To create a multi-column PK, Use the Shift-Click or Cntl-Click to select a group of adjacent columns or several separate columns. A PK or FK can have up to 10 fields. To make the FK, just drag the join lines between each table on each column. When you are doing left or right joins, you will have to change every line of the FK to left or right.
In my description above, I didn't include a simple destination table because I assumed that you already had one. If you don't, you will need that also. Since this is a many-many relationship, the alternate destinations table is the junction table. that joins the plane destination to the destination table to come up with the list of alternates. The alternate destinations will be presented as a subform on the plane/destination main form.
Post what you have and we'll look at it.
In my description above, I didn't include a simple destination table because I assumed that you already had one. If you don't, you will need that also. Since this is a many-many relationship, the alternate destinations table is the junction table. that joins the plane destination to the destination table to come up with the list of alternates. The alternate destinations will be presented as a subform on the plane/destination main form.
Post what you have and we'll look at it.
ASKER
Hallo Pat,
Quite a new way of programming to me. Multiple PF en FK's.
Attched what I made of your suggestions sofar.
Regards
AlternatesLIDO2.accdb
Quite a new way of programming to me. Multiple PF en FK's.
Attched what I made of your suggestions sofar.
Regards
AlternatesLIDO2.accdb
ASKER
Hallo Pat,
I gave your reactions a closer look and started from scratch after a figured out the relations between the tables and the primary and foreign keys.
Attached the solution a came up with. It seems to be working. Would you have a look please.
Database31.accdb
I gave your reactions a closer look and started from scratch after a figured out the relations between the tables and the primary and foreign keys.
Attached the solution a came up with. It seems to be working. Would you have a look please.
Database31.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. I'll get going with the design of the form.
ASKER
Hallo Pat.
I finished the form. The only thing I'am still working on is to get the possibility to input either ICAO Code or IATA COde eg EHAM or AMS into the search criteria and display the airport name in the search criteria. The only textbox working with regard to the airport is the ICAO Code.
Kind regards.
Database312.accdb
I finished the form. The only thing I'am still working on is to get the possibility to input either ICAO Code or IATA COde eg EHAM or AMS into the search criteria and display the airport name in the search criteria. The only textbox working with regard to the airport is the ICAO Code.
Kind regards.
Database312.accdb
Can we call this question done and start a new one? You have added the airport names into the wrong table and you have replaced the combos I build with text boxes. Is that really the best solution?
ASKER
Thanks for the great help. If need be I'll start a new question.
You are a tough marker.
Normally you would not have separate columns for 744 and 737. You should have a column for AcftType, a column for Destination, and another column for Alternates.