Multiple autonumber fields

[maggie]

I have a table with 4 sets of grandparents (grandparent1
info, grandparent2 info, etc.). I really want 4
autonumber fields but I know Access won't allow that.
How can I get grandparents1 to have an "autonumber"
field, and grandparents2 to have an "autonumber" field?
The reason I need this is because I need to keep track of
what money grandparents 1 donate each year, and what
money grandparents2 donate each year. I am using a union
query for the main form so that grandparents1 is a record
and grandparents2 is a different record. I also have a
subform with the yearly money donation information. I
have tried to create just 4 number fields beginning with
a certain number for each set of grandparents, but I
don't want the users to have to input a number. I want
it to be automatic. And I don't want 4 separate tables
either. How can I get each set of grandparents to have
their own number so that I can link the main form and the
subform? Thanks!

 

[Rebecca Riordan]

What you need is a Grandparents table with a GrandparentID field that you
set to an autonumber. Then you reference that ID as GP1, GP2, etc. But
depending on what you're doing, you may (probably will) run into
trouble...lots of people have step-parents, and thus more than four
grandparents.

Aside from donations, what exactly are you modelling? There's probably a
better way than using a repetitive field.

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Maggie]

Hi Rebecca, and thank you for your reply. Having just
one autofield won't work. When I do the union query on
the GP table and create a line item for GP1 and a line
item for GP2, they are still then using the same
autonumber. Which means that the master and child fields
on the subform will also be using the same autonumber,
which won't then differentiate between which GP donated
$1,000 and which GP donated $50. We don't need to worry
about more than 4 sets because after many discussions,
that is what was decided on.

If there is a better way to do this, I would love to hear
about it. I just need to know which GP donated how much
and when. I hope this makes sense.

 

[John Vinson]

I have a table with 4 sets of grandparents (grandparent1
info, grandparent2 info, etc.). I really want 4
autonumber fields but I know Access won't allow that.
How can I get grandparents1 to have an "autonumber"
field, and grandparents2 to have an "autonumber" field?

Well... DON'T.

One person might have anywhere from zero to a dozen grandparents -
they might have all died, or a child might have more than one set of
step-grandparents.

Each potential donor should have their own record in a People or
Donors table. If a child has (mother's mother), (father's mother),
(father's stepfather), (father's stepmother), (stepfather's father),
(stepfather's mother) there would be six records in this table, each
with a link to the child's ID. The Donation table would have a link to
the primary key of this Donors table (which might well be an
autonumber if you wish).

Remember - "fields are expensive, records are cheap"!

 

[Rebecca Riordan]

Maggie,

As I said, you need a record, not a field, for each grandparent, and a
standard natural join, not a union query.


--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...