Majoe problems trying to query between two tables

[jayphilips]

Okay here's my problem, I have 1 database with 2 tables (well really 4 but only 2 of them need to be queried really)

The first table is "Gage" with "Gage ID" as the primary key, also has a field "Gage Description"
The 2nd table is "Testrev2" with "EE#" as the primary key, has dropdown lists from the Gage table Gage ID field, the fields are called: "Gage ID, Gage ID 2, Gage ID 3 and so on..."

The part that I am having trouble with is the queries. I need to have 2 queries:

1. Allow the user to run a query by either typing in the EER# or selecting the EER# from a dropdown and the program will in return report the Gage ID's that were used and the Gage ID's description if a field is blank and or shows "Select one" the program will not display it.

2. Allow the user to query by Gage ID and in return the program will only report the EER's that used that particular Gage.

Can any one help me? I tried using the wizard but that didn't help. I am really realy confused X-)

Thanks in advance!!! Jay
Get The Exposure Your Web Site Deserves

http://www.sewizard.com

 

[iejerry]

I would modify your database design to make it more normalized. You usually store multiple occurrences in multiple rows, not multiple columns. This makes querying simpler. Is there "header" type information for a testrev that applies to all gages? If so, I would keep the GAGE table:

GageID
Gage Description

and have the TESTREV2 table only be:

EER#

and any other information that applies at that level, and then add the TESTREV2GAGE table (with hopefully a better name than that - sorry) that would be:

EER#
GAGEID

Your query would then look something like:

SELECT TESTREV2GAGE.EER#, TESTREV2GAGE.GAGEID, GAGE.GageDescription
FROM TESTREV2GAGE INNER JOIN GAGE ON TESTREV2GAGE .GAGEID = GAGE.GAGEID;

 

[jayphilips]

What do you mean by multiple rows and not multiple columns?

I could send you the database if you want to see how I have it all set up.

Thanks in advance! Jay
Get The Exposure Your Web Site Deserves

http://www.sewizard.com

 

[GingerR]

i agree with iejerry. read about 'normalization' and 'many-to-many relationships'. instead of your data looking like you have it, there will be a third table that relates each EER# to the GAGES it uses. In that table, as iejerry describes, your data would look something like this:

EER# GageID
1 1
1 2
1 7
2 7
3 2

where EER# is taken from TESTREV2 table, and GageID comes from the table Gage. This way you are not limited to three (or whatever) gages, and your data is normalized and filtering/searching/sorting is performed easily and correctly and as it should be. get it?

The ID's in the above table can be entered using drop-down boxes (seems like you already know how to do this). After setting it up this way you will easily see how queries and searches can be done.

have fun--g