G
Guest
Trying to use old report, but how to update to new table?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick B
You might want to post a detailed question with complete sentences. What is
"old", Access 97? Access 2000? What is the "new table"? Reports don't
typically update tables.
Please repost with details of what you are trying to accomplish.
"old", Access 97? Access 2000? What is the "new table"? Reports don't
typically update tables.
Please repost with details of what you are trying to accomplish.
G
Guest
Sorry. I have never used this format and it said to be very brief.
I have a report that uses 2006 data from a table. I have a new table with
new data for 2007. I want to use the old report format save it as a new
report and remove the 2006 table and replace it with the 2007 table
information.
I have a report that uses 2006 data from a table. I have a new table with
new data for 2007. I want to use the old report format save it as a new
report and remove the 2006 table and replace it with the 2007 table
information.
R
Rick B
Whoa. That is going to turn into a lot of work every year. Why not put all
your data in ONE table and include a date field? That would follow
relational database normalization rules.
Then, when you run the report you have two options. Either code the query
to only pull the current year's details, or ask the user which year to use.
Anytime you have to have the database developer jump through hoops when a
new year rolls around, you can bet there is a better way to do it.
Hope that helps.
your data in ONE table and include a date field? That would follow
relational database normalization rules.
Then, when you run the report you have two options. Either code the query
to only pull the current year's details, or ask the user which year to use.
Anytime you have to have the database developer jump through hoops when a
new year rolls around, you can bet there is a better way to do it.
Hope that helps.
J
John Vinson
I have a report that uses 2006 data from a table. I have a new table with
new data for 2007. I want to use the old report format save it as a new
report and remove the 2006 table and replace it with the 2007 table
information.
I absolutely agree with Rick about the table design question: storing
data in tablenames IS SIMPLY WRONG and will cause you far more trouble
than benefit.
As a temporary getaround while you're fixing your table structure, you
can open the Report in design view; view its Properties; select the
Data tab; and change the Recordsource property to your new table (or
change the Query referenced there to point to the new table by
clicking the ... icon and changing the query definition).
John W. Vinson[MVP]
G
Guest
Yes. I guess I would normally agree, but these tables come from an outside
government source that we have no control over. They send text files every
year and you never know what data will be included or for what companies. I
would be very difficult to try to merge these lists. It is just easier to
start a new report using the new data. And, there is never any reason to run
queries on old data. I thank you both for your advice though. I wish I could
use it.
government source that we have no control over. They send text files every
year and you never know what data will be included or for what companies. I
would be very difficult to try to merge these lists. It is just easier to
start a new report using the new data. And, there is never any reason to run
queries on old data. I thank you both for your advice though. I wish I could
use it.
L
Larry Linson
The _Tables_ come as Text Files? Isn't it more likely that the _data_ comes
as Text Files, to which you could link, or which could be imported into a
temporary table and manipulated from there to add the information to the
existing table, adding the year indicator?
You may have no control over what the government agency sends, but can't you
control what you _do_ with it?
How about this scenario:
1. Import text file into a Temporary Table.
2. Create a Query to determine which of the Records in the new Table are
already in the Permanent Table.
3. Use the Query from 2 above to create an Update Query to update the
information in the existing Records. Also update the "last updated" date, if
appropriate.
4. Create a Query to determine which of the Records in the new Table are
not already in the Permanent Table
5. Use the Query that indicates which Tables are not in the Permanent
Table to create an append Query to add them, along with the "last updated"
date.
6. Determine if you need to do any "deletions" based on the new data;
and do them.
Larry Linson
Microsoft Access MVP
as Text Files, to which you could link, or which could be imported into a
temporary table and manipulated from there to add the information to the
existing table, adding the year indicator?
You may have no control over what the government agency sends, but can't you
control what you _do_ with it?
How about this scenario:
1. Import text file into a Temporary Table.
2. Create a Query to determine which of the Records in the new Table are
already in the Permanent Table.
3. Use the Query from 2 above to create an Update Query to update the
information in the existing Records. Also update the "last updated" date, if
appropriate.
4. Create a Query to determine which of the Records in the new Table are
not already in the Permanent Table
5. Use the Query that indicates which Tables are not in the Permanent
Table to create an append Query to add them, along with the "last updated"
date.
6. Determine if you need to do any "deletions" based on the new data;
and do them.
Larry Linson
Microsoft Access MVP