Compare Query

[D.E.N.N.I.S.]

I'm very new to SQL and I am having a heck of a time figuring out how to design this query. qryRackZeroOHB references two tables: Sizing and tblRackZeroOHBReviewed. This query will run daily and append into tblRackZeroOHBReviewed once the user enters the relevant information into the [Action] field.

I want the query parameters to cross reference the [Part Number] field between each table to exclude any records that match between Sizing and tblRackZeroOHBReviewed AND a DateDiff(d,now(),tblRackZeroOHBReviewed.[Review Date])>365. I also want to include records that do not have a [Part Number] match. I have no idea where to begin as I'm unfamiliar with inner/outer joins and unions. My current SQL has a syntax error.

My additional query parameters are in my current SQL statement below.

SELECT Sizing.[Part Number], Sizing.Ohb, Sizing.Trans, Sizing.Location, Sizing.[Curr Alpha], Sizing.[Curr Cs1], Sizing.[Curr Cs2], Sizing.[Curr Max Qty], Sizing.[Sugg Alpha], Sizing.[Sugg Cs1], Sizing.[Sugg Cs2], Sizing.[Sugg Max Qty], Sizing.Weight, Sizing.[Order Qty], Sizing.[Order Point Qty], Now() AS [Review Date], fOSUserName() AS [Reviewed By], "" AS [Action], "" AS [Additional Notes]
FROM Sizing, tblRackZeroOHBReviewed
LEFT OUTER JOIN tblRackZeroOHBReviewed
ON Sizing.[Part Number] = tblRackZeroOHBReviewed.[Part Number]
WHERE (((Sizing.Ohb)=0) AND ((Sizing.[Curr Alpha])="XS" Or (Sizing.[Curr Alpha])="XP" Or (Sizing.[Curr Alpha])="XT" Or (Sizing.[Curr Alpha])="XL" Or (Sizing.[Curr Alpha])="XV" Or (Sizing.[Curr Alpha])="HS") AND ((Sizing.[Curr Cs1])<>[Sizing].[Sugg Max Qty]) AND ((Sizing.[Sugg Alpha])<>"BN")) AND DateDiff(d,now(),tblRackZeroOHBReviewed.[Review Date])>365
ORDER BY Sizing.Trans DESC;

Any help would be much appreciated!

 

[plog]

Your SQL might have a syntax error, but even if that gets fixed you have a logical error.

The first argument in your DateDiff needs to be enclosed in quotes (d->"d"). However, logically that DateDiff is working against you. When you add criteria to a field in a table that is in the LEFT JOIN (tblRackZeroOHBReviewed) you have effectively undone the LEFT JOIN and made it an INNER JOIN. You want to include records without a Part Number match, but that will not be possible with that DateDiff criteria. The only way to pass that DateDiff criteria is to have a value in [Review Date], the only way to have a value in [Review Date] is for the [Part Number] to match.

So, let's start from scratch. Can you provide me 2 sets of data?

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what you expect your query to produce when you feed it the data from A.

A simplified database with those tables would be best, or Excel.

 

[Galaxiom]

BTW You can also shorten the expressions. Replace this

(Sizing.[Curr Alpha])="XS" Or (Sizing.[Curr Alpha])="XP" Or (Sizing.[Curr Alpha])="XT" Or (Sizing.[Curr Alpha])="XL" Or (Sizing.[Curr Alpha])="XV" Or (Sizing.[Curr Alpha])="HS"

with

Sizing.[Curr Alpha] IN("XS", "XP", "XT", "XL", "XV", "HS")

 

[D.E.N.N.I.S.]

Your SQL might have a syntax error, but even if that gets fixed you have a logical error.

The first argument in your DateDiff needs to be enclosed in quotes (d->"d"). However, logically that DateDiff is working against you. When you add criteria to a field in a table that is in the LEFT JOIN (tblRackZeroOHBReviewed) you have effectively undone the LEFT JOIN and made it an INNER JOIN. You want to include records without a Part Number match, but that will not be possible with that DateDiff criteria. The only way to pass that DateDiff criteria is to have a value in [Review Date], the only way to have a value in [Review Date] is for the [Part Number] to match.

So, let's start from scratch. Can you provide me 2 sets of data?

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me what you expect your query to produce when you feed it the data from A.

A simplified database with those tables would be best, or Excel.

I put dummy data in the attached spreadsheet.

A. Tabs "Sizing" and "Reviewed" are the tables.

B. Tab "Query Results" is the query.

 

[plog]

I would remove the DateDiff from the WHERE clause, put the logic in a calculated field and then apply criteria to that calculated field.

In design view, open up your query, remove the DateDiff field and put this new field in it:

ReviewDateInclude: IIf(IsNull(DateDiff("d",[Review Date],Date())) Or DateDiff("d",[Review Date],Date())>365,1,0)

Then in the criteria section under that field, put "1". It will include null records as well as ones that are over 365 days since Review Date.

 

[D.E.N.N.I.S.]

Sorry it has been awhile since I have checked out this thread. I have been traveling and I put this project temporarily on hold. Thank you for your suggestion PLOG, that worked exactly how I wanted it too. Below is my SQL to memorialize if anyone else in the future is having the same issue.

SELECT Sizing.[Part Number], Sizing.Ohb, Sizing.Trans, Sizing.Location, Sizing.[Curr Alpha], Sizing.[Curr Cs1], Sizing.[Curr Cs2], Sizing.[Curr Max Qty], Sizing.[Sugg Alpha], Sizing.[Sugg Cs1], Sizing.[Sugg Cs2], Sizing.[Sugg Max Qty], Sizing.Weight, Sizing.[Order Qty], Sizing.[Order Point Qty], Now() AS [Review Date], fOSUserName() AS [Reviewed By], Sizing.Action, Sizing.[Additional Notes], IIf(IsNull(DateDiff("d",tblRackZeroOHBReviewed.[Review Date],Date())) Or DateDiff("d",tblRackZeroOHBReviewed.[Review Date],Date())>365,1,0) AS ReviewDateInclude, Sizing.[Part Number]
FROM Sizing LEFT JOIN tblRackZeroOHBReviewed ON Sizing.[Part Number] = tblRackZeroOHBReviewed.[Part Number]
WHERE (((Sizing.Ohb)=0) AND ((Sizing.[Curr Alpha]) In ("XS","XP","XT","XL","XV","HS")) AND ((Sizing.[Curr Max Qty])<>[Sizing].[Sugg Max Qty]) AND ((IIf(IsNull(DateDiff("d",[tblRackZeroOHBReviewed].[Review Date],Date())) Or DateDiff("d",[tblRackZeroOHBReviewed].[Review Date],Date())>365,1,0))=1))
ORDER BY Sizing.Trans DESC;