Link to home
Create AccountLog in
Avatar of gbnorton
gbnortonFlag for United States of America

asked on

Access query for 2 tables and 2 criteria

Here is my query:
SELECT TubeData.SerialNumber, TubeData.VmiPartNumber, TubeData.Date_Received, TubeData.Customer, TubeData.TubeType, TubeData.SalesOrder, [Current Location].SerialNumber, [Current Location].Area, [Current Location].[Date Moved], [Current Location].VmiPartNumber
FROM TubeData INNER JOIN [Current Location] ON (TubeData.VmiPartNumber = [Current Location].VmiPartNumber) AND (TubeData.SerialNumber = [Current Location].SerialNumber)
WHERE (((TubeData.SerialNumber)=[Serial Number:]) AND ((TubeData.VmiPartNumber)=[Part Number:]));

This query returns no records.

To me it should return all records from both tables where the requested serial number and part number are found.

In table one that would be one record.  In table 2 that would be many records.

Thanks,
Brooks
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

check if you are entering the correct values for the parameters

[Serial Number:]  and [Part Number:]


upload a copy of the db with those tables
SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of gbnorton

ASKER

Capriocorn, the db is attached.

In TUBEDATA we have a serial number that is used twice.  Once each for two different part numbers PS485 and PS489.  As the query is currently configured I get no data.  By removing the VmiPartNumber from the join I get data from both tables for the serial number.
But the data from CURRENT LOCATION is the same whether I enter either PS485 or PS489...

Thanks,
Brooks
EE-Copy-Xray-Tube-Tracking.mdb
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
My mistake.  Although there is a field for VmiPartNumber in CURRENT LOCATION, none of the records are populated with it.

Thanks for your help.