Query 3 tables and a bunch of columns. Very confused

[jayphilips]

Hi,

I would like to use a If -> Then but I can't figure this out. I have 3 tables that I need to pull certain data from.

Table 1 - GageID, GageDescription (GageID is used as a dropdown in Table 3)
Table 2 - ParameterMeasured (used as dropdown in Table3)
Table 3 - EER, GageID (there are 30, numbered GageID1, GageID2 .... GageID columns), ParameterMeasured (there are 30, numbered ParameterMeasured1, ParameterMeasured2.... columns)

What I want to do is run a query that will pull all GageID's, ParameterMeasured & GageDescription by EER.

How can I run a query that will pull the GageID, ParameterMeasured & GageDescription from the tables?

The query should let the user enter/select an EER and the query will display all GageID's, GageDescription & ParameterMeasured's. Ordered by GageID

I would really appreciate it if someone could help me???

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

http://www.sewizard.com

 

[jjonesal]

The way you have your tables designed makes this a more difficult task than usual. You probably should think about redesigning your tables to eliminate these multiple GageID and ParameterMeasured columns in Table 3 (ie. GageID1, GageID2, etc. and ParameterMeasured1, ParameterMeasured2....)

I think you're design is what's holding you back. If you could tell me a little more about the scenario, I can try to give you some design suggestions

Otherwise, this query is not going to be trivial....


JJ J. Jones
jjones@cybrtyme.com

 

[jayphilips]

Hi,

The reason why the database is designed that way is because the client specifically wanted it. (Trust me, I wouldn't of done it this way if it was up to me). At first there wasn't even dropdowns for the Gages but I finally talked them into that (it's better than updating every field when there's a change, now only one table needs to be updated)

I tried making a couple of queries to work off of but it never returns the data back correctly.

The code: SELECT Gage.GageID, Gage.GageDescription, ParameterMeasured.ParameterMeasured, TestRev2.EER
FROM ParameterMeasured INNER JOIN (Gage INNER JOIN TestRev2 ON (Gage.GageID = TestRev2.GageID1) OR (Gage.GageID = TestRev2.GageID2) OR (Gage.GageID = TestRev2.GageID3) OR (Gage.GageID = TestRev2.GageID4) OR (Gage.GageID = TestRev2.GageID5) OR (Gage.GageID = TestRev2.GageID6) OR (Gage.GageID = TestRev2.GageID7) OR (Gage.GageID = TestRev2.GageID6) OR (Gage.GageID = TestRev2.GageID7) OR (Gage.GageID = TestRev2.GageID8) OR (Gage.GageID = TestRev2.GageID9) OR (Gage.GageID = TestRev2.GageID10) OR (Gage.GageID = TestRev2.GageID11) OR (Gage.GageID = TestRev2.GageID12) OR (Gage.GageID = TestRev2.GageID13) OR (Gage.GageID = TestRev2.GageID14) OR (Gage.GageID = TestRev2.GageID15) OR (Gage.GageID = TestRev2.GageID16) OR (Gage.GageID = TestRev2.GageID17) OR (Gage.GageID = TestRev2.GageID18) OR (Gage.GageID = TestRev2.GageID19) OR (Gage.GageID = TestRev2.GageID20) OR (Gage.GageID = TestRev2.GageID21) OR (Gage.GageID = TestRev2.GageID22) OR (Gage.GageID = TestRev2.GageID23) OR (Gage.GageID = TestRev2.GageID24) OR (Gage.GageID = TestRev2.GageID25) OR (Gage.GageID = TestRev2.GageID26) OR (Gage.GageID = TestRev2.GageID27) OR (Gage.GageID = TestRev2.GageID28) OR (Gage.GageID = TestRev2.GageID29) OR (Gage.GageID = TestRev2.GageID30)) ON (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured1) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured2) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured3) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured4) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured5) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured6) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured7) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured8) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured9) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured10) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured11) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured12) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured13) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured14) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured15) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured16) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured17) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured18) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured19) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured20) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured21) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured22) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured23) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured24) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured25) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured26) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured27) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured28) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured29) OR (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured30);


Instead of returning 5 rows it's returning 129.


If I make the code read:

SELECT Gage.GageID, Gage.GageDescription, ParameterMeasured.ParameterMeasured, TestRev2.EER
FROM ParameterMeasured INNER JOIN (Gage INNER JOIN TestRev2 ON (Gage.GageID = TestRev2.GageID1) OR (Gage.GageID = TestRev2.GageID2) OR (Gage.GageID = TestRev2.GageID3) OR (Gage.GageID = TestRev2.GageID4) OR (Gage.GageID = TestRev2.GageID5) OR (Gage.GageID = TestRev2.GageID6) OR (Gage.GageID = TestRev2.GageID7) OR (Gage.GageID = TestRev2.GageID6) OR (Gage.GageID = TestRev2.GageID7) OR (Gage.GageID = TestRev2.GageID8) OR (Gage.GageID = TestRev2.GageID9) OR (Gage.GageID = TestRev2.GageID10) OR (Gage.GageID = TestRev2.GageID11) OR (Gage.GageID = TestRev2.GageID12) OR (Gage.GageID = TestRev2.GageID13) OR (Gage.GageID = TestRev2.GageID14) OR (Gage.GageID = TestRev2.GageID15) OR (Gage.GageID = TestRev2.GageID16) OR (Gage.GageID = TestRev2.GageID17) OR (Gage.GageID = TestRev2.GageID18) OR (Gage.GageID = TestRev2.GageID19) OR (Gage.GageID = TestRev2.GageID20) OR (Gage.GageID = TestRev2.GageID21) OR (Gage.GageID = TestRev2.GageID22) OR (Gage.GageID = TestRev2.GageID23) OR (Gage.GageID = TestRev2.GageID24) OR (Gage.GageID = TestRev2.GageID25) OR (Gage.GageID = TestRev2.GageID26) OR (Gage.GageID = TestRev2.GageID27) OR (Gage.GageID = TestRev2.GageID28) OR (Gage.GageID = TestRev2.GageID29) OR (Gage.GageID = TestRev2.GageID30)) ON (ParameterMeasured.ParameterMeasured = TestRev2.ParameterMeasured1);

it will return the correct gages id, description and only the 1st parameter (but it displays for all gages when it shouldn't

I'm really confused on how to do this.

Thanks in advance
Jay
Get The Exposure Your Web Site Deserves

http://www.sewizard.com

 

[jayphilips]

Hi again,

Okay, I've got it down from displaying 129 fields to 25 but something still isn't right with my code.

I have the TestRev2 table with EER as the primary key, the Gage table with GageID as primary, ParameterMeasured table with ParameterMeasured as primary key. When I run the query it returns too many rows, it should only return 5. It seems like it's returning the Gages that were used and the descriptions but for every ParameterMeasured so for Frequency it's showing that the 5 gages that were used were all for Frequency when really only 1 of them was.

Does anyone else seem to have this problem? If I understood modules better I could do it better but I don't know what to do. I also tired the DLookup but I couldn't get it to work at all.

Here's the code:


SELECT DISTINCTROW TestRev2.EER, Gage.GageID, Gage.GageDescription, ParameterMeasured.ParameterMeasured
FROM ParameterMeasured INNER JOIN (TestRev2 INNER JOIN (Gage INNER JOIN EER_GageID_Parameter ON (Gage.GageID = EER_GageID_Parameter.GageID1) OR (Gage.GageID = EER_GageID_Parameter.GageID2) OR (Gage.GageID = EER_GageID_Parameter.GageID3) OR (Gage.GageID = EER_GageID_Parameter.GageID3) OR (Gage.GageID = EER_GageID_Parameter.GageID4) OR (Gage.GageID = EER_GageID_Parameter.GageID4) OR (Gage.GageID = EER_GageID_Parameter.GageID5) OR (Gage.GageID = EER_GageID_Parameter.GageID6) OR (Gage.GageID = EER_GageID_Parameter.GageID7) OR (Gage.GageID = EER_GageID_Parameter.GageID8) OR (Gage.GageID = EER_GageID_Parameter.GageID9) OR (Gage.GageID = EER_GageID_Parameter.GageID10) OR (Gage.GageID = EER_GageID_Parameter.GageID11) OR (Gage.GageID = EER_GageID_Parameter.GageID12) OR (Gage.GageID = EER_GageID_Parameter.GageID13) OR (Gage.GageID = EER_GageID_Parameter.GageID14) OR (Gage.GageID = EER_GageID_Parameter.GageID15) OR (Gage.GageID = EER_GageID_Parameter.GageID16) OR (Gage.GageID = EER_GageID_Parameter.GageID17) OR (Gage.GageID = EER_GageID_Parameter.GageID18) OR (Gage.GageID = EER_GageID_Parameter.GageID19) OR (Gage.GageID = EER_GageID_Parameter.GageID20) OR (Gage.GageID = EER_GageID_Parameter.GageID21) OR (Gage.GageID = EER_GageID_Parameter.GageID22) OR (Gage.GageID = EER_GageID_Parameter.GageID23) OR (Gage.GageID = EER_GageID_Parameter.GageID24) OR (Gage.GageID = EER_GageID_Parameter.GageID25) OR (Gage.GageID = EER_GageID_Parameter.GageID26) OR (Gage.GageID = EER_GageID_Parameter.GageID27) OR (Gage.GageID = EER_GageID_Parameter.GageID28) OR (Gage.GageID = EER_GageID_Parameter.GageID29) OR (Gage.GageID = EER_GageID_Parameter.GageID30)) ON TestRev2.EER = EER_GageID_Parameter.EER) ON (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured30) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured29) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured28) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured27) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured26) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured25) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured24) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured23) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured22) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured21) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured20) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured19) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured18) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured17) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured16) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured15) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured15) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured13) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured12) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured11) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured10) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured9) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured8) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured7) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured6) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured5) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured4) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured3) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured2) OR (ParameterMeasured.ParameterMeasured = EER_GageID_Parameter.ParameterMeasured1);

Any help would be very much appreciated.
Thanks in advance!!
Jay
Get The Exposure Your Web Site Deserves

http://www.sewizard.com