If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy Data
I have a database that tracks transactions for internal
comany use (intrabank transfers, wire transfers, etc.). Many of these entries are similar period to period (changing only in amount and maybe description). I would like to take a given period's transactions and detail and copy to a new period. This will allow me to minimize the overall amount of work in entering in a new period's transactions. I know how to do this with two related tables (as below, tblPeriod/tblTransHeader or tblTransHeader/tblTransDetail) but how do you do it with three related tables. The procedure will create a new period record and then copy the prior periods transactions headers and related details. I have prety much everything worked out, except for the query(SQL) to append the old records to their tables using the new related values. The table structures is as follows (not all tables, just the ones that apply to my issue). tblPeriod PeriodID ... tblTransHeader TransHdrID PeriodID TransDescription ... tblTransDetail TransDetID TransHdrID AcctNumID ... A sample of the data BEFORE copy (assumming only one period), simplified. tblPeriod 200404 . . . tblTransHeader 1 200404 Intercompany . . . 2 200404 MM Transfer . . . tblTransDetail 1 1 12345 . . . 2 1 22345 . . . 3 2 10010 . . . 4 2 10025 . . . 5 2 10026 . . . A sample of the data AFTER copy (assumming only one period), simplified. tblPeriod 200404 . . . 200405 . . . tblTransHeader 1 200404 Intercompany . . . 2 200404 MM Transfer . . . 3 200405 Intercompany . . . 4 200405 MM Transfer . . . tblTransDetail 1 1 12345 . . . 2 1 22345 . . . 3 2 10010 . . . 4 2 10025 . . . 5 2 10026 . . . 6 3 12345 . . . 7 3 22345 . . . 8 4 10010 . . . 9 4 10025 . . . 10 4 10026 . . . Thank you so much in advance, John |
#2
|
|||
|
|||
Copy Data
Hi John,
I'd do this with at least two append queries. The first would append the new Period to tblPeriod. The second would duplicate the TransHeaders for the previous period, substituting the new Period value for the old one. The SQL would look something like this: INSERT INTO tblXX ( Period, X, Y ) SELECT 25 AS NewPeriod, X, Y FROM tblXX WHERE (((Period)=24)); The third does the same for tblTransDetail. On Thu, 13 May 2004 14:07:48 -0700, "John" wrote: I have a database that tracks transactions for internal comany use (intrabank transfers, wire transfers, etc.). Many of these entries are similar period to period (changing only in amount and maybe description). I would like to take a given period's transactions and detail and copy to a new period. This will allow me to minimize the overall amount of work in entering in a new period's transactions. I know how to do this with two related tables (as below, tblPeriod/tblTransHeader or tblTransHeader/tblTransDetail) but how do you do it with three related tables. The procedure will create a new period record and then copy the prior periods transactions headers and related details. I have prety much everything worked out, except for the query(SQL) to append the old records to their tables using the new related values. The table structures is as follows (not all tables, just the ones that apply to my issue). tblPeriod PeriodID ... tblTransHeader TransHdrID PeriodID TransDescription ... tblTransDetail TransDetID TransHdrID AcctNumID ... A sample of the data BEFORE copy (assumming only one period), simplified. tblPeriod 200404 . . . tblTransHeader 1 200404 Intercompany . . . 2 200404 MM Transfer . . . tblTransDetail 1 1 12345 . . . 2 1 22345 . . . 3 2 10010 . . . 4 2 10025 . . . 5 2 10026 . . . A sample of the data AFTER copy (assumming only one period), simplified. tblPeriod 200404 . . . 200405 . . . tblTransHeader 1 200404 Intercompany . . . 2 200404 MM Transfer . . . 3 200405 Intercompany . . . 4 200405 MM Transfer . . . tblTransDetail 1 1 12345 . . . 2 1 22345 . . . 3 2 10010 . . . 4 2 10025 . . . 5 2 10026 . . . 6 3 12345 . . . 7 3 22345 . . . 8 4 10010 . . . 9 4 10025 . . . 10 4 10026 . . . Thank you so much in advance, John -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|