Reports Database by Date and Criteria

[soldat452002]

Hello,

I have a report database that provides my company with clients that took our training modules and notify us of which clients completed our trainings.
The clients can complete training in 3 States and "Passed" means they are good to go.


I download an excel report daily and import it to Access on a daily basis. Problem is the Report is over 8,000 rows long
and basically I just need the clients that completed training within past 48 hours. The excel report provides 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.

SELECT report.SPS, report.FirstName, report.region, report.id, report.AZ_Cert, report.AZStatus, report.CA_CERT, report.CAStatus, report.OR_CERT, report.ORStatus, report.Completed FROM report WHERE (((report.Completed)=False));


The completed checkbox removes the record from the cert queue. How can I do this more efficiently? I think I have it right.

Private Sub Completed_Click()
Const cstrPrompt As String = _
"Are you sure you want to complete this record? Yes/No"
If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
If Me.Dirty Then
Me.Dirty = False ' save the record
Forms!frmRecertView.subfrmRecert.Requery
End If
End If
End Sub


I uploaded a screen shot of my database.

 

[GinaWhipp]

To pull only those that have *Passed* in a rolling 48 hour period...

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()-2 And Date()) AND ((CMPreport.ORStatus)="Passed") AND ((CMPreport.Completed)=False));

Note: Doing rolling days because you aren't storing time.

Nothing to do with the other part looks fine to me and it works!

 

[soldat452002]

Seems that when I have to query "Passed" and "Between Date()-2 And Date()" Plus "False" for all three states, the query does not work.

 

[GinaWhipp]

Oh, didn't realize you wanted it across all three. One thing that is missing is the Completed and Passed the same on all 3 lines.

EDIT: I also just checked and realize nothing does fall within those parameters. To test you'll need to adjust the dates.

 

[GinaWhipp]

Here you go...

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()-2 And Date()) AND ((CMPreport.ORStatus)="Passed") AND ((CMPreport.Completed)=False)) OR (((CMPreport.CA_CERT) Between Date()-2 And Date()) AND ((CMPreport.CAStatus)="Passed") AND ((CMPreport.Completed)=False)) OR (((CMPreport.AZ_Cert) Between Date()-2 And Date()) AND ((CMPreport.AZStatus)="Passed") AND ((CMPreport.Completed)=False));

Just remember, something has to fall within those parameters to show.

 

[soldat452002]

That SQL Statement works like magic!!!
I have a question for you, in Queries the more criteria you muster, the less the query works?


SELECT CMPreport.SPS, CMPreport.Name, CMPreport.AZ_PT, CMPreport.AZStatus, CMPreport.CA_PT, CMPreport.CAStatus, CMPreport.OR_PT, CMPreport.ORStatus, CMPreport.FWA, CMPreport.FWAStatus, CMPreport.Compliance, CMPreport.CompStatus, CMPreport.Recert, CMPreport.Reject
FROM CMPreport
WHERE (((CMPreport.AZ_PT) Between Date()-30 And Date()) AND ((CMPreport.AZStatus)="Passed") AND ((CMPreport.FWA) Between Date()-10 And Date()) AND ((CMPreport.FWAStatus)="Passed") AND ((CMPreport.Compliance) Between Date()-10 And Date()) AND ((CMPreport.CompStatus)="Passed") AND ((CMPreport.Recert)=False) AND ((CMPreport.Reject)=False)) OR (((CMPreport.CA_PT) Between Date()-30 And Date()) AND ((CMPreport.CAStatus)="Passed") AND ((CMPreport.FWA) Between Date()-10 And Date()) AND ((CMPreport.FWAStatus)="Passed") AND ((CMPreport.Compliance) Between Date()-10 And Date()) AND ((CMPreport.CompStatus)="Passed") AND ((CMPreport.Recert)=False) AND ((CMPreport.Reject)=False)) OR (((CMPreport.OR_PT) Between Date()-30 And Date()) AND ((CMPreport.ORStatus)="Passed") AND ((CMPreport.FWA) Between Date()-10 And Date()) AND ((CMPreport.FWAStatus)="Passed") AND ((CMPreport.Compliance) Between Date()-10 And Date()) AND ((CMPreport.CompStatus)="Passed") AND ((CMPreport.Recert)=False) AND ((CMPreport.Reject)=False));

 

[GinaWhipp]

Well, you *can* slow down the speed in which it returns its results if it's got a LOT of records to go thru BUT even this depends on what you are asking it to do because generally you only want to pull the records you need so.... *trick* question.

Now, if you are asking about what you posted, yep, I could see that slow down the return because of all the calculating it has to do but it should be offset by the fact it's returning far less data. Now, that said proper indexing can counter any effects you might get... hmm, read this...

http://allenbrowne.com/QueryPerfIssue.html