Increment query is assigning random numbers

[Dmaccess]

Hello all,

I've been working on refining an established MS access database. There is a priority field in an import table, and it gets reset when you re-import data that matches entries already existing in the table, giving a priority 1 to the new data.

This uses an update query, with where the new priority is set at +1 from the existing priority (see attached picture). However, this is assigning random numbers greater than the existing priority, instead of incrementing all existing priorities by one.

I imported the one data field 4 times, this was the result:

priority
1
8
78
85

I expecting the priorities to be 1,2,3,4???? How do I get them to increment by 1, instead of randomly assigning integers?

I am confused, any help would be awesome.

Thanks in advance,

Dmac

[RuralGuy]

Show us the SQL view of that same query, please.

[Dmaccess]

Hi RuralGuy,

Thanks for your quick response. Here is the SQL:

UPDATE arlreadyexistingblocks 
INNER JOIN tbl_oreblocks 
ON (arlreadyexistingblocks.cut = tbl_oreblocks.cut) 
AND (arlreadyexistingblocks.substrip = tbl_oreblocks.substrip) 
AND (arlreadyexistingblocks.strip = tbl_oreblocks.strip) 
AND (arlreadyexistingblocks.pit = tbl_oreblocks.pit) 
SET tbl_oreblocks.priority = [tbl_oreblocks]![priority]+1;

Does that help? I'm very new to SQL, and trying to fumble my way through it all.

Thanks for your time,

Dmac

[RuralGuy]

Try setting the last line to:

SET tbl_oreblocks.priority = tbl_oreblocks.priority + 1;

[RuralGuy]

Does the criteria always yield just one record?

[Dmaccess]

It can yield anywhere from 1 to 30 records. The number of records for one area can also increase or decrease from one data import to the next. Tried that code mod and reimported the same data 3 times, and it gave priority numbers 1, 8 & 15, not 1,2 &3 as I would expect???

[RuralGuy]

I think it can get confused. Try two queries. One to make the select with the criteria and the next one that uses the first query's output to update the field.

[Dmaccess]

Thanks ruralguy. I'll have a play with splitting the queries up, but i'm still a little confused.

The guy who designed the database i'm working on wasn't very organised, and it's taken me ages to decipher how he's built everything up.

[RuralGuy]

I'm not very adept at queries but I can hack around for a while.

[Rabbit]

If the table's relationship is one-to-many or many-to-many, you will end up running that update query on the same record multiple times. You need a unique key to do it correctly.

[Dmaccess]

@ Rabbit: Thanks for the reply. I don't want to start playing with the existing table structure too much, the database I'm working on has a heap of data in it, and a lot of linked tables and I don't quite understand the relationships. I don't want to cause more trouble than I'm fixing! :)

Just a thought, if I can run a query and find all the priority 1 records, would there be a way to run a query and find the next highest priority for a record? Even if it is a random number?

Thanks for your help. Have a nice day :)

[Rabbit]

That's possible, but the important thing is to find a way to uniquely identify a record. Even if you were to implement what you want to do, unless you could get unique records, you will end up updating the same record multiple times. If the join is creating multiple records, you should use a subquery instead.