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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|