Save Query Results to Multiple Tables

  • Thread starter Thread starter Darrell
  • Start date Start date
D

Darrell

So I have successfully created my query against a reporting database and now
need to copy the data returned to a differnt table (and a back-up table) so
that each day when I run the query I get e new set of records and do not
update the records from the previous days query. I do not understand the
"Make-Table" query command, where it is or how to use it.... can anyone walk
me through the process for copying the query data into two differnt tables?

Darrell
 
Darrell

It sounds like you want to append new records to an existing table, not
create a totally new table.

If your existing table has a unique index set on the fields that YOU use to
decide uniqueness, you wont be able to add the same "new" record more than
once.

And if you are using an APPEND query (not an UPDATE query), you would only
be adding records, not updating them.

Remember to make a backup copy of the database (or two) before using any
action query that could append, update or delete.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thx for the assistance. When I created the original query I did select a
field that contains a unique ID for each record which would be the same UID
for the next set of records. If I understand your post correctly I will need
to change this and use an Access auto-generated UID so that each record has a
different UID? This will allow me to use the APPEND query to add new records,
yes?

ahhhh... so once I have my table (and back-up table) populated with the
first set of records I can then just run the APPEND query to add new records
and not the original query I used to produce the first set of records, yes?

Again thx a ton,
Darrell
 
Darrell

I may not be understanding your situation...

If you have a table with 6 fields, and the first 4 need to be unique, then
you can create an index in the table definition that spans those 4 and
requires No Duplicates.

That way, when you attempt to APPEND a new record, if Access finds a match
on those 4, it refuses to append the record (would cause a "duplicate" index
value). Note that this even works the FIRST time, since there'll be no
records in the table to start with, so every record you try to append should
go in (unless, of course, you try to add "duplicates" the first time).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

I have actually been able to get my table and append query to work as
desired. I now have two distinct sets of records in the same table, one set
dated 12/18 and one set dated 12/19. The date stamp is the only difference
between the two sets of records, with the exception of the one field I am
interested in monitoring for a change. So, I now have dulpicate records with
different date stamps and hopefully different data in one specific field.

Now I have to figure out how to match the duplicate records within a
"selected" (reporting period) date range, and then test for the change (or
delta) I am interested in. I then want to produce a report to show all
records within the reporting period "without" a change in the monitored field
from one date to the next. Basically I want the report to say "Show me all
tasks where the Actual Work field did not change from 12/18 to 12/19". i.e.
no update was applied!

Again thx a ton for the assistance,
Darrell
 
One approach might be to create two queries, one for each date.

Then use something like an "unmatched" query between those two to find
records in one not in the other.

(or maybe I'm still not quite grasping your situation...<g>)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thx for all of your help. I was able to create a table and several queries
to bring the information I was interested in into one report that shows every
task in the MSPS 2007 Reporting Database that has no change in the Actual
Work field within a selected date range (usually two consecutive work days).

Thx again,
Darrell
 
Back
Top