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
faraoosirisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
can you post a sample of your table structure and the query you are using?

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.
0
PatHartmanCommented:
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.
0
faraoosirisAuthor Commented:
Hallo Dale,

I attached a sample of the "inherited" table tblAltn to the initial posting.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

faraoosirisAuthor Commented:
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.
0
PatHartmanCommented:
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
0
faraoosirisAuthor Commented:
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.
0
PatHartmanCommented:
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.
0
faraoosirisAuthor Commented:
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
0
faraoosirisAuthor Commented:
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
0
PatHartmanCommented:
I changed the schema.  You don't need tblAlternate unless the set of Alternates is different from the set of destinations.  An alternate for JFK might be LGA but they are both airports and belong in the list of destinations.

I also created a form with a subform because that would be your next challenge.
Database31UpdatedByPat.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
faraoosirisAuthor Commented:
Thank you. I'll get going with the design of the form.
0
faraoosirisAuthor Commented:
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
0
PatHartmanCommented:
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?
0
faraoosirisAuthor Commented:
Thanks for the great help. If need be I'll start a new question.
0
PatHartmanCommented:
You are a tough marker.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.