how to find all records entered yesterday and none today

[hmho]

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

 

[ajetrumpet]

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 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 Null

Then a stacked query on top of that:

SELECT * FROM query
   WHERE [EndingNumber] Is Not Null AND
      [ReportDate] = DateAdd("d", -1, Date())

 

[hmho]

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 Null

Then 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.

 

[khawar]

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));