Multiple records showing when Multiples do not exist

[Nick]

The query shows multiple records when AlID0001.label_id and ALID0001.date and
ALID0001.time have a ALID0001.scans, of "7" later than 12:00:00. the record
multiplies each trans action of ALID0001.scans by 2. I have tried Join
changes but this does not correct the problem. Please take a look and let me
know where the error is. Thank you!

ExpressPast 12
SELECT ALID0001.label_id, ScannAction.Scanname, ALID0001.date,
ALID0001.time, ALID0001.Delete, ALID0001.Manual, ALID0001.scans,
ALID0001.zipcode, ALID0001.route_id, ALID0001.Employee_id, [Last Name] & ", "
& [First Name] AS Expr1, AccountAction.AccountAction
FROM AccountAction INNER JOIN (ScannAction INNER JOIN (Employees RIGHT JOIN
ALID0001 ON Employees.ScannerId = ALID0001.Employee_id) ON
ScannAction.ScanActionId = ALID0001.scans) ON AccountAction.AccoutnNo =
ALID0001.Manual
WHERE (((ALID0001.label_id) Like "e*") AND ((ALID0001.date)=[What Date]) AND
((ALID0001.time)>"12:00:00"));

ExpHistory
SELECT expresPast12query.label_id, ALID0001.time, ALID0001.Employee_id,
ALID0001.date, ALID0001.Delete, ScannAction.Scanname, ALID0001.scans,
ALID0001.Scanner_id, [Last Name] & ", " & [First Name] AS Expr2
FROM ScannAction INNER JOIN (Employees RIGHT JOIN (expresPast12query RIGHT
JOIN ALID0001 ON expresPast12query.label_id = ALID0001.label_id) ON
Employees.ScannerId = ALID0001.Employee_id) ON ScannAction.ScanActionId =
ALID0001.scans
WHERE (((expresPast12query.label_id) Like "e*"))
ORDER BY expresPast12query.label_id;

 

[Jerry Whittle]

Is Time a date/time field or text? If a date/time field it should look like
#12:00:00# and not have the quotation marks.

Also #12:00:00# is actually stored as Saturday, December 30, 1899 12:00:00
PM in Access!

It's almost aways a very bad thing to seperate the date and time into
different fields. Access does a much better job if both are kept together.

Speaking of "date" and "time", they are both reserved words in Access as
there are Date() and Time() functions. This can cause strange problems. Put
square brackets around them so that Access doesn't get confused. It should
look something like ALID0001.[time] .

How about the Employees.ScannerId = ALID0001.Employee_id
and ScannAction.ScanActionId = ALID0001.scans
and AccountAction.AccoutnNo = ALID0001.Manual
joins. Is at least one side of each of these joins the primary key for its
table? If not you can have multiple records returned as there may be a
Many-to-Many relationship leading to a Cartesian product.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The query shows multiple records when AlID0001.label_id and ALID0001.date and
ALID0001.time have a ALID0001.scans, of "7" later than 12:00:00. the record
multiplies each trans action of ALID0001.scans by 2. I have tried Join
changes but this does not correct the problem. Please take a look and let me
know where the error is. Thank you!

ExpressPast 12
SELECT ALID0001.label_id, ScannAction.Scanname, ALID0001.date,
ALID0001.time, ALID0001.Delete, ALID0001.Manual, ALID0001.scans,
ALID0001.zipcode, ALID0001.route_id, ALID0001.Employee_id, [Last Name] & ", "
& [First Name] AS Expr1, AccountAction.AccountAction
FROM AccountAction INNER JOIN (ScannAction INNER JOIN (Employees RIGHT JOIN
ALID0001 ON Employees.ScannerId = ALID0001.Employee_id) ON
ScannAction.ScanActionId = ALID0001.scans) ON AccountAction.AccoutnNo =
ALID0001.Manual
WHERE (((ALID0001.label_id) Like "e*") AND ((ALID0001.date)=[What Date]) AND
((ALID0001.time)>"12:00:00"));

ExpHistory
SELECT expresPast12query.label_id, ALID0001.time, ALID0001.Employee_id,
ALID0001.date, ALID0001.Delete, ScannAction.Scanname, ALID0001.scans,
ALID0001.Scanner_id, [Last Name] & ", " & [First Name] AS Expr2
FROM ScannAction INNER JOIN (Employees RIGHT JOIN (expresPast12query RIGHT
JOIN ALID0001 ON expresPast12query.label_id = ALID0001.label_id) ON
Employees.ScannerId = ALID0001.Employee_id) ON ScannAction.ScanActionId =
ALID0001.scans
WHERE (((expresPast12query.label_id) Like "e*"))
ORDER BY expresPast12query.label_id;

 

[Nick]

Thank you, The ALID0001 is an imported text file. The date and time are
sepreate fields. I wll change the "12:00" to #12:00# and I do have the
ScannAction.ScanActionID as primary key but I beleive I do have the
Cartesian product. Could changing quotation mark make the difference?

Is Time a date/time field or text? If a date/time field it should look like
#12:00:00# and not have the quotation marks.

Also #12:00:00# is actually stored as Saturday, December 30, 1899 12:00:00
PM in Access!

It's almost aways a very bad thing to seperate the date and time into
different fields. Access does a much better job if both are kept together.

Speaking of "date" and "time", they are both reserved words in Access as
there are Date() and Time() functions. This can cause strange problems. Put
square brackets around them so that Access doesn't get confused. It should
look something like ALID0001.[time] .

How about the Employees.ScannerId = ALID0001.Employee_id
and ScannAction.ScanActionId = ALID0001.scans
and AccountAction.AccoutnNo = ALID0001.Manual
joins. Is at least one side of each of these joins the primary key for its
table? If not you can have multiple records returned as there may be a
Many-to-Many relationship leading to a Cartesian product.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The query shows multiple records when AlID0001.label_id and ALID0001.date and
ALID0001.time have a ALID0001.scans, of "7" later than 12:00:00. the record
multiplies each trans action of ALID0001.scans by 2. I have tried Join
changes but this does not correct the problem. Please take a look and let me
know where the error is. Thank you!

ExpressPast 12
SELECT ALID0001.label_id, ScannAction.Scanname, ALID0001.date,
ALID0001.time, ALID0001.Delete, ALID0001.Manual, ALID0001.scans,
ALID0001.zipcode, ALID0001.route_id, ALID0001.Employee_id, [Last Name] & ", "
& [First Name] AS Expr1, AccountAction.AccountAction
FROM AccountAction INNER JOIN (ScannAction INNER JOIN (Employees RIGHT JOIN
ALID0001 ON Employees.ScannerId = ALID0001.Employee_id) ON
ScannAction.ScanActionId = ALID0001.scans) ON AccountAction.AccoutnNo =
ALID0001.Manual
WHERE (((ALID0001.label_id) Like "e*") AND ((ALID0001.date)=[What Date]) AND
((ALID0001.time)>"12:00:00"));

ExpHistory
SELECT expresPast12query.label_id, ALID0001.time, ALID0001.Employee_id,
ALID0001.date, ALID0001.Delete, ScannAction.Scanname, ALID0001.scans,
ALID0001.Scanner_id, [Last Name] & ", " & [First Name] AS Expr2
FROM ScannAction INNER JOIN (Employees RIGHT JOIN (expresPast12query RIGHT
JOIN ALID0001 ON expresPast12query.label_id = ALID0001.label_id) ON
Employees.ScannerId = ALID0001.Employee_id) ON ScannAction.ScanActionId =
ALID0001.scans
WHERE (((expresPast12query.label_id) Like "e*"))
ORDER BY expresPast12query.label_id;

 

[Jerry Whittle]

Actually if it's stored in a text file, switching from " to # probably won't
work. If the "time" is a 24 hours clock, like 1 pm being 13:00, your current
statement should work. Even then it would have nothing to do with your
Cartesian product.

You mentioned that one of the joins is on a primary key; however, your sql
statement show three joins. There needs to be a primary key in each of the
three joins to ensure that you aren't getting duplicate records returned.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thank you, The ALID0001 is an imported text file. The date and time are
sepreate fields. I wll change the "12:00" to #12:00# and I do have the
ScannAction.ScanActionID as primary key but I beleive I do have the
Cartesian product. Could changing quotation mark make the difference?

((ALID0001.time)>"12:00:00"));

Is Time a date/time field or text? If a date/time field it should look like
#12:00:00# and not have the quotation marks.

Also #12:00:00# is actually stored as Saturday, December 30, 1899 12:00:00
PM in Access!

It's almost aways a very bad thing to seperate the date and time into
different fields. Access does a much better job if both are kept together.

Speaking of "date" and "time", they are both reserved words in Access as
there are Date() and Time() functions. This can cause strange problems. Put
square brackets around them so that Access doesn't get confused. It should
look something like ALID0001.[time] .

How about the Employees.ScannerId = ALID0001.Employee_id
and ScannAction.ScanActionId = ALID0001.scans
and AccountAction.AccoutnNo = ALID0001.Manual
joins. Is at least one side of each of these joins the primary key for its
table? If not you can have multiple records returned as there may be a
Many-to-Many relationship leading to a Cartesian product.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The query shows multiple records when AlID0001.label_id and ALID0001.date and
ALID0001.time have a ALID0001.scans, of "7" later than 12:00:00. the record
multiplies each trans action of ALID0001.scans by 2. I have tried Join
changes but this does not correct the problem. Please take a look and let me
know where the error is. Thank you!

ExpressPast 12
SELECT ALID0001.label_id, ScannAction.Scanname, ALID0001.date,
ALID0001.time, ALID0001.Delete, ALID0001.Manual, ALID0001.scans,
ALID0001.zipcode, ALID0001.route_id, ALID0001.Employee_id, [Last Name] & ", "
& [First Name] AS Expr1, AccountAction.AccountAction
FROM AccountAction INNER JOIN (ScannAction INNER JOIN (Employees RIGHT JOIN
ALID0001 ON Employees.ScannerId = ALID0001.Employee_id) ON
ScannAction.ScanActionId = ALID0001.scans) ON AccountAction.AccoutnNo =
ALID0001.Manual
WHERE (((ALID0001.label_id) Like "e*") AND ((ALID0001.date)=[What Date]) AND
((ALID0001.time)>"12:00:00"));

ExpHistory
SELECT expresPast12query.label_id, ALID0001.time, ALID0001.Employee_id,
ALID0001.date, ALID0001.Delete, ScannAction.Scanname, ALID0001.scans,
ALID0001.Scanner_id, [Last Name] & ", " & [First Name] AS Expr2
FROM ScannAction INNER JOIN (Employees RIGHT JOIN (expresPast12query RIGHT
JOIN ALID0001 ON expresPast12query.label_id = ALID0001.label_id) ON
Employees.ScannerId = ALID0001.Employee_id) ON ScannAction.ScanActionId =
ALID0001.scans
WHERE (((expresPast12query.label_id) Like "e*"))
ORDER BY expresPast12query.label_id;