Multi Table Query with Look up...

[BlueChicken]

Hi, I have been making a database to record employee travel - which at first I was trying to make all my information fit into one little form (disaster as pointed out by many). I fixed that and now have many different little forms all linked together and displayed neatly and with a lovely little form acting as my Directory... now here is where I hit my snag.

I have 3 forms (Air Travel, Hotels, and Rental Cars) which are all linked back the their original request via a Travel Request ID ( [RequestID] ). Each form has various information relating to their title subject.

I need to be able to pull information from all three forms by searching for their request ID number, and then make a report from all of this. The report would be easy to make from a Query if I could only get the query working.

I have tried loading all of the fields into the query wizard and under the [RequestID] criteria for each [RequestID] field I put [Travel Request ID:], which makes it a prompt for string data, but when the query is run no matter what I enter into the look up, there is no data displayed.

I really could use some help! Thanks!

 

[SOS]

Does the query return anything if you remove the criteria?

Also, do you have any lookups set at table level?

 

[BlueChicken]

Does the query return anything if you remove the criteria?

Also, do you have any lookups set at table level?

Nothing is returned with the criteria removed either.

There are look ups in all three individual tables. The Air Travel form has look ups to an Airline List table, and a city table. The Hotel form has a look up for hotel names, and the rental car table has a look up for rental car size and rental agencies.

 

[SOS]

There are look ups in all three individual tables.

You should get rid of lookups at table level. See here for why.

Also, when you link your tables you may not want 1 to 1 links (Inner Join) for some tables which may, or may not, have data. For those you would want to join to them using an OUTER JOIN. In other words, the Select All from this one table which will have data and only data from X table which may have data. If you double click on the link in the query you will open a dialog where you can change the join type.

 

[BlueChicken]

You should get rid of lookups at table level. See here for why.

So because I have look ups in my tables, the query won't run? They have been working fine for me before in several other queries I have made.

Also, when you link your tables you may not want 1 to 1 links (Inner Join) for some tables which may, or may not, have data. For those you would want to join to them using an OUTER JOIN. In other words, the Select All from this one table which will have data and only data from X table which may have data. If you double click on the link in the query you will open a dialog where you can change the join type.

... I hate to say it but most of that went over my head... Could you explain that again please?

 

[SOS]

Having the lookups confuses Access many times when you select a field that has a lookup, it will display the text value and you can't use criteria on the field that you want because it is looking for something else.

As for the joins -

Go to your query

See the line that links the two tables

Double click on it

See what comes up.

 

[BlueChicken]

See what comes up.

The relationships are all one-to-many not one-to-one... and if they are not one-to-many they are indeterminate... not sure what that means.

Under Join Type I have 3 options to choose from. I currently have them all set to the default #1. The options are:

1: Only include rows where the joined fields from both tables are equal.

2: Inlclude ALL records from 'Car Sizes' (or [FieldName]) and only those records from 'Requests where the joined fields are equal.

3: Include ALL records from 'Requests' and only those records from 'Car Sizes' where the joined fields are equal.

Then OK or Cancel.

There is no "Outer Join"

 

[SOS]

Numbers 2 and 3 ARE Outer joins. Number 1 is an INNER Join. Which of number 2 or 3 you select (for each link) depends on which table(s) are going to always have data and which will only sometimes have data.

 

[BlueChicken]

Numbers 2 and 3 ARE Outer joins. Number 1 is an INNER Join. Which of number 2 or 3 you select (for each link) depends on which table(s) are going to always have data and which will only sometimes have data.

When it comes to the request form, the data will sometimes be empty because not everyone requests everything - but when that happens no record is made in the other forms... so they both will be empty or not there at all...

Which option would be better? It is the same for all 3 tables.

 

[SOS]

Which is considered the "Parent" table? (the one on the one-to-many side) - that is the base one.

 

[BlueChicken]

The Parent table is the Request Table.

 

[SOS]

Then that one would be the one where you would select either #2 or #3, where it said Select All from Request and only those records that match from XXX table.

 

[BlueChicken]

By doing that will I be able to keep my tables the way they are?

 

[SOS]

By doing that will I be able to keep my tables the way they are?

As far as I know... but we'll have to see. I've kind of lost track of a lot of it with most of the back and forth lately. It might be useful to upload a copy of the database if you can.

 

[BlueChicken]

It might be useful to upload a copy of the database if you can.

I tried to upload a copy but there is some security on our work network. Would screen shots help a little?

 

[SOS]

They might help.

 

[BlueChicken]

Alright. I'll get a few together and post them here.

Thanks for all of your help!

 

[BlueChicken]

Here are some pictures of the database.

http://s823.photobucket.com/albums/zz154/BlueChickenACCESS/ <-- All of them were too big to attach to this message so I used photobucket... hope that is ok?

All of my tables are listed. Any names that are the same are linked from the request table down to the other tables. Some tables are there to organize smaller pieces of data for drop down lists, such as the rental agencies and hotel names and such. There are forms for all tables, however they are there only for update purposes.

dba1.bmp is what my database looks like when opening. The directory has buttons to all forms, and the smaller buttons with the magnifying glass on the paper are reports that match the forms. They are reports on all files organized by Request ID, but not paired cross-table which is what I am trying to do.

If you need to see anything else to figure it out, let me know and I'll take more pictures. I'm going to try and see if I can get the database out of the system.

 

[SOS]

Unfortunately work does not let that get through. I will have to wait until I get home to view them.

 

[BlueChicken]

Does your work allow any other image sharing site?