Insert records update key

[Robert H]

OK - I think I see the "final" fix:

UPDATE CDD
SET CDD.DeviceConfig =
IIf(CDD.DeviceCode > "Module 01",Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceConfig),
CDD.DeviceID = CDD.DeviceID + 1
WHERE (CDD.DeviceCode="Module 01" And CDD.DeviceConfig>"B")
OR (CDD.DeviceCode > "Module 01");

New results

ID Code Config Type
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 C +11
5 Module 01 D -11
6 Module 02 B LOAD
7 Module 02 C LOAD
8 Module 02 D +11
9 Module 02 E -11
10 Module 03 B LOAD
11 Module 03 C LOAD
12 Module 03 D +11
13 Module 03 E -11

bumps config A after Module 01
bumps ID for C in Module 01 only

 

[Ken Snell \(MVP\)]

Sorry -- I was tired when I posted that; got the arguments reversed for the
IIf function. Try this:

UPDATE CDD
SET CDD.DeviceConfig =
IIf(CDD.DeviceCode = "Module 01",Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceConfig),
CDD.DeviceID = CDD.DeviceID + 1
WHERE (CDD.DeviceCode="Module 01" And CDD.DeviceConfig>"B")
OR (CDD.DeviceCode > "Module 01");

 

[Robert H]

That works Ken!

Results

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
6 Module 02 A LOAD
7 Module 02 B LOAD
8 Module 02 C +11
9 Module 02 D -11
10 Module 03 A LOAD
11 Module 03 B LOAD
12 Module 03 C +11
13 Module 03 D -11


It works for when I only need to bump the Config in Module 01. I
tried to manipulate the sql to bump C in all modules with disastrous
results.

 

[Ken Snell \(MVP\)]

Post examples of before and after data so that we can see what you're
wanting to achieve for this latest option.

 

[Robert H]

Post examples of before and after data so that we can see what you're
wanting to achieve for this latest option.

Thanks Ken,

Before:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
3 Module 01 C +11
4 Module 01 D -11
5 Module 02 A LOAD
6 Module 02 B LOAD
7 Module 02 C +11
8 Module 02 D -11
9 Module 03 A LOAD
10 Module 03 B LOAD
11 Module 03 C +11
12 Module 03 D -11

After:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
6 Module 02 A LOAD
7 Module 02 B LOAD
9 Module 02 D +11
10 Module 02 E -11
11 Module 03 A LOAD
12 Module 03 B LOAD
14 Module 03 D +11
15 Module 03 E -11

Basicly, bump config C and the corresponding ID

 

[Robert H]

leaving a slot, if you will, in the ID sequence for the new Config Cs

 

[Ken Snell \(MVP\)]

The query to do this would be this:

UPDATE CDD
SET CDD.DeviceConfig =
Chr(Asc(CDD.DeviceConfig)+1)
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceConfig>"B";

 

[Ken Snell \(MVP\)]

Sorry, left out a comma:

UPDATE CDD
SET CDD.DeviceConfig =
Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceConfig>"B";

 

[Robert H]

Results

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
5 Module 02 A LOAD
6 Module 02 B LOAD
8 Module 02 D +11
9 Module 02 E -11
9 Module 03 A LOAD
10 Module 03 B LOAD
12 Module 03 D +11
13 Module 03 E -11

Now, the Config and the ID bump correctly. However, we have the double
ID problem again (ID 5 and 9) all IDs after the first instance need to
bump sothey stay unique. It seem like we are caught in a loop . Am
I asking for something that cant be done?

Thanks for your patience, time and effort.
Robert

 

[Ken Snell \(MVP\)]

In order to do what you seek, it'll be necessary to make a few assumptions
from your data and use the data values directly. For example, if you'd
always have "Module 01", "Module 02", "Module 03", etc., and you'd always be
bumping all the modules, then we could use the "01", "02", "03", etc. part
of the module value to decide how much of a gap we need to create for the
Module values -- e.g., gap of 1 for Module 01 DeviceID values, gap of 2 for
Module 02 DeviceID values, etc.

Can you live with such a data restriction in order to make this query
"simple"? Or would you possibly have other scenarios where this "use of the
data values themselves" won't work?

 

[Robert H]

Can you live with such a data restriction in order to make this query

"simple"? Or would you possibly have other scenarios where this "use of the
data values themselves" won't work?

In some cases the Device Codes will have no numeric value in the name
at all. There will be times that we need to select a single device
code. To Simplify could we have one query that is for simgle device
code applications and one for all device codes?

 

[Ken Snell \(MVP\)]

The problem with trying to use just queries for these scenarios is that it's
nearly impossible to inform the query of how many previous levels have been
bumped when it's looking at another level. When you have "01", "02", etc. in
the name, you can extract that number and use it to know how many levels
were already bumped. Without such an embedded identification, a query just
isn't going to know what to do.

Therefore, you're now looking at the need to write some type of VBA
procedure that would figure out and tell the query how many bumps to make to
the DeviceID value for each level that is being "bumped" by the query.

Sorry, but it just continues to get more complicated as you expand the
possible scenarios and make the data more generic.

 

[Robert H]

I figured as much, thats why I posted it on the Macro forum...
However, seeing that no one has offered any suggestions along those
lines I guessing its a daunting task... You have spent much more time
helping me sort this out than I would have expected.
thank you very much
Robert

 

[Ken Snell \(MVP\)]

In ACCESS, macros are not VBA, so no one, including me, has suggested a VBA
approach so far because you did post into the macro newsgroup.

Doing what you want in VBA is not impossible, but would require careful
analysis of all the possible scenarios that you want to handle so that the
code could be written to handle them all. Probably would spend much more
time on the analysis than on the programming.... < g >

 

[Robert H]

In ACCESS, macros are not VBA, so no one, including me, has suggested a VBA
approach so far because you did post into the macro newsgroup.

Ahhh, I was missled becuase the visual basic editor is available under
Macros... Looking at the macro function it looks like it might quicker
than writing VBA code. Would you agree? And, is there a rule of thumb
on how to draw the line on when to use one over the other?