473,320 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Copy records in two related tables

1
I have two tables, tblWorkPlan and tblWorkPlanSteps. There is a one to many relationship between ID (PK) in the tblWorkPlan table and WorkOrderNumber in tblWorkPlanSteps table.

I have a from that has a subform. The main form is for tblWorkPlan and the subform is for tblWorkPlanSteps. I want to copy both the record in the tblWorkplan table and the related records in the tblWorkPlanSteps table and append them to the same respective tables.

I have tried using append queries and I can get it to work except the WorkOrderNumber field in tblWorkPlanSteps is not updated with ID field in tblWorkPlan.
Dec 14 '16 #1

✓ answered by zmbd

rcisco,
I know this is an old thread now and I hope you have things worked out by now; however, if you're still struggling with this issue I'd be happy to lend a hand.

If I understand correctly, what we have:
[tblWorkPlan]
[tblWorkPlan].[ID] PK


[tblWorkPlanSteps]
[tblWorkPlanSteps].[ID] PK
[tblWorkPlanSteps].[WorkOrderNumber] FK to [tblWorkPlan]


main form is for [tblWorkPlan] === parent
subform is for [tblWorkPlanSteps] === child

I want to copy both the record in the [tblWorkplan] table and the related records in the [tblWorkPlanSteps] table

and append them to the same respective tables.

Ok, this really makes very little sense to copy the same information between the two tables.

Sounds as if your database either needs to have another table or there may be some normalization issues to address: home > topics > microsoft access / vba > insights > database normalization and table structures

On paper, what I am suspecting is that you have something like:
Work Plan 123456
Step A
Step B
Step C

Work Plan 7890123
Step A
Step D
Step E

etc... and you're needing a way to track these different work plans.

Is that correct, if so, then you need another table that has a many:many relationship between your current tables.

1 874
zmbd
5,501 Expert Mod 4TB
rcisco,
I know this is an old thread now and I hope you have things worked out by now; however, if you're still struggling with this issue I'd be happy to lend a hand.

If I understand correctly, what we have:
[tblWorkPlan]
[tblWorkPlan].[ID] PK


[tblWorkPlanSteps]
[tblWorkPlanSteps].[ID] PK
[tblWorkPlanSteps].[WorkOrderNumber] FK to [tblWorkPlan]


main form is for [tblWorkPlan] === parent
subform is for [tblWorkPlanSteps] === child

I want to copy both the record in the [tblWorkplan] table and the related records in the [tblWorkPlanSteps] table

and append them to the same respective tables.

Ok, this really makes very little sense to copy the same information between the two tables.

Sounds as if your database either needs to have another table or there may be some normalization issues to address: home > topics > microsoft access / vba > insights > database normalization and table structures

On paper, what I am suspecting is that you have something like:
Work Plan 123456
Step A
Step B
Step C

Work Plan 7890123
Step A
Step D
Step E

etc... and you're needing a way to track these different work plans.

Is that correct, if so, then you need another table that has a many:many relationship between your current tables.
Jan 22 '17 #2

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

Similar topics

0
by: William Gill | last post by:
Is there a simple front-end that would let me select records from multiple related tables and edit their field data? Right now, I am opening each table individually in mysql control center,...
3
by: William Gill | last post by:
I can't help but think I'm re-inventing the wheel if I have to code my own interface! Isn't there some script, php code, or something (modifiable / customizable) available that lets me select...
0
by: Hanuman | last post by:
Hi, On the IBM host we use a product called RDX that lets us copy "related" table data from the QA and prod environments to our UT environments. For example if we provide a CustomerID as input...
3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
3
by: Leinad Kong | last post by:
I'm using Access 2002, as front-end and back-end as well: 1) I faced database corrupted problems, when more than 1 user edit concurrently. I'm using All-records Locking, and open-exclusively as...
1
by: Christa Waggett | last post by:
Hi, I'm not a programmer but would appreciate some help with the following. I've been looking at various sites but cannot find the information I require. I have a table of strata plans and if we...
4
by: HLCruz via AccessMonster.com | last post by:
I am working with a database that has client information separated in to 4 related tables - tFolder, tAddress, tEmail, tPhone number. In addition there are related tables tGifts and tCalls. The...
5
by: upwardgazing | last post by:
I'm using Access 2003 (Access 2000 file format) and I have two tables related one-to-many called tblTempEncounter and tblTempEncounterDetails. I need to move a record from the first table with it's...
3
by: simple simon | last post by:
How would I insert into multiple related tables using a table valued parameter? Is there any way to do this without using a WHILE loop? I know how to insert from a table valued parameter into one...
4
stonward
by: stonward | last post by:
Hello again everyone, I have a quotations form (main and subform) based upon two related tables. I want to be able to convert the quote to a sale and thus transfer the records to my Order and...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.