Query for information that does not match

[HeathenLdy]

I have a database in Access 2000 for processing weekly freight bills. I have
two tables, one is imported with the new week's information on it, and the
other is a consolidated table of all the Tracking ID #'s we have already
processed. I am trying to get my query to return only those Tracking ID #'s
that are not in the Consolidated table.

My formula is Express or Ground Tracking ID: (IIf([030905].[Express or
Ground Tracking ID]<>[Consolidated Summary].[Express or Ground Tracking
ID],Null,[030905].[Express or Ground Tracking ID]))

Unfortunately I am getting both those Tracking ID #'s that match and those
that don't in a random fashion (at least I can't figure out the reasoning).
I'm sure there is something wrong with the way this formula is written, I
just don't know what. This information is then being pulled into a second
table for "processing" and all the other hoops I need this information to
jump through to prepare the bill for payment. I need the field name "Express
or Ground Tracking ID" to carry through the whole process in order for my
final report to provide all the information needed for my accounting
department.

I hope I explained this clearly enough. Any help would be greatly
appreciated.

 

[Douglas J. Steele]

Do a Left Join between the two tables, and check for those rows that have
null values for the value from the Consolidated table.

 

[HeathenLdy]

I have a Left Join on these tables on the "Express or Ground ID" field. I
should have mentioned that in my original post.

Thanks for your reply.

Do a Left Join between the two tables, and check for those rows that have
null values for the value from the Consolidated table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a database in Access 2000 for processing weekly freight bills. I
have
two tables, one is imported with the new week's information on it, and the
other is a consolidated table of all the Tracking ID #'s we have already
processed. I am trying to get my query to return only those Tracking ID
#'s
that are not in the Consolidated table.

My formula is Express or Ground Tracking ID: (IIf([030905].[Express
or
Ground Tracking ID]<>[Consolidated Summary].[Express or Ground Tracking
ID],Null,[030905].[Express or Ground Tracking ID]))

Unfortunately I am getting both those Tracking ID #'s that match and those
that don't in a random fashion (at least I can't figure out the
reasoning).
I'm sure there is something wrong with the way this formula is written, I
just don't know what. This information is then being pulled into a second
table for "processing" and all the other hoops I need this information to
jump through to prepare the bill for payment. I need the field name
"Express
or Ground Tracking ID" to carry through the whole process in order for my
final report to provide all the information needed for my accounting
department.

I hope I explained this clearly enough. Any help would be greatly
appreciated.

 

[Douglas J. Steele]

Well, it depends on which way the left join is, but it should be as simple
as looking for those rows where the ID is null.

If you've got something like

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id
WHERE Table2.Id IS NULL

it should return only those rows in Table1 where Id doesn't exist in Table2.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a Left Join on these tables on the "Express or Ground ID" field. I
should have mentioned that in my original post.

Thanks for your reply.

Do a Left Join between the two tables, and check for those rows that have
null values for the value from the Consolidated table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a database in Access 2000 for processing weekly freight bills. I
have
two tables, one is imported with the new week's information on it, and
the
other is a consolidated table of all the Tracking ID #'s we have
already
processed. I am trying to get my query to return only those Tracking ID
#'s
that are not in the Consolidated table.

My formula is Express or Ground Tracking ID:
(IIf([030905].[Express
or
Ground Tracking ID]<>[Consolidated Summary].[Express or Ground Tracking
ID],Null,[030905].[Express or Ground Tracking ID]))

Unfortunately I am getting both those Tracking ID #'s that match and
those
that don't in a random fashion (at least I can't figure out the
reasoning).
I'm sure there is something wrong with the way this formula is written,
I
just don't know what. This information is then being pulled into a
second
table for "processing" and all the other hoops I need this information
to
jump through to prepare the bill for payment. I need the field name
"Express
or Ground Tracking ID" to carry through the whole process in order for
my
final report to provide all the information needed for my accounting
department.

I hope I explained this clearly enough. Any help would be greatly
appreciated.

 

[Heathenldy]

Thank you for your patience. I hope this isn't as frustrating for you as it
is for me. I am not use to working in the SQL view of my database, I usually
go through the query wizard. I tried :

SELECT [030905].[Bill to Account Number], [030905].[Invoice Number],
[030905].[Express or Ground Tracking ID]
FROM 030905 LEFT JOIN [Consolidated Summary] ON [030905].[Express or Ground
Tracking ID] = [Consolidated Summary].[Express or Ground Tracking ID]
WHERE ((([Consolidated Summary].[Express or Ground Tracking ID]) Is Null));

and got nothing. No records at all. What did I do wrong? (I didn't put the
round parethesis there, Access did that when I closed out of SQL and came
back in to make this copy.)

Thank you!!
Heather

Well, it depends on which way the left join is, but it should be as simple
as looking for those rows where the ID is null.

If you've got something like

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id
WHERE Table2.Id IS NULL

it should return only those rows in Table1 where Id doesn't exist in Table2.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a Left Join on these tables on the "Express or Ground ID" field. I
should have mentioned that in my original post.

Thanks for your reply.

Do a Left Join between the two tables, and check for those rows that have
null values for the value from the Consolidated table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a database in Access 2000 for processing weekly freight bills. I
have
two tables, one is imported with the new week's information on it, and
the
other is a consolidated table of all the Tracking ID #'s we have
already
processed. I am trying to get my query to return only those Tracking ID
#'s
that are not in the Consolidated table.

My formula is Express or Ground Tracking ID:
(IIf([030905].[Express
or
Ground Tracking ID]<>[Consolidated Summary].[Express or Ground Tracking
ID],Null,[030905].[Express or Ground Tracking ID]))

Unfortunately I am getting both those Tracking ID #'s that match and
those
that don't in a random fashion (at least I can't figure out the
reasoning).
I'm sure there is something wrong with the way this formula is written,
I
just don't know what. This information is then being pulled into a
second
table for "processing" and all the other hoops I need this information
to
jump through to prepare the bill for payment. I need the field name
"Express
or Ground Tracking ID" to carry through the whole process in order for
my
final report to provide all the information needed for my accounting
department.

I hope I explained this clearly enough. Any help would be greatly
appreciated.

 

[HeathenLdy]

Never mind!! Ignore me!! Just goes to show I should never jump into an access
project before my first cup of coffee! In working with this database
yesterday the information from the 030905 table had already been dumped into
the Consolidated Summary. DUH! I wasn't getting anything because it WAS
working.

Thank you for all your help Mr. Steele!!!

Thank you for your patience. I hope this isn't as frustrating for you as it
is for me. I am not use to working in the SQL view of my database, I usually
go through the query wizard. I tried :

SELECT [030905].[Bill to Account Number], [030905].[Invoice Number],
[030905].[Express or Ground Tracking ID]
FROM 030905 LEFT JOIN [Consolidated Summary] ON [030905].[Express or Ground
Tracking ID] = [Consolidated Summary].[Express or Ground Tracking ID]
WHERE ((([Consolidated Summary].[Express or Ground Tracking ID]) Is Null));

and got nothing. No records at all. What did I do wrong? (I didn't put the
round parethesis there, Access did that when I closed out of SQL and came
back in to make this copy.)

Thank you!!
Heather

Well, it depends on which way the left join is, but it should be as simple
as looking for those rows where the ID is null.

If you've got something like

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 LEFT JOIN Table2 ON Table1.Id = Table2.Id
WHERE Table2.Id IS NULL

it should return only those rows in Table1 where Id doesn't exist in Table2.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a Left Join on these tables on the "Express or Ground ID" field. I
should have mentioned that in my original post.

Thanks for your reply.

:

Do a Left Join between the two tables, and check for those rows that have
null values for the value from the Consolidated table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a database in Access 2000 for processing weekly freight bills. I
have
two tables, one is imported with the new week's information on it, and
the
other is a consolidated table of all the Tracking ID #'s we have
already
processed. I am trying to get my query to return only those Tracking ID
#'s
that are not in the Consolidated table.

My formula is Express or Ground Tracking ID:
(IIf([030905].[Express
or
Ground Tracking ID]<>[Consolidated Summary].[Express or Ground Tracking
ID],Null,[030905].[Express or Ground Tracking ID]))

Unfortunately I am getting both those Tracking ID #'s that match and
those
that don't in a random fashion (at least I can't figure out the
reasoning).
I'm sure there is something wrong with the way this formula is written,
I
just don't know what. This information is then being pulled into a
second
table for "processing" and all the other hoops I need this information
to
jump through to prepare the bill for payment. I need the field name
"Express
or Ground Tracking ID" to carry through the whole process in order for
my
final report to provide all the information needed for my accounting
department.

I hope I explained this clearly enough. Any help would be greatly
appreciated.