Loop to Create New Records

[joesmithf1]

Hello Everyone,

I know the 'basic' stuff(like creating tables, simple forms, reports, etc.) but I am not too good with the advance stuff.

Here is my table structure and data setups, and then I will list my issue.

I have a table with two columns, "GradeCodes" and "SalaryAmount."

GradeCodes SalaryAmount
A-11 $10
A-12 $20
A-40 $60
B-01 $5
B-02 $15

Is there a script that can do something like:

Create a new record for all the grades starting with "A," AND add an "O" after it, AND then apply 5% to its salayAmount. Hence the result should look something like this: GradeCodes =AO-11 SalaryAmount= $10.50. I want to do the same with "B," so B could look something like this: If "B," then add "M" after B and apply 20% to its SalaryAmount.

Please advise on the codes.

Thank you!

 

[WayneRyan]

Joe,

I don't really think that createing new records is something that you
really want to do. In general, if you can "calculate" something
than you don't need (or want) to store it. If you change the original
salaries for the A-11, A-12 and A-40 codes then what happens to the
"AO" records that we made?

The following does it. Leave out the 1st line to just list them and
not create records.

Insert Into YourTable (GradeCodes, SalaryAmount)
Select "AO" & Mid(GradeCodes, 2),  <-- Return the A's @ 5%
       SalaryAmount * 1.05
From   YourTable
Where  Left([GradeCodes], 1) = "A" UNION
Select "BM" & Mid(GradeCodes, 2),  <-- Return the B's @ 20%
       SalaryAmount * 1.2
From   YourTable
Where  Left([GradeCodes], 1) = "B"

hth,
Wayne

 

[joesmithf1]

Hi Wayne,
Thank you VERY much! To clarify your question. What I am doing is, I currently have all the A,B,C,D...Z gradecodes, and I need to add more grades codes(with different % of salaryAmount applied to each NEW codes). But instead of manually go into to my database to add(potentially need thousands and thousands of new gradeCodes), say, AO,A1, A2....ZO,Z1,Z2..., and apply the new %, I need a script that does this for me.

Is there a "For" loop or "Do While" loop that says:

Add 0 through 10 to anything that start with A throug Z, and then apply 10% if there is a "1" after the alphabet, apply 20% if there is a "2", etc....
The result should look something like this:
A0 - apply 10%
A1 - apply 20%
...
..
A10 apply -100%
B0 - apply 10%
B1 - apply 20%
...
..
B10 -apply 100%
.......
.....
...
..
Z1 - apply 10%



Any advice?
Thank you!
Joe

 

[Fernando]

Whats the idea? so when the user picks lets say from a combo box A0 it will add 10% to the salary?.
Or your gradecodes will have value depending a single amount ( x )
gradecode amount
A0 = x + 0.1x
A1 = x + 0.2x
....
B0 = x + 0.1x
..

 

[joesmithf1]

no, the users do NOT pick any combo box. I simply wants to add records to my table, no users interaction whatsoever. The idea is, my company just added about 50,000 new GradeCodes and each separate gradeCodes(according to its alphabet) has a different % applied to the SalaryAmount. So instead of having to manually input all 50,000 new records, I am hoping there is a script I can use that will automatically add new record and apply the % accordingly. Thank you! Joe

 

[MistyEE]

If this is a one time thing---then I wouldn't spend a lot of time programming something for this.

Okay..I know this is an Access forum, but I have something that will help you build an excel data file for importing into Access.

It is an excel file with vba....the great thing is that you can put in ANY rules you want. To do the whole alphabet 10 times should take you all of 10 minutes...then maybe 30 seconds to import and append.

Let me know if you want it!

 

[joesmithf1]

Hi MistyEE,

Please do give me the Excel vba method! Anything is better than having to repeat everything manually. Thank you!

Joe

 

[MistyEE]

You will see that it is not really all that complex or pretty. just a quick way to apply your rules to the different series, to create a data file to import into an Access table.

If you think you are gonna do this more than once, then I would definitely make something a little more robust.

Anyway, there are 2 tabs. One is the worksheet...where you will type what you need to. the other tab will be the actual tab you want to import into the Access database.

Make sure macros are enabled...and if it doesn't quite do what you need, or if it is confusing, then let me know and we can try to tweak it.

Actually...I need your email address...I can't post the file here.

 

[joesmithf1]

I'll be doing this maybe once every 5 years, so your method will do for now since it is a last minute thing. My email is joesmithf1@yahoo.com

Thanks again!

Joe