The logic that we were discussing will not work for this setup. The query
would return (if working correctly) records for all months except November
if you enter November as the one to be tested.
Let's try a union query instead:
SELECT [Table 1].*
FROM [Table 1]
LEFT JOIN [Table 2]
ON [Table 1].PrimaryKeyField = [Table 2].ForeignKeyField
WHERE ([Table 2].ForeignKeyField Is Null)
UNION
SELECT [Table 1].*
FROM [Table 1]
WHERE [Table 1].PrimaryKeyField NOT IN
(SELECT [Table 2].PrimaryKeyField
FROM [Table 2]
WHERE ([Table 2].Field1 = "A" AND [Table 2].Field2 = "B");
This type of query, using the NOT IN logic, may run slow if you have lots of
records. But let's see if it works for your testing, and then we can discuss
some changes that would make it run faster.
--
Ken Snell
<MS ACCESS MVP>
Helga said:
Sample Table 1 (Vehicle):
Vehicle ID #
123456
8888888888
963852741
9999999999
Sample Table 2 (Mileage):
Mileage ID VIN # M Year M Month Mileage
1 123456 2004 October 12000
2 8888888888 2004 October 95000
3 9999999999 2004 October 12000
For example, when I query for all vehicle ID's that do not have a record in
Table 2 for November 2004, the results:
Vehicle ID#: 963852741
What I am expecting to get is all of the records from Table 1 because none
of them have corresponding records in Table 2.
:
"not working as desired" doesn't tell us much info about what records you're
getting vs. what you expect. Can you post some sample data from both tables
and what results you're getting?
--
Ken Snell
<MS ACCESS MVP>
Thank you for your response. Yes I would like all records from Table 1
who
do not have a record in Table 2 whereTable 2.Field 1 = "A" AND
Table2.Field 2
= "B". I took your advice. I tried the following query:
SELECT tblVehicle.*
FROM tblVehicle
LEFT JOIN tblMileage
ON tblVehicle.vehVehicleIDNum = tblMileage.milVehicleIDNum
WHERE (tblMileage.milVehicleIDNum Is Null) OR (tblMileage.milYear <>
[Enter
Year:] AND tblMileage.milMonth <> [Enter Month:]);
This is not working as desired. Can you help?
:
Let me see if I'm understanding your question.
You want to find all records in Table 1 that either have no record in
Table
2 with a foreign key value equal to Table 1's primary key value, or
records
in Table 1 that have a record in Table 2 (foreign key value equal to
Table
1's primary key value) but where the values of field 1 in Table 2 is not
A
and of field 2 in Table 2 is not B.
Is this correct?
Assuming that it is, try this SQL statement:
SELECT [Table 1].*
FROM [Table 1]
LEFT JOIN [Table 2]
ON [Table 1].PrimaryKeyField = [Table 2].ForeignKeyField
WHERE ([Table 2].ForeignKeyField Is Null) OR
([Table 2].[field 1] <> "A" AND [Table 2].[field 2] <> "B") ;
--
Ken Snell
<MS ACCESS MVP>
I am trying to create a query that will display all records in Table 1
that
do not have a record in Table 2 based on 3 fields in Table 2. For
example,
Select all records in Table 1 that do not have a record in Table 2,
where
Table 2.field 1 = A and Table2.field2 = B.
Table 1 and Table 2 have a 1 to Many relationship, respectively.
Table 1
Primary key is linked to a Foreign key in Table 2. Can anyone help?
