Query with many criteria (Dates, Condition, etc)

[soldat452002]

Hello,



I have a report database that provides my company with those clients that took our training modules and completed successfully the tests.
The clients can complete training in 3 States and "Passed" means they have completed.


I import an Excel report into Access on a daily basis. Problem is the Report is over 8,000 rows long and basically the only thing I need is those clients that completed training within past 48 hours. The excel report provides us with a date of completion.

Can someone please assist me with the code that only pulls those clients that "Passed" within the last 48 Hours. Here is my SQL Statement I use on the RecordSource. I attached my sample database.

[June7]

What's wrong with the query you have?

[soldat452002]

What's wrong with the query you have?

The query does not filter "Passed" for any of three states in the last 48 hours. Then I have a checkbox that only should show False.
It brings up all records.

[June7]

The query is working as designed. The criteria requires a date value to be between a date range AND "Passed" status. If not within the date range, the record will retrieve regardless of status.

Real source of issue is this is not a normalized data structure. Filtering multiple fields that are really the same type data is not easy. There are 3 certification fields and their associated Status fields. It appears not every record has all 3. Normalized would be another related table with 2 fields for the certification value and status value. Each SPS could have up to 3 records in this related table.

The completed checkbox field appears redundant.

Need to clarify the filter rules.

If any of the 3 certifications has Passed status, do not retrieve record? Or if any of the 3 certifications is not Passed retrieve record?

[soldat452002]

Hi, I got it working on this SQL Statement. What I wanted was: If any of the 3 certifications has been "Passed" retrieve the record for those completed
in the last 48 hours. I'm not sure if this SQL Statement is the most efficient, but I think it works. Maybe you have a better idea, i'm a beginner in SQL.


SELECT CMPreport.SPS, CMPreport.FirstName, CMPreport.region, CMPreport.id, CMPreport.AZ_Cert, CMPreport.AZStatus, CMPreport.CA_CERT, CMPreport.CAStatus, CMPreport.OR_CERT, CMPreport.ORStatus, CMPreport.Completed
FROM CMPreport
WHERE (((CMPreport.OR_CERT) Between Date()-30 And Date()) AND ((CMPreport.ORStatus)="Passed") AND ((CMPreport.Completed)=False)) OR (((CMPreport.CA_CERT) Between Date()-30 And Date()) AND ((CMPreport.CAStatus)="Passed") AND ((CMPreport.Completed)=False)) OR (((CMPreport.AZ_Cert) Between Date()-30 And Date()) AND ((CMPreport.AZStatus)="Passed") AND ((CMPreport.Completed)=False));

The query is working as designed. The criteria requires a date value to be between a date range AND "Passed" status. If not within the date range, the record will retrieve regardless of status.

Real source of issue is this is not a normalized data structure. Filtering multiple fields that are really the same type data is not easy. There are 3 certification fields and their associated Status fields. It appears not every record has all 3. Normalized would be another related table with 2 fields for the certification value and status value. Each SPS could have up to 3 records in this related table.

The completed checkbox field appears redundant.

Need to clarify the filter rules.

If any of the 3 certifications has Passed status, do not retrieve record? Or if any of the 3 certifications is not Passed retrieve record?

[June7]

Glad you got a solution.

The only improvement would be to normalize data structure.