Insert records update key

  • Thread starter Thread starter Robert H
  • Start date Start date
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");
Click to expand...

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
 
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");
 
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.
 
Post examples of before and after data so that we can see what you're
wanting to achieve for this latest option.
 
Post examples of before and after data so that we can see what you're
wanting to achieve for this latest option.
Click to expand...

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
 
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";
 
Sorry, left out a comma:

UPDATE CDD
SET CDD.DeviceConfig =
Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceConfig>"B";
 
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
 
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?
 
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?
Click to expand...

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?
 
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.
 
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
 
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 >
 
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.
Click to expand...

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?
 
Back
Top