Saving a daily generated query for later lookup by date?

[Krypto3]

I have a query that reports the number of students that are currently enrolled in a given number of schools within an area for that given day that the query is ran. This number can fluctuate greatly day to day, so is there a way to create a table which can hold this information so that I can go back in history and look up what our numbers were on whatever given day (date). (The example below is just a representation of what I actually have.) The School number would be unique, as would the date (I assume), I would like to see all of our school on whatever given date to see what our numbers are or were. Thanks for any helpful advice.

I have an example of the query currently I have set up in access 2007:



School # School Name Qty Date
001 BLUE 300 02/26/2014
002 RED 468 02/26/2014
003 YELLOW 10 02/26/2014


VS

School # School Name Qty Date
001 BLUE 310 02/27/2014
002 RED 400 02/27/2014
003 YELLOW 8 02/27/2014

[June7]

Just apply date filter criteria to query or report.

SELECT * FROM table WHERE [Date] = [enter date value];

The [enter date value] input can be reference to textbox on form.

SELECT * FROM table WHERE [Date] = Forms!formname!textboxname;

Date is a reserved word. Should not use reserved words in naming convention.

Also avoid spaces and special characters/punctuation (underscore is exception) in naming convention.

[Krypto3]

That wouldn't work for me as the query's date changes each day and is not stored currently, so the only date to look up would be the current day. I think I just figured it out though. I created a table from the query, I think made the query into an append query that I can run each day as the data changes, the append would add the data to the table just below the previous days numbers. I can then run a query off this table to run the example you just demonstrated. I think this would work for me. I will just have to run it everyday, so may I can create a macro to do it for me upon closing the database? another question for another forum post i guess. Thanks for the help June7.

[June7]

I don't understand that. Show the original query you are using. If you have a parameter in query for current date, there is a way to do that and still not need another table.

I never build queries with dynamic parameters. I open filtered forms and reports.

Another table should not be needed.