Update Query ignores "Unique Records" (1 Viewer)

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.

If this is a one-time deal I would suggest you split the update in incremental steps. First get your old data into a temporary table (by using maketable, delete/append, update queries) where you can check and make sure you have it right. Then use that in your final update; I find this approach a lot easier than the "all-or-nothing-one query- does-it-all" one.

Cheers,
Vlad

(This # is the same I get from a similar select query without unique records required)
Instead of a select query try to run a "Make Table" query, then look at the number of records, is that the same as the select query.

Taking JHBs point a step further, save the required records to a temp table. If its a one off use a make table otherwise append.
Then update using the temp table.
Finally either delete or empty the temp table depending on whether its a one off process or not.
If the latter then the table is temporary in terms of its records.

I think all of these responses together have put an idea in my mind. It's not a one-time deal, it's a process I need to store for other users. I am already importing the data into a temporary table & then modifying it before appending new data.

I was so hung up on figuring out the update query, I didn't think of taking the modifications to the temp table even further. I don't think I can directly replace the target table this way, as it has a LOT of dependent relationships, but I could get the temp table a little closer to it & see if the update table behaves better.

Thanks everyone, will report back.

Suggest you do the same process as I use when importing data from new CSV files each night.
1. Link the CSV files with all fields as Text datatype.
2. Import to local IMPORT tables .
3. Process the data as necessary so it is in the required format for each field.
4. Use APPEND, UPDATE and DELETE queries or SQL to synchronise the data in the BE with the CSV files

Step 3 is crucial to this being successful without the need for human intervention

Thanks everyone! As isladogs says, I've increased the processing before updating. I now connect the data using Select queries and write the new auto-number IDs to the incoming tables. THEN I run the update queries using the IDs as my only join. No more DISTINCTROW troubles.

It took me all day to get the TableDefs and CreateFields working properly on my Temp tables. I was running into issues with relationships that I needed to build the queries preventing tables from deleting, which then rolled my next attempt at import to a TempTable1, which upset my field creators... But it's now stable & I think you all...

still learning...