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

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.