Query Help

[Guest]

I have 1 question regarding the following query. I need to know why when I
run the query it is prompting me for the date 2 times.

SELECT qry_AMUWork_Rcd_Date.DateRcd, tblCorrWork.Frequency,
tblCorrWork.RptName, tblCorrWork.MISNumber
FROM qry_AMUWork_Rcd_Date LEFT JOIN tblCorrWork ON
qry_AMUWork_Rcd_Date.MISNumber = tblCorrWork.MISNumber
WHERE (((([tblCorrWork].[MISNumber])=85 Or ([tblCorrWork].[MISNumber])=69 Or
([tblCorrWork].[MISNumber])=71 Or ([tblCorrWork].[MISNumber])=74 Or
([tblCorrWork].[MISNumber])=80 Or ([tblCorrWork].[MISNumber])=81 Or
([tblCorrWork].[MISNumber])=92 Or ([tblCorrWork].[MISNumber])=93 Or
([tblCorrWork].[MISNumber])=98 Or ([tblCorrWork].[MISNumber])=79)=False));

The other query that is associated with the above query is:

SELECT tblAMUWork.DateRcd, tblAMUWork.MISNumber, tblAMUWork.RptName,
tblAMUWork.AmtRcd
FROM tblAMUWork
WHERE (((tblAMUWork.DateRcd)=[Enter Date: mm/dd/yyyy]));


If someone could assist that would be great.

Thank you.

 

[Guest]

Try this. If nothing else it's a little cleaner. You might need to change the
NOT IN to IN if the results aren't correct.

SELECT tblAMUWork.DateRcd,
tblCorrWork.Frequency,
tblCorrWork.RptName,
tblCorrWork.MISNumber
FROM tblAMUWork LEFT JOIN tblCorrWork
ON tblAMUWork.MISNumber = tblCorrWork.MISNumber
WHERE [tblCorrWork].[MISNumber]
NOT IN (85, 69, 71, 74, 80, 81, 92, 93, 98, 79)
AND tblAMUWork.DateRcd=[Enter Date: mm/dd/yyyy];

 

[Guest]

Thank you for you response. The information is not showing me all of what I
want to see. I need to see everything from the tblCorrWork table and if
items have been received in the tblAMUWork then I need to see the date. When
I run the query it is only showing me that items that are in the tblAMUWork
and not the items in tblCorrWork.

Try this. If nothing else it's a little cleaner. You might need to change the
NOT IN to IN if the results aren't correct.

SELECT tblAMUWork.DateRcd,
tblCorrWork.Frequency,
tblCorrWork.RptName,
tblCorrWork.MISNumber
FROM tblAMUWork LEFT JOIN tblCorrWork
ON tblAMUWork.MISNumber = tblCorrWork.MISNumber
WHERE [tblCorrWork].[MISNumber]
NOT IN (85, 69, 71, 74, 80, 81, 92, 93, 98, 79)
AND tblAMUWork.DateRcd=[Enter Date: mm/dd/yyyy];


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have 1 question regarding the following query. I need to know why when I
run the query it is prompting me for the date 2 times.

SELECT qry_AMUWork_Rcd_Date.DateRcd, tblCorrWork.Frequency,
tblCorrWork.RptName, tblCorrWork.MISNumber
FROM qry_AMUWork_Rcd_Date LEFT JOIN tblCorrWork ON
qry_AMUWork_Rcd_Date.MISNumber = tblCorrWork.MISNumber
WHERE (((([tblCorrWork].[MISNumber])=85 Or ([tblCorrWork].[MISNumber])=69 Or
([tblCorrWork].[MISNumber])=71 Or ([tblCorrWork].[MISNumber])=74 Or
([tblCorrWork].[MISNumber])=80 Or ([tblCorrWork].[MISNumber])=81 Or
([tblCorrWork].[MISNumber])=92 Or ([tblCorrWork].[MISNumber])=93 Or
([tblCorrWork].[MISNumber])=98 Or ([tblCorrWork].[MISNumber])=79)=False));

The other query that is associated with the above query is:

SELECT tblAMUWork.DateRcd, tblAMUWork.MISNumber, tblAMUWork.RptName,
tblAMUWork.AmtRcd
FROM tblAMUWork
WHERE (((tblAMUWork.DateRcd)=[Enter Date: mm/dd/yyyy]));


If someone could assist that would be great.

Thank you.

 

[John W. Vinson]

Thank you for you response. The information is not showing me all of what I
want to see. I need to see everything from the tblCorrWork table and if
items have been received in the tblAMUWork then I need to see the date. When
I run the query it is only showing me that items that are in the tblAMUWork
and not the items in tblCorrWork.

The problem is that you're applying a criterion to the MISNumber field in
tblCorrWork. If there is no record in tblCorrWork, then its MISNumber field is
NULL in the left join query - so it will fail the NOT IN criterion (or an IN
criterion as well!)

Two suggestions:

SELECT tblAMUWork.DateRcd,
tblCorrWork.Frequency,
tblCorrWork.RptName,
tblCorrWork.MISNumber
FROM tblAMUWork LEFT JOIN tblCorrWork
ON tblAMUWork.MISNumber = tblCorrWork.MISNumber
WHERE [tblCorrWork].[MISNumber]
NOT IN (85, 69, 71, 74, 80, 81, 92, 93, 98, 79)
OR [tblCorrWork].[MISNumber] IS NULL
AND tblAMUWork.DateRcd=[Enter Date: mm/dd/yyyy];

Or you may need to create and save a query selecting the subset of tblCorrWork
that is NOT IN (....), and left join this query to tblAMUWork.

John W. Vinson [MVP]