Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 08-26-2009, 12:49 PM   #1
Coach Ty
Registered User
 
Join Date: Aug 2009
Posts: 64
Thanks: 0
Thanked 0 Times in 0 Posts
Coach Ty is on a distinguished road
How to save and store Query Results

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!

Coach Ty is offline   Reply With Quote
Old 08-26-2009, 01:00 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How to save and store Query Results

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 08-26-2009, 02:50 PM   #3
Coach Ty
Registered User
 
Join Date: Aug 2009
Posts: 64
Thanks: 0
Thanked 0 Times in 0 Posts
Coach Ty is on a distinguished road
Re: How to save and store Query Results

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 is offline   Reply With Quote
Old 08-26-2009, 03:08 PM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How to save and store Query Results

Quote:
Originally Posted by Coach Ty View Post
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!
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:MyDateFieldName:Date()
Table:
Sort:
AppendTo:YourFieldNameInYourNewTable
Criteria: (put any criteria here if any)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 08-26-2009, 03:24 PM   #5
Coach Ty
Registered User
 
Join Date: Aug 2009
Posts: 64
Thanks: 0
Thanked 0 Times in 0 Posts
Coach Ty is on a distinguished road
Re: How to save and store Query Results

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?
Coach Ty is offline   Reply With Quote
Old 08-26-2009, 03:25 PM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How to save and store Query Results

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 08-26-2009, 03:46 PM   #7
Coach Ty
Registered User
 
Join Date: Aug 2009
Posts: 64
Thanks: 0
Thanked 0 Times in 0 Posts
Coach Ty is on a distinguished road
Re: How to save and store Query Results

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!

Coach Ty is offline   Reply With Quote
Old 07-05-2012, 11:29 PM   #8
venku_m
Newly Registered User
 
Join Date: Jul 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
venku_m is on a distinguished road
Cool 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
-----------------
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));
venku_m is offline   Reply With Quote
Old 07-07-2012, 09:43 AM   #9
Ashtechsmith
Newly Registered User
 
Join Date: May 2012
Location: Ahmedabad,Gujarat,India
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Ashtechsmith is on a distinguished road
Re: How to save and store Query Results

  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.

Ashtechsmith is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
save results from a query into an Array? - Newb Question tembenite Modules & VBA 11 12-03-2013 02:07 PM
One Report format - output and save many report pdf's wstandis Reports 9 05-19-2009 01:34 AM
quick question on modules/procedures/functions/sub Cire Modules & VBA 8 05-25-2006 11:38 PM
Store Data from Query in Memory Muzicmn Queries 2 03-13-2006 03:16 AM
Run query monthly Mike Hughes Queries 1 06-17-2004 07:46 AM




All times are GMT -8. The time now is 07:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World