Access database Simple solution I hope??? 1

[djbrian]

I have a two database files which contain 5 identical fields. The first database has 4232 records. The second has over 17000 records. I need to merge the two, but first I need to increase the value of field5 by 423200 in every record of the larger database. I'll explain... The program that created these databases assigned a numeric value to field5 based on the position of the record in the original database. Both databases contain information that is not duplicated in either database, but must be combined into one database, with consecutive field5 values. field5 is a long integer numeric field. The value of field5 is not consecutive, but is more like 101, 102,103,105,201,202,203,204,205,206,207,301,302,303,304,305,306,307,308,401,402,403,404,501,505,508,509,510, etc... In each case, the last two (rightmost) digits must remain the same as the original number, (202 must be assigned a value of 452502, 505 will become 423805, etc...)

I don't have Access at home, but will be using the computer at work to do this on my files, and I don't even use it at work, but it's there and I honestly don't have the time to learn it just for this project. I can pay for the solution if needed. Any help is appreciated, even links to a site with clues to what I need to do.

THANK YOU in advance!

Brian

 

[MintJulep]

Create a new field in the big BD, and cause it to be 423200 + the value of field5.

When yo merge, use the newly created field, and just ignore field5.

Assuming that filed5 is not a primary key.

 

[djbrian]

OK, now, how do I do that? Like I said, I have NO knowledge of acess programming. I have been playing with it a bit, and I am confused by macros and functions and all that, I tried to create the new field and setting the default value to = [field5] + 430000 but it gives me an error, (can't remember what it said, but something about an invalid expression) and it creates the new column but all the values are null.

 

[johnwm]

To increase the value of a field use an Update Query. Go into Queries, Create query in Design view. Add the table that you want to change the value in. Then change the View to SQL view (ALT V Q) and change whatever you see into:

UPDATE yourtablename SET yourtablename.field5 = [field5]+423200;

where yourtablename is the actual table name. Then merge the 2 tables using an Append query

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk