Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access database Simple solution I hope???

Access database Simple solution I hope???

Access database Simple solution I hope???

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!


RE: Access database Simple solution I hope???

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.

RE: Access database Simple solution I hope???

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.   

RE: Access database Simple solution I hope???

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
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close