snow-raven
Registered User.
- Local time
- Today, 02:51
- Joined
- Apr 12, 2018
- Messages
- 48
Hi all,
I'm working on import and update queries to bring data from an older format database to a newer, better model. There were a few things about the original that made it not perfectly normalized, so I'm addressing that in my latest version.
Most of my update queries have worked, but one is causing me trouble. It's set to update 'unique records' and the datasheet preview has the correct # of records. However, when I launch the update, it says it's updating a much higher #. (This # is the same I get from a similar select query without unique records required). If I let it finish, I end up with the correct number of records, but the screwy data from the incorrect version. (Basically, my data becomes the first 522 lines of 7308 records. I have no idea why it would be a multiple of 14, that doesn't correlate with any # of records that I can find.)
I need to pass data from external databases to the home office. Basic format of the tables is:
Project Phase (top table)
Site Location (with project phase unique ID as a field)
Data Tables (each has a unique key and a field that stores the unique ID of the site location)
Each new table has a unique auto-numbered key ID. The two old tables also have unique keys, but they weren't auto-numbered. My query structure ends up with the project phase in the middle and two site tables (one temp old, one new) and two data tables (one temp old, one new).
Here is an abbreviated view of my query:
Any ideas for better ways I could address this? I'll be launching this from a form with a bunch of code designed to import tables from the old version to the new, so I'm open to approaching this without the update query.
I'm working on import and update queries to bring data from an older format database to a newer, better model. There were a few things about the original that made it not perfectly normalized, so I'm addressing that in my latest version.
Most of my update queries have worked, but one is causing me trouble. It's set to update 'unique records' and the datasheet preview has the correct # of records. However, when I launch the update, it says it's updating a much higher #. (This # is the same I get from a similar select query without unique records required). If I let it finish, I end up with the correct number of records, but the screwy data from the incorrect version. (Basically, my data becomes the first 522 lines of 7308 records. I have no idea why it would be a multiple of 14, that doesn't correlate with any # of records that I can find.)
I need to pass data from external databases to the home office. Basic format of the tables is:
Project Phase (top table)
Site Location (with project phase unique ID as a field)
Data Tables (each has a unique key and a field that stores the unique ID of the site location)
Each new table has a unique auto-numbered key ID. The two old tables also have unique keys, but they weren't auto-numbered. My query structure ends up with the project phase in the middle and two site tables (one temp old, one new) and two data tables (one temp old, one new).
Here is an abbreviated view of my query:
Code:
UPDATE DISTINCTROW
(Project_Info INNER JOIN
(TempSite INNER JOIN
TempSample_Intervals
ON TempSite.Site_ID = TempSample_Intervals.Site_ID)
ON Project_Info.Project_Phase = TempSite.Project_Phase)
INNER JOIN
(Site INNER JOIN
Sample_Intervals
ON Site.Site_ID = Sample_Intervals.Site_ID)
ON Project_Info.Project_ID = Site.Project_ID
SET
{update many fields here}
WHERE (((Sample_Intervals.Samp_From)=[TempSample_Intervals].[Samp_From]) AND ((Sample_Intervals.Samp_To)=[TempSample_Intervals].[Samp_To]) AND ((Site.Site_Num)=[TempSample_Intervals].[Site_ID]));
Any ideas for better ways I could address this? I'll be launching this from a form with a bunch of code designed to import tables from the old version to the new, so I'm open to approaching this without the update query.