And/ Or

[Guest]

I have a table with a list of IDs. I need to produce a query that takes the
matching IDs from tables 2 and 3.
For example

Table 1 Table 2 Table 3
000001 000001 000002
000002

How do i write a query so that it pulls from both tables?
Thanks

 

[Guest]

What do you want the results to look like?

 

[Duane Hookom]

How does 000001 from Table1 match 000002 from Table3?

 

[Guest]

Well I have a main table with hundreds of records in and I need to match
Serial IDs to this from Tables 2 and 3.
Not the same ones from 2 and 3 but all those that match from both.
Am i being too vague?

 

[Neil Sunderland]

Well I have a main table with hundreds of records in and I need to match
Serial IDs to this from Tables 2 and 3.
Not the same ones from 2 and 3 but all those that match from both.
Am i being too vague?

This would probably do it:

SELECT * FROM Table1
WHERE Table1.ID IN (SELECT ID From Table2) AND
Table1.ID IN (SELECT ID From Table3)

 

[Guest]

Sorry Neil,
it does not need to be in both, sinply in either
Would i replace the And with and OR?
It currently reads:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Serial ID], IP.[Serial ID], PO.[Serial ID]
FROM ([All T&M Data] INNER JOIN IP ON [All T&M Data].[Serial ID] =
IP.[Serial ID]) INNER JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE [All T&M Data].[Serial ID] IN (SELECT [Serial ID] From IP) AND [All
T&M Data].[Serial ID] IN (SELECT [Serial ID] From PO)

Matt

 

[Duane Hookom]

Can you provide about 5-6 records from your tables and then the desired
display in a query?


--
Duane Hookom
MS Access MVP

Sorry Neil,
it does not need to be in both, sinply in either
Would i replace the And with and OR?
It currently reads:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Serial ID], IP.[Serial ID], PO.[Serial ID]
FROM ([All T&M Data] INNER JOIN IP ON [All T&M Data].[Serial ID] =
IP.[Serial ID]) INNER JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial
ID]
WHERE [All T&M Data].[Serial ID] IN (SELECT [Serial ID] From IP) AND [All
T&M Data].[Serial ID] IN (SELECT [Serial ID] From PO)

Matt

This would probably do it:

SELECT * FROM Table1
WHERE Table1.ID IN (SELECT ID From Table2) AND
Table1.ID IN (SELECT ID From Table3)

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

 

[Neil Sunderland]

it does not need to be in both, sinply in either
Would i replace the And with and OR?

Er, yes! (I'm sure it would have been quicker to try it than wait for
an answer!!)