How to save and store Query Results (1 Viewer)

Hello,
Can any one tell me what the best way is to save and store query results, by date?
I need to run weekly statistics and then save the results.
I've already developed the queries to produce the stat results I need.
I now need to store the values every week, in order to run a performance review report at the end of every month.

Right now I can input the information into the table that includes every employees information.
Then run the queries to produce the desired stats.
Once these stat results are produced, I need to save and store the stats for that date.
When I run stats now, I go into the existing stat table to input new information. The last record always comes up and I modify the record.
I simply modify the information and change the date, to run the queries. But I'm only modifying the same record over and over again.
I need to have a way to save the query results in a table every time, so that I can run a report at the end of every month, that I'll have the ability to see what the same stats were, for every date, the queries were ran. In order to have the ability to track overall performance over time.
I'm not sure if I should run a make table query every time or run an update or append query to modify an existing table?
Can anyone tell me the best way to do this?
Thanks for your help!

Why not just build a table to hold your data and you can have one additional field to store the reporting period. Then, you can just use a single append query whenever you need to do this.

Hello,
Thanks for your reply.
Right now I have a single table that contains the employee information for twenty employees. There are a number of fields that contain the various performance categories.
The scoring information for each individual category is input into this table.
The various scoring information is then combined to produce the desired statistics through calculated expression queries.
So, I can't modify the primary table because the information has to first be input into this table to run the queries.
Are you saying to produce an additional table to store the information?
Maybe, I can then create an overall query to house all of the individual queries and do a make table query from that, to produce the additional table. Is that it?
Then your saying to run an append query each week to modify this table?
I'd like it to look like this:

Date ________ Name _________ Stat 1 _________ Stat 2 ________ Ect
Date ________ Name _________ Stat 1 _________ Stat 2 ________ Ect
Date ________ Name _________ Stat 1 _________ Stat 2 ________ Ect

Will the append query insert the additional information for every employee in decending order according to date?
Please let me know if this is what your saying. If not could you please clarify?
Thanks!

Coach Ty said:

Will the append query insert the additional information for every employee in decending order according to date?
Please let me know if this is what your saying. If not could you please clarify?
Thanks!

Click to expand...

Well, it will put it in there if you add the field to the query. So, build the table with the fields you want including the date field.

Then, in the query by example grid you can change from a select query to an append query and then to add the date field you would have something like:

Field:MyDateFieldNameate()
Table:
Sort:
AppendTo:YourFieldNameInYourNewTable
Criteria: (put any criteria here if any)

So, I was right then?
Step 1: make an additional Table to house the stat information from the query results. By creating a make table query, with a date column as you stated. Then ...
Step 2: Do an Append Query to append that table, which includes the Date) date field and append that to the new table.
Is that about it?

That is it. And then when you want info from that table, you can query for a specific date or a date range and that will let you do comparisons, data aggregation, etc.

Hey,
Thanks so much!
Really new to this as you can see ...

I have another question, if you don't mind ...
Later on in the program I'll be placing employees into various positions according to their rank in the various categories. Here's the problem:
People can't be placed into positions multiple times.
The positions are ranked according to priority.
The problem is that the same employee may have the top rank in several categories.
I need the system to place one employee per position.
If the employee who fills the first position is also the top candidate in the category to fill the second position, I need the system to eliminate (or block) the first employee from the next step, so they won't also be placed in the next position.
In other words after an employee is placed in a position according to rank, They are then blocked from future steps in the process and the system will roll down to the next available candidate.
I have a few ideas about this one ... but I thought I'd consult an expert
Let me know if you have any ideas about this one and thanks again!

Re: Iniline query to self join & select results

Actually I am new to MS Access but have years of exp in SQL (but been out of touch for last 2 years).. I just logged into this forum and found this question. Tried inline query concept and it really works! MS Access rocks! but the query :banghead:
-----------------
Created a table Emp_Stat_test with Emp_No and file stat columns. Populated 5 employee records with different stat values. And then this query I created is working!

SELECT S4.Stat1Emp, S4.Stat1, S4.Stat2Emp, S4.Stat2, S4.Stat3Emp, S4.Stat3, S4.Stat4Emp, S4.Stat4, E5.Emp_No AS Stat5Emp, E5.Stat5
FROM [SELECT S3.Stat1Emp, S3.Stat1, S3.Stat2Emp, S3.Stat2, S3.Stat3Emp, S3.Stat3, E4.Emp_No AS Stat4Emp, E4.Stat4 From
(Select S2.Stat1Emp, S2.Stat1, S2.Stat2Emp, S2.Stat2, E3.Emp_No As Stat3Emp, E3.Stat3 From
(Select S1.Stat1Emp, S1.Stat1, E2.Emp_No As Stat2Emp, E2.Stat2 From
(Select E1.Emp_no As Stat1Emp, E1.Stat1 From Emp_stat_test E1 Where E1.Stat1 = (Select Max(Sub1.Stat1) From Emp_stat_test As Sub1)) As S1, Emp_stat_test E2
Where E2.Stat2 = (Select Max(Sub2.Stat2) From Emp_stat_test As Sub2 Where NOT (Sub2.Emp_No = S1.Stat1Emp))) As S2, Emp_stat_test E3
Where E3.Stat3 = (Select Max(Sub3.Stat3) From Emp_stat_test As Sub3 Where NOT (Sub3.Emp_No = S2.Stat1Emp OR Sub3.Emp_No = S2.Stat2Emp))) AS S3, Emp_stat_test AS E4
WHERE E4.Stat4 = (Select Max(Sub4.Stat4) From Emp_stat_test As Sub4 Where NOT (Sub4.Emp_No = S3.Stat1Emp OR Sub4.Emp_No = S3.Stat2Emp OR Sub4.Emp_No = S3.Stat3Emp))]. AS S4, Emp_stat_test AS E5
WHERE E5.Stat5 = (Select Max(Sub5.Stat5) From Emp_stat_test As Sub5 Where NOT (Sub5.Emp_No = S4.Stat1Emp OR Sub5.Emp_No = S4.Stat2Emp OR Sub5.Emp_No = S4.Stat3Emp OR Sub5.Emp_No = S4.Stat4Emp));

1. Run the select query to verify that the results are what you expect.
2. Make-table queries, like other action queries, are not enabled in a database that is not in a trusted location and that does not have a security certificate. If the database that you are using does not enable action queries, the following message appears in the Message Bar beneath the Ribbon, part of the Microsoft Office Fluent user interface:

To enable action queries, on the Message Bar, click Options, and then click Enable this content.
If the Message Bar does not appear, action queries may already be enabled in the database that you are using, or you may need to display the Message Bar by doing the following:

• On the Database Tools tab, in the Show/Hide group, click Message Bar.

Note If Message Bar is unavailable (dimmed), there are no messages to display. In this case, action queries are already enabled.

1. On the Home tab, in the Views group, click View, and then click Design View.
2. On the Design tab, in the Query Type group, click Make Table.

The Make Table dialog box appears.

1. By default, the new table is created in the current database. If you want to save the table in another database, click Another Database, and then type the path and file name of that database, or click Browse to browse for the database.
2. In the Table Name box, type a name for the new table. If you want to replace an existing table, click the arrow in the Table Name box, and then, in the drop-down list, select the table that you want to replace.
3. On the Design tab, in the Results group, click Run.

1. If you are replacing an existing table, Office Access 2007 displays a warning that the existing table will be deleted. Click OK to confirm the table deletion.

A confirmation dialog box appears.

1. In the confirmation dialog box, click Yes to paste the rows into a new table.