Query using a SQL Statement

[mattjones]

Hi All,

I am new to Access and would like a some assistance with the following.

Tables

Test Calibration
Calibration ID (Private Key)
Test Set ID (Lookup to Test Set Table)
Date Calibrated

Test Table
Test ID (Private Key)
Equipment ID
Date Of Test
Test Set ID (Lookup to Test Set Table)
Calibration ID


When the user selects a value from the Test Set ID field in the Test Table, I would like a drop down list to appear that would return the content of the Test Calibration Table, where the Test Set ID matches the value selected in the Test Set ID in table Test Table, allowing the Calibrated ID to be placed into the Calibrated ID in the Test Table.

The rows returned would be dynamic based on the value selected from the Test Set ID each time the user puts a new record into the table.

The current SQL statement that I have used for this is

SELECT [Test Calibration].[Calibration ID], [Test Calibration].[Test Set ID], [Test Calibration].[Date Calibrated]
FROM [Test Calibration]
WHERE [Test Table].[Test Set ID].Value = [Test Calibration].[Test Set ID]

For some reason when I initially open the table I receive a message asking the user to enter the parameter value for Test Table.Test Set ID then select ok or cancel.

If I enter the value of one of the Test Set IDs into the parameter box and select OK, that appears to return the same values for all records created regardless of the Test Set ID selection made by the user.

I would be very grateful of any assistance with this.

 

[tehNellie]

Because Access has no knowledge of [Test Table].[Test Set ID] in the context of your query. You Only reference the calibration table in your query (and for the love of all that is holy, DONT use spaces in your table or column names)

Your syntax is a bit confusing, but if [test table].testsetid is a table/column then use a join:

SELECT [Test Calibration].[Calibration ID], 
[Test Calibration].[Test Set ID], 
[Test Calibration].[Date Calibrated]
FROM [Test Calibration]
INNER JOIN [test table] ON
  [Test Table].[Test Set ID] = [Test Calibration].[Test Set ID]

That shows you all the rows in TestCalibration that have a matching TestSetID in the test Table.

if the user/query/application is supplying the value:

SELECT [Test Calibration].[Calibration ID], 
[Test Calibration].[Test Set ID], 
[Test Calibration].[Date Calibrated]
FROM [Test Calibration]
WHERE [Test Calibration].[Test Set ID] = [i]"Some value"[/i]

 

[mattjones]

Hi,

Thank you very much for your help so far, and the pointers but I still have an issue.

The value in [Test Table].[Test Set ID] is a combo box which is looked up from another table. When using the code that you have provided I get the same results regardless of the selection the user makes.

Thank you again for your help so far.