Can I create a query that compares Today's report with Yesterday's report, and generates a number based on how many more "new" records are on Today's report? I'm tired of having to open both, then compare. Thank you! 
Comparing report numbers?
- Thread starter lilyaqha
- Start date
David R
I know a few things...
- Local time
- Today, 07:56
- Joined
- Oct 23, 2001
- Messages
- 2,632
I think you'll want to play with the DCount() function. Your parameters for the WHERE part will determine how many records it pulls, and then simple math will give you your answer.
Post back with more details if you need more help/get stuck.
Post back with more details if you need more help/get stuck.
David R
I know a few things...
- Local time
- Today, 07:56
- Joined
- Oct 23, 2001
- Messages
- 2,632
Easiest way to build it when you're just starting out is to build a query that pulls the data you want in Query Design, then read the SQL and get the piece you want.
So: Drag your [record table] into Design View in TWO new queries. Drag the * down into the fields, and then drag down the field that contains your datestamp (probably something like [RecordCreated]). If it's a date field only, your criteria can just be Date() for Today's total, and Date()-1 for yesterday's. If it's a datetime stamp, it'll be more like
for Today
for Yesterday.
Run these queries and verify that they're giving you the right number of records. Then go back to the top left where you flip to Design View, only this time flip to SQL.
DON'T PANIC! All you need is the table name and the piece between WHERE and the ; at the end. Copy that to your clipboard; it'll be something like:
So now you can actually make an unbound field in your report, with control source:
You can test this first in the VBA Immediate Window if you're brave and daring and bold. With time you'll understand how to construct WHERE clauses without the Design View window step.
So: Drag your [record table] into Design View in TWO new queries. Drag the * down into the fields, and then drag down the field that contains your datestamp (probably something like [RecordCreated]). If it's a date field only, your criteria can just be Date() for Today's total, and Date()-1 for yesterday's. If it's a datetime stamp, it'll be more like
Code:
Between Date() And Date()+1
Code:
Between Date() And Date()-1Run these queries and verify that they're giving you the right number of records. Then go back to the top left where you flip to Design View, only this time flip to SQL.
DON'T PANIC! All you need is the table name and the piece between WHERE and the ; at the end. Copy that to your clipboard; it'll be something like:
Code:
(((tableRecords.RecordCreated) Between Date() And Date()+1))So now you can actually make an unbound field in your report, with control source:
Code:
=DCount("*","tableRecords","PASTETHATWHERECLAUSEHERE")can I post a picture of what i'm working with here? I have the 2 tables. Created the queries, but the date thing threw me, because I don't have dates. The count would be of DSI number. Oh I can't, I need 7 more posts to be able to post the pic. BRB.
Some explanation..I pull the same report, daily. The records are all students who are enrolled in a particular course. Everyday there are more students enrolled in this course. I pull the group of students, sort for the students in the particular course, then send out the report. What I was trying to do was eliminate the process of having to open the previous day's report to check that the enrollment had gone up. (a sign the report is correct).
Hope that makes sense,
ETA: When I say 'report" I mean query that I just send as an excel doc. not an access generated "report".
Hope that makes sense,
ETA: When I say 'report" I mean query that I just send as an excel doc. not an access generated "report".
David R
I know a few things...
- Local time
- Today, 07:56
- Joined
- Oct 23, 2001
- Messages
- 2,632
So each day you pull/relink to the report? Where do you store yesterday's data to compare it to?
Thinking, thinking... I notice in your picture your table is called Eng_Today_All. Does that let me assume safely there is a link to Eng_Yesterday_All? If so you could just DCount on both separately and be done with it (it's terrible database design, but you're kind of using Access as a sledgehammer to swat a fly right now anyway, unless I totally misunderstand your business process).
Thinking, thinking... I notice in your picture your table is called Eng_Today_All. Does that let me assume safely there is a link to Eng_Yesterday_All? If so you could just DCount on both separately and be done with it (it's terrible database design, but you're kind of using Access as a sledgehammer to swat a fly right now anyway, unless I totally misunderstand your business process).
Ok, so each day i'm sent an excel document of all students in all classes. I have to sort to get the particular course I need, then add addresses from another excel report i'm sent. There is a 3rd report i'm sent that has home campus and delivery campus, which I need to sort as well.
I need 1 tab with all students with this course, and 1 tab with online home campus only.
So, I have 3 imported tables. Sort 1 to get the students/course I need. Have a query to add the home campus. Have 2 final queries to put all together. Have a form with VB to send report to folder with current date for file name.
I keep the previous days report to compare to be sure the number is going up like it is supposed to.
Like I said, i'm new so i'm not sure of any othe rway to do this (yet!)
Thanks for putting up with all this!
I need 1 tab with all students with this course, and 1 tab with online home campus only.
So, I have 3 imported tables. Sort 1 to get the students/course I need. Have a query to add the home campus. Have 2 final queries to put all together. Have a form with VB to send report to folder with current date for file name.
I keep the previous days report to compare to be sure the number is going up like it is supposed to.
Like I said, i'm new so i'm not sure of any othe rway to do this (yet!)
Thanks for putting up with all this!
David R
I know a few things...
- Local time
- Today, 07:56
- Joined
- Oct 23, 2001
- Messages
- 2,632
Yeah, easiest way then is going to be a DCount on each day's table separately (filtered down to that course, probably). Your email form presumably already holds the course name, destination email, etc?
Second easiest, if you only need to send a summary report not one for each course, would be two Totals Queries that you then do math on in a third query: Increase: Today_Total - Yesterday_Total
Second easiest, if you only need to send a summary report not one for each course, would be two Totals Queries that you then do math on in a third query: Increase: Today_Total - Yesterday_Total
David R
I know a few things...
- Local time
- Today, 07:56
- Joined
- Oct 23, 2001
- Messages
- 2,632
Your code and SQL will be simpler if you are willing to just reupload/rename the linked tables each time as Report-today and Report-yesterday. You can do the same thing in VBA but you may not want to fiddle with all of that!
David R
I know a few things...
- Local time
- Today, 07:56
- Joined
- Oct 23, 2001
- Messages
- 2,632
DCount(Expr, Domain, [Criteria])
Expression is the part you're looking to count. in your case a simple "*" will suffice, since you just want to know how many records there were.
Domain is a silly name for the table/query you're counting. For one of your purposes this will be "Eng_Today_All" (unless you've changed your names).
Criteria is optional, but it works just like query criteria: it lets you ratchet down the results to a particular course or date range or student or whatever. In fact it uses the same syntax as query SQL, so you can design it there and then copy and paste.
So one of your calculated fields MIGHT look like:
. Try it and see!
Expression is the part you're looking to count. in your case a simple "*" will suffice, since you just want to know how many records there were.
Domain is a silly name for the table/query you're counting. For one of your purposes this will be "Eng_Today_All" (unless you've changed your names).
Criteria is optional, but it works just like query criteria: it lets you ratchet down the results to a particular course or date range or student or whatever. In fact it uses the same syntax as query SQL, so you can design it there and then copy and paste.
So one of your calculated fields MIGHT look like:
Code:
Today_Total: DCount("*","Eng_Today_All")
