Append Data

[neilmcmor]

I have a form with subform, containing data from 2 tables on a 1 to many relationship. I would like to append the data in the form and subform to 2 seperate tables. The destination tables each have primary keys, (autonumbers) along with the fields I wish to append from the original tables. Can this be done by writing an append query? If so how. I want to append the 2 sets of data and keep the relationship between both sets and this is what is causing problems beacuse the primary keys of the original tables will not be the primary keys of the destination tables. Hope I have explained it ok.

 

[neilmcmor]

more info

I will try to explain a bit better.

Table A contains:- TimetableID (PK), Day, Subject, Teacher, Location.
Table B contains:- StudentTimetableCode (PK Auotnumber), TimetableID, Student.

I would like to append them to:-
Table C:- AttendanceCode (PK Autonumber), Date, Day, Subject, Teacher, Location
Table D:- AttendedStudentTimetableCode (PK Autonumber), AttendanceCode, Student

 

[Libre]

Not sure I get it exactly, but I would write an INSERT SQL statement. You can append rows to any table with any data you wish, as long as the data types are compatible.

 

[neilmcmor]

But can you insert to 2 tables with within the same statement?

 

[Libre]

No - but why do you have to do it in a single statement?
Write as many statements as you need.
Create a record set of Table A. Store the values in variables and then do separate INSERT statements into Table C and Table D with the VALUE clauses using the variables you've defined.
Then do the same with Table B. If you want to act on the same rows (now in tables C and D) as you did with step 1, then use UPDATE statements on C and D instead of INSERT statements the second time around.

 

[neilmcmor]

I cannot see how this problem can be overcome. I have it set up to work where Tables A & B get combined into Table C. But with approx 250 records per week getting added I get approx 225 redundant records. I know this is not a good situation. How long would it be before I would notice performance difficulties as there would be a lot of analysis going on with this redundant data

 

[Libre]

Sorry, maybe I misspoke.
I don't really understand your particular situation, or your db structure.
I'm just saying it should not be a problem to write SQL statements to take records from one or several tables and append them to one or several other tables.

 

[neilmcmor]

would you like to see the structure. i could post it here i suppose