Update queries 2

[hoofit]

Good morning,

I’m in a corner. In a db that tracks equipment and their meters, I need to ‘delete’ a meter. Well, not actually delete it, just re-number it. I set up an update query with the following SQL.

UPDATE TblRunningMeter SET TblRunningMeter.MeterID = 1000
WHERE (((TblRunningMeter.MeterID)=1) AND ((TblRunningMeter.UnitID)=[forms]![frm_viewmeters]![unitID]));

This particular arrangement re-numbers meter1 to meter 1000 and work just fine. The problem arises when I want to re-number meter 1 again….I end up with 2 meters numbered 1000….duplicate records. Is there a way to increment in SQL so I end up with sequentially numbered meters like 1000, 1001, 1002 etc?

Thank you

 

[kjv1611]

You could use a conditional statement, if you are running this in VBA, or in SQL. I am not 100% sure of using the conditional statements in strict SQL, but I know that it can be done.

If you want to use VBA to differentiate, then you could do something like this:

If DLOOKUP(...) Then 'if the 1000 esists
   'Run your current SQL
Else
   'Run a variation - instead of 1000, then 1001 or whatever you choose.

But, in order to make it really work, I might would try using a variable or two, and loop through until you find a nonexistent number for the new id. Of course, there may be a simpler way than this. If no other results, and you want to give that a try, I'll try to help with more detail if necessary - I am assuming this in VBA as well.

 

[hoofit]

kjv1611,
Yes, most everything is in VB. I'm not terribly familiar with VB and looping....more with .net. I could use a hand if you have the time. Let me lookmat the dlookup scheme.....

 

[Golom]

You (it appears) already have sequential numbers and you just want to renumber them by some constant increment. Would this work?

UPDATE TblRunningMeter 

SET MeterID = MeterID + 999

WHERE MeterID = 1
  AND UnitID=[forms]![frm_viewmeters]![unitID]

  AND NOT EXISTS (Select 1 From TblRunningMeter 
                  Where MeterID = MeterID + 999)

If a second run causes duplication then, before the first run, you must have had duplicates.

You could also look at

UPDATE TblRunningMeter 

SET MeterID = (Select MAX(MeterID) + 1 From TblRunningMeter)

WHERE MeterID = 1
  AND UnitID=[forms]![frm_viewmeters]![unitID]

 

[PHV]

UPDATE TblRunningMeter
SET MeterID = 1+DMax("MeterID","TblRunningMeter")
WHERE MeterID=1 AND UnitID=[forms]![frm_viewmeters]![unitID];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hoofit]

PHV,
What is the '1+dmax' line doing? As a test, I changed it to 1000+dmax ... and put 4 meters in the table numbered 1 thru 4. After running the query, I ended up with meters numbers 2,3,4 and 1004. I then put in another meter 1, showing 1,2,3,4 and 1004. Ran it again and showed 1,2,3,4,1004 and 2004. Is dmax counting the number of meters and adding 1000? The re-numbering appears to work, just not consecutively.

Thank you

 

[hoofit]

Hi Golom,
Thank you for the response. Your suggestion appears to change meter 1 to meter 1000 on the first run. The second run would result in a duplicate - here's why. I failed to mention something - don't they all say that. Let's say a user "deletes" meter 1...it gets re-numbered to 1000...effectively making it un-useable in the db but preserving it as a historical record. Later...la-la-la-la... the user realizes hey I really want a meter 1 and installs it. This could occur several times. Each time the user deletes a "new" meter 1, the code looks at it, the query tries to update but can't because there would be a duplicate record. I think PHV was sort of on track in that the re-numbering took place with no dupes, it's just that it was not sequential.

 

[PHV]

I didn't suggested to add 1000 but 1.

 

[PHV]

I also missed the 1000 issue :~/
SET MeterID = 1+Nz(DMax("MeterID","TblRunningMeter","MeterID>999"),999)
WHERE MeterID=1 AND UnitID=[forms]![frm_viewmeters]![unitID];

 

[hoofit]

PHV,
Had to run out, sorry about the delay in getting back. Yes this works fine now. Results were not good at first. I modified as you suggested and results are perfect.

Thank you!

 

[kjv1611]

PHV,

If you don't mind,

Can you give us a short step-by-step or overall explanation of how your SQL works?

If not, it's no big deal, but I thought it interesting.

 

[PHV]

how your SQL works

DMax("MeterID","TblRunningMeter","MeterID>999")
Find the highest used MeterID starting at 1000
Nz(...,999)
If none found, pretend it's 999
SET MeterID = 1+...
Add 1 and assign the result

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

Thanks. Makes sense.