How to create a SQL query using three tables

[uted]

Good day !

I am new to Access (have played a little before) and SQL syntax and would appreciate some help in designing a query that retrieves the final value from the ST-Results table using 2 additional tables. I am using Access to help me with the SQL syntax.

This is what I have :

Table 1 - ST_Sample contains information on the sample that is being analyzed. From this table I get the name of the sample (ST_Samples.Origin) and sample ID (ST_Samples.[SampleID *]).

Table 2 - ST-Tests contains a list of the tests that can be used to analyze the sample. From this table I get the test name (ST-Tests.TestName) and test ID (ST_Tests.TestID). However, the test ID changes whenever the test is updated, I therefore have to retrieve the newest testID using the TestName. I want to use this table to determine the TestID number.

Table 3 - ST_Results contains the results (ST_Results.ResultValue) of all the analysis on all the samples for all components etc.

I do not know how to link these three tables to be able to retrieve the correct testID from the 2nd table to use when retrieving the final value from the first and third table. The ST-Results table is linked to the ST_Samples table by the SampleID which is in both, and the ST_Results table is linked to the ST_Test table by the TestID which is in both, but not in the ST_Samples table. And the ST_Test table does not contain the SampleID.

This is what I have so far :

SELECT ST_Samples.[SampleID *], ST_Samples.Origin, ST_Results.ComponentID, ST_Results.ResultValue, ST_Samples.UserSampleID, ST_Results.TestID
FROM ST_Samples LEFT JOIN ST_Results ON ST_Samples.[SampleID *] = ST_Results.SampleID
WHERE (((ST_Samples.Origin)="AO-V4403") AND ((ST_Results.TestID)=63));

and in here I already told it to look at the TestID = 63 (this is the part I need to change). And this query gives me a list of all the components analyzed in this test (which is what I need).

I hope I was clear !
Thank you for the help.

Ute Duvenhage

Ute
A person who never made a mistake never tried anything new.
Albert Einstein

 

[jedraw]

Ute,

Do you really have a field named [Sample *]?

You can try this query to get the Testname from Table 2 ST_Tests. If this is part of what you need, post back with more info/questions.

SELECT ST_Samples.[SampleID *]
, ST_Samples.Origin
, ST_Results.ComponentID
, ST_Results.ResultValue
, ST_Samples.UserSampleID
, ST_Results.TestID
,ST_Tests.Testname
FROM 
 ST_Samples 
,ST_Results  
,ST_Tests
WHERE 
ST_Samples.[SampleID *] = ST_Results.SampleID AND
ST_Samples.Origin="AO-V4403" AND 
ST_Samples.TestId = TD_Tests.TestID AND
ST_Results.TestID=63;

 

[randy700]

Let's see if I understand your question.
The query you have works, but you need a way to determine the TestID?

If that's correct, I'd try something like this...

SELECT ST_Samples.[SampleID *], ST_Samples.Origin, ST_Results.ComponentID, ST_Results.ResultValue, ST_Samples.UserSampleID, [b][COLOR=red]Max(ST_Results.TestID) As NewTestID[/color][/b]
FROM ST_Samples LEFT JOIN ST_Results ON ST_Samples.[SampleID *] = ST_Results.SampleID
WHERE (((ST_Samples.Origin)="AO-V4403") AND (([s]ST_Results.TestID)=63[/s][b][COLOR=red]ST_Tests.TestName = "[i]xxx[/i]"[/color][/b]))

Randy

 

[uted]

Thank you for the replies.

MS Access creates the name in the SQL format with the brackets around them. The column however does not have the brackets.

I have both you recommendations, but neither is giving me the results I need.

Jedraw - I need to determine the number of the TestID from the TestName in ST_Tests. There will be several ID's for a single TestName in the same ST_Tests table, but only one is active - the TestID with highest number. And this number will then be used to determine which results to report from the ST_Results table.

Randy700 - This makes sense, but, in MS Access (after I posted your syntax there) it tells me that there is no link between the third table ST_Tests with the other two - at least that is what I think the following message is saying :

You tried to execute a query that does not include the specified expression 'SampleID *' as part of an aggregate function.

SampleID is not available in the third table ST_Tests.

I tried adding an AND in the FROM part of the sql syntax to combine the ST_Results table with the ST_Tests table using TestID as the link, but was unable to do it successfully. The FROM part does not accept an AND when using LEFT JOIN. Are there other alternatives ?

Thanks

Ute
A person who never made a mistake never tried anything new.
Albert Einstein