I have table for daily data entry and it has these fields [ID],[ReportDate][ProdNumber],[SerialNumber], [Status] and [EndingNumber] and the table name is [DailyReports]. What I like to have is query that will give me all products that had [EndingNumber] the day before where the [Status]=1 but has no entry on today. The reason is I like to find if someone forgot to enter product that should have ending number.

attached the data here. In the query MissigSales product with serial number 0789-100000 there were ending number yesterday but no data entry with today's date. I would like the query to show this product.

Thanks

I have table for daily data entry and it has these fields [ID],[ReportDate][ProdNumber],[SerialNumber], [Status] and [EndingNumber] and the table name is [DailyReports]. What I like to have is query that will give me all products that had [EndingNumber] the day before where the [Status]=1 but has no entry on today. The reason is I like to find if someone forgot to enter product that should have ending number.

attached the data here. In the query MissigSales product with serial number 0789-100000 there were ending number yesterday but no data entry with today's date. I would like the query to show this product.

ThanksI don't think you can do with one query here. you have two criteria for the ENDINGNUMBER, and one needs a subquery.

maybe this would work:SELECT * FROM DailyReports
WHERE [Status] = 1 AND [EndingNumber] Is NullThen a stacked query on top of that:SELECT * FROM query
WHERE [EndingNumber] Is Not Null AND
[ReportDate] = DateAdd("d", -1, Date())

I don't think you can do with one query here. you have two criteria for the ENDINGNUMBER, and one needs a subquery.

maybe this would work:SELECT * FROM DailyReports
WHERE [Status] = 1 AND [EndingNumber] Is NullThen a stacked query on top of that:SELECT * FROM query
WHERE [EndingNumber] Is Not Null AND
[ReportDate] = DateAdd("d", -1, Date())

I'm not sure how to do subquery but when I tried this I got error.

Try this sql


SELECT TbleDailyReports.ID,
TbleDailyReports.ReportDate,
TbleDailyReports.ProductNumber,
TbleDailyReports.SerialNumber,
TbleDailyReports.Status,
TbleDailyReports.EndingNumber,
TbleDailyReports_1.ProductNumber
FROM TbleDailyReports LEFT JOIN
TbleDailyReports AS TbleDailyReports_1 ON
(TbleDailyReports.ReportDate = TbleDailyReports_1.ReportDate-1) AND
(TbleDailyReports.ProductNumber = TbleDailyReports_1.ProductNumber) AND
(TbleDailyReports.SerialNumber = TbleDailyReports_1.SerialNumber)
WHERE (((TbleDailyReports.ReportDate)=Date()-1) AND
((TbleDailyReports.Status)=1) AND
((TbleDailyReports.EndingNumber) Is Not Null) AND
((TbleDailyReports_1.ProductNumber) Is Null));