automatically populating tables (1 Viewer)

[debbiebarton]

how do I populate a table with records from another table but with the primary key from a further table, eg, I have a student table, a keyskills table (with numerous rows) and a KS achieved table which will hold the studentID and the records from the keyskills tables. Because of the volume of the students and the keyskills I do not want to have to key them in manually, is there a way to do it automatically? Initially I want to do it for all students in database, then only for new students. Any help would be very much appreciated

 

[CrazyJay]

Your probably best using vb to do this. What you need to is create a recordset of the table which you want to transfer which is probably the students one and another recordset of the info your adding to it if needed.

Use a for... next loop to step thru the recordset and use either a SQL statement to insert whatever details you need or create another recordset and write the info to it.

 

[debbiebarton]

Many thanks for your reply. Can you recommend any good books for this sort of thing. I have never written code within Access before and the majority of books I have seen do not cover this.

 

[Rich]

Why can't you use an Append query?

 

[CrazyJay]

Microsoft sell step by step books which do not go into much details but are good to use starting off. Some of the better ones are books by Macmillans. There is a really in depth one called Visual Basic Super Bible which covers lots and lots but it is huge. They all tend to be a bit expensive. There are some really good sites as well:
http://www.geocities.com/~chuckeasttom/vb/Vb.htm http://www.visualbasicforum.com/ http://www.vbinformation.com/

 

[Fornatian]

Use an append query if you can sa Rich suggested, it much less hassle than doing it in code.

 

[Rich]

And 1,000? times faster.

 

[BukHix]

While I agree with Rich and Fornatian about the benefit of an append query, if you were in a situation where you have to do it in code. This thread covers the topic and should get you started.

Topic: Using RecordSet to update table?


____________________________________________________________

The ever increasingly inaccurately named UBB Board List for PC Support.
____________________________________________________________