468,768 Members | 1,640 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,768 developers. It's quick & easy.

Transferring data from form to table

3
I am learning Access 2007 on my own, any help would be greatly appreciated. I have a form which I have associated with a table, TB1. There are 3 values from this table which I would like to transfer to a second table, TB2. How do I go about transferring data from TB1 to TB2 when the Add button is selected?

Thank you.
Aug 19 '11 #1
6 2090
patjones
931 Expert 512MB
Hi,

A little more context would be helpful...in particular, why are you trying to move the data from TB1 to TB2 in the first place? We often ask questions like this in response to questions in order to better focus the answer.

Also, is the form bound to TB1, meaning that any record you enter through the form - or changes that you make to existing records - are immediately reflected in the table?

Pat
Aug 19 '11 #2
P Ryan
3
TB1 contains information regarding the company fleet such as year, make, model, insurance info, driver, information for inspection, it also has information regarding the purchase of the vehicle, cost, date of purchase and mileage. All of the information on the form is recorded in TB1. I also have a second table with the odometer readings for the car which the drivers will update every few weeks or so. What I am trying to accomplish is taking the purchase date and purchase mileage and moving them to the table which will keep track of the odometer readings as this is the initial setting for the vehicle.
Aug 19 '11 #3
patjones
931 Expert 512MB
It sounds like you want to keep a history of odometer readings versus date. Do you have something that uniquely identifies each vehicle (e.g. VIN, fleet number, etc.), and which you can use to link the two tables together?

For populating purchase date and mileage for the records that you already have...this can be accomplished a couple of ways. One is to use Query Design view in Access to build an insert query that will perform the record insertions into TB2. This is a pretty straightforward 'point and click' method to quickly accomplish what you want. But a unique identifier (primary key) is helpful...really almost necessary, to accomplish this. A more advanced way to do this is to write a SQL INSERT query. This is basically the same thing - and in fact when you use the design grid to build your query that's what Access is doing, and you can even switch to SQL view to see the SQL that Access constructed for you.

Going forward, I would eliminate the initial odometer reading column from TB1 and keep that information just in TB2, as this will eliminate duplication of the data. When you initially enter a vehicle in the database, you would want to populate both TB1 and TB2 with the necessary information, so that involves attaching TB2 to the form as well.

I highly suggest taking a look at the article in our Insights section on how to properly structure database tables: Database Normalization and Table Structures. It has lots of useful information about creating primary keys and minimizing data duplication.

I know this is all pretty general, but I think it's the right direction for you to go in.

Pat
Aug 19 '11 #4
P Ryan
3
Pat, thank you for the help. I do have a primary key for the tables which is a vehicle id number. I realize that there will be duplication of information, but there should not be more than 30 or 40 vehicles to track at any one time. And since I am very inexperienced with Access I thought it would be simplier to copy the one duplicate record to the second table to elimate trying to figure out how to determine the purchase date and mileage when needed. I think I will try attaching both tables to the form and see how that works.

Peggy
Aug 19 '11 #5
patjones
931 Expert 512MB
Hi Peggy,

Sounds good, but let us know if you have problems attaching both tables to the form. This would mean joining the tables together via the VIN if they aren't already joined.

Pat
Aug 19 '11 #6
NeoPa
32,131 Expert Mod 16PB
P Ryan:
I thought it would be simplier to copy the one duplicate record to the second table to elimate trying to figure out how to determine the purchase date and mileage when needed.
That's a very common misconception held by inexperienced database designers Peggy. The reason that experienced developers advise against it (as Pat was doing when he directed you to that article) is because we know from experience that it will only lead you deeper and deeper into the mire as you try to use and develop your database. Very similar to the biblical reference to building houses on sand if you're familiar with that.

Obviously, your choices are your own and it's not our responsibility to push you one way or another, but we don't feel it would be fair to you to avoid expressing reservations about your current approach.

That said, I'm fully confident that Pat can lead you forward in this, whichever course you choose ultimately.
Aug 19 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by AZ Jack | last post: by
15 posts views Thread by http://www.visual-basic-data-mining.net/forum | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.