A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Save Query Results to Multiple Tables



 
 
Thread Tools Display Modes
  #1  
Old December 19th, 2008, 12:40 AM posted to microsoft.public.access.gettingstarted
Darrell
external usenet poster
 
Posts: 70
Default Save Query Results to Multiple Tables

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
  #2  
Old December 19th, 2008, 01:20 AM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Save Query Results to Multiple Tables

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

"Darrell" wrote in message
...
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



  #3  
Old December 19th, 2008, 05:40 PM posted to microsoft.public.access.gettingstarted
Darrell
external usenet poster
 
Posts: 70
Default Save Query Results to Multiple Tables

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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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




  #4  
Old December 19th, 2008, 07:31 PM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Save Query Results to Multiple Tables

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

"Darrell" wrote in message
...
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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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






  #5  
Old December 19th, 2008, 09:25 PM posted to microsoft.public.access.gettingstarted
Darrell
external usenet poster
 
Posts: 70
Default Save Query Results to Multiple Tables

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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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






  #6  
Old December 19th, 2008, 11:07 PM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Save Query Results to Multiple Tables

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


"Darrell" wrote in message
...
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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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








  #7  
Old December 22nd, 2008, 06:08 PM posted to microsoft.public.access.gettingstarted
Darrell
external usenet poster
 
Posts: 70
Default Save Query Results to Multiple Tables

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

"Jeff Boyce" wrote:

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


"Darrell" wrote in message
...
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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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

"Jeff Boyce" wrote:

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

"Darrell" wrote in message
...
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









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.