Ken,
I think we are almost there (I hope). I thank you so much
for your help so far and your continued help.
I still am not completely clear about how to maintain the
correct TransHeaderID for tblTransDetail. Let me
illistrate.
Before "Copy" procedure (simplified):
tblTransHeader
TransHeaderID Year Description
1 2003 XYZ Office Complex
2 2003 SBC Communication - Phone
tblTransDetail
TransDetailID TransHeaderID Account Amount
1 1 Checking -25000
2 1 Rent 20000
3 1 Occupancy 5000
4 2 Payable -2000
5 2 Telephone 1250
6 2 Internet 750
After "Copy" procedure (simplified):
tblTransHeader
TransHeaderID Year Description
1 2003 XYZ Office Complex
2 2003 SBC Communication - Phone
3 2004 XYZ Office Complex
4 2004 SBC Communication - Phone
tblTransDetail
TransDetailID TransHeaderID Account Amount
1 1 Checking -25000
2 1 Rent 20000
3 1 Occupancy 5000
4 2 Payable -2000
5 2 Telephone 1250
6 2 Internet 750
7 3 Checking -25000
8 3 Rent 20000
9 3 Occupancy 5000
10 4 Payable -2000
11 4 Telephone 1250
12 4 Internet 750
The idea is to:
1) Be able to run reports on the budget for more than 1
year
2) Have different amounts for same transaction detail or
completely differnece transactions for any given year
3) Make setting up a new year much eaiser and accurate
than having to re-enter a new years worth of data.
I get how to do the first append query for tblTransHeader,
and mostly know how do the second append query except for
maintaining the correct TransHeaderID.
1st query:
INSERT INTO tblTransHeader ( Description, Type, Year )
SELECT tblTransHeader.Description, tblTransHeader.Type,
[Forms]![frmCopyYearSelect]![cboYearSelectTo] AS NewYear
FROM tblTransHeader
WHERE (((tblTransHeader.Year)=[Forms]! [frmCopyYearSelect]!
[cboYearSelectFrom]));
2nd query:
*** This would use the "old" TransHeaderID, how do I have
it use the "new" TransHeaderID?***
INSERT INTO tblTransDetail ( TransHeaderID, Comment,
Account, JanAmt, FebAmt, MarAmt, AprAmt, JunAmt, JulAmt,
AugAmt, SepAmt, OctAmt, NovAmt, DecAmt )
SELECT tblTransDetail.TransHeaderID,
tblTransDetail.Comment, tblTransDetail.Account,
tblTransDetail.JanAmt, tblTransDetail.FebAmt,
tblTransDetail.MarAmt, tblTransDetail.AprAmt,
tblTransDetail.JunAmt, tblTransDetail.JulAmt,
tblTransDetail.AugAmt, tblTransDetail.SepAmt,
tblTransDetail.OctAmt, tblTransDetail.NovAmt,
tblTransDetail.DecAmt
FROM tblTransDetail INNER JOIN tblTransHeader ON
tblTransDetail.TransHeaderID = tblTransHeader.TransHeaderID
WHERE (((tblTransHeader.Year)=[Forms]! [frmCopyYearSelect]!
[cboYearSelectFrom]));
Jim
-----Original Message-----
Assuming that you wish to continue using the same value
for TransHeaderID as
was in the original tables, and assuming that the
TransHeaderID field in the
table to which you're appending the data has either no
records or has no
records with the same values of TransHeaderID, then your
append query can be
run such that you put the current value of TransHeaderID
into both tables.
An append query can be used to put your own values into
an Autonumber field.
--
Ken Snell
<MS ACCESS MP>
message
Ken,
tblTransHeader.TransHeaderID is autonumber.
Thanks,
-----Original Message-----
tblTransHeader is the parent table, so it should be
done
first. That way the
child table will find the foreign key TransHeaderID in
the parent table.
With respect to the TransHeaderID value in the child
table, how you set that
depends upon how you'l set the value of TransHeaderID
in
the parent table.
Would your first append query be using a specific value
for each record's
TransHeaderID value? Or is TransHeaderID an autonumber
field in the
tblTransHeader table?
--
Ken Snell
<MS ACCESS MVP>
message
Ken,
A couple more questions, please:
- Should I do tblTransHeader or tblTransDetail first
- How do I make sure that the TransHeaderID in
tblTransDetail matches the correct
tblTransHeader.TransHeaderID . . . I know that doing
one
append after the other will automatically change each
tables new records ID's, but how can I make sure
tblTransDetail contains the correct TransHeaderId.
Unless
I can somehow change the TransHeaderID in
tblTransDetail
the new detail will point to the old header?
Thanks!
-----Original Message-----
Just run two consecutive append queries. The first
one
for the first table,
the second one for the second table.
--
Ken Snell
<MS ACCESS MVP>
message
Ken,
Thanks for the help, but I still am having
problems,
please keep reading.
When I use an append query, I can only select one
table
to
append to, not two. I would like to copy a year's
worth
of
TransHeader and TransDetail information to a new
year,
the
only thing changing is the year in TransHeader.
I could be wrong, but I believe the tables are
relational / normalize. TransHeader contains the
information related to all it's details . . . i.e.
the
header may contain a salesperson information or
the
information for an office lease; the details
contains
that
salesperson's salary, bonus, commissions,
benefits,
etc
or
that office lease's monthly rent, occupancy costs,
utility
expenses.
Maybe this might help
tblTransHeader
TransHeaderID (primary key, autonumber)
Year
Description
Type (person, lease, monthly recurring vendor,
etc.)
...
tblTransDetail
TransDetailID (primary key, autonumber)
TransHeaderId (foreign key)
Comment
Account
JanAmt . . . DecAmt
I have a query to select all the right
records . . .
SELECT tblTransHeader.*, tblTransDetail.*
FROM tblTransDetail INNER JOIN tblTransHeader ON
tblTransDetail.TransHeaderID =
tblTransHeader.TransHeaderID
WHERE (((tblTransHeader.Year)=[Forms]!
[frmCopyYearSelect]!
[cboYearSelectFrom]));
I then would like to change the query from select
to
append (in grid design, not sure about SQL), but
can
only
select one table. The year to copy to would be
from
[Forms]![frmCopyYearSelect]![cboYearSelectTo]
Thanks
Jim
-----Original Message-----
Well...the answer to your actual question is to
use
an
append query to copy
the data over. Append queries can write your own
numbers
into the autonumber
fields.
But, let me point out that your setup does not
appear
to
be a relational
database design. It would be a more logical
design
if
all
the data were in
the same table, using a field that has the year
value
in
it so that you can
have separate records by year.
--
Ken Snell
<MS ACCESS MVP>
"Jim" <
[email protected]>
wrote in
message
[email protected]...
I am using Access 2000
I have two tables: tblTransHeader and
tblTransDetail
tblTransHeader has TransHeaderID (autonumber)
for
its
primary key
tblTransDetail has TransDetailID (autonumber)
for
its
primary key and a foreign key TransHeaderID to
tblTransHeader.
Each record in tblTransHeader has a field for
year.
I can create a query to select all records in
tblTransHeader and related records in
tblTransDetail
for a
given year.
What I want to do, and am having the problem
with, is
take
the data selected and append to tblTransHeader
and
tblTransDetail using a different year. All the
data
is
to
remain the same in each table except for the
year
(and
the
autonumbers and linked foreing key).
The database is used in our budgetting process
and
typically contains 3 years of data (current,
prior,
next . . . 2002, 2003, 2004). When we start to
do
next
year's budget, we currently type in manually
the
new
year's transactions information and details.
Normally
very
little changes, except for the amounts and
maybe a
couple
new transactions or losing a few transactions.
Think
the
process would be much faster if we could copy a
given
year's data to a new year.
Thanks in advance for your help.
Jim
.
.
.
.
