Two tables, One big problem please reply!

[drolfe]

So here is what i am trying to do:

Background:
I have two tables, (AuditDB) and (AccountDB)

AccountDB has an autonumber key preventing duplicates (AccountID)
AuditDB also has an autonumber key preventing duplicates (AuditID)

A relationship has been established between the two tables, linking a
field in (AccountDB.CollectorID) to (auditDB.auditID)

I have a form to modify these two tables.

Objective:
I need to make it so when add a new record to auditDB, it will also add
10 new records to AccountDB, marking (AccountDB.CollectorID) with the
new record created in auditDB (auditdb.auditid), also for those 10 new
records that I created in accountdb, I need to to take each AccountID
that was just created and put them into auditDB.AccountID1,
auditDB.AccountID2, etc.. up to 10

know what im trying to say? i can email somebody an image of the
layout of the tables if required. Please respond asap! thanks!

 

[ruralguy via AccessMonster.com]

Hopefully others will jump in here but I believe you are having difficulty
because you are trying to violate normalization rules. You may wish to
rethink the design. A properly implimented SubForm will automatically supply
the ForeignKey to the table to which it is bound as you add records but your
MainForm fields are problematic and a normalization issue.

 

[Guest]

Before I give you an answer, I need to know why you need to create 10
records? It is not normal to create records until you have data in them you
want to store. If you can describe the business rules for what you are
trying to accomplish, I can show you how to do it if it is necessary, or how
to accomplish your goal using a more conventional approach.

 

[drolfe]

Here are some images of what i have so far, maybe this will help

http://i134.photobucket.com/albums/q88/danrolfe227/dbimage1.png
http://i134.photobucket.com/albums/q88/danrolfe227/dbimage2.png
http://i134.photobucket.com/albums/q88/danrolfe227/dbimage3.jpg

The problem i am having now, is when I add a new record, it will add
the record to the auditdb table no problem, but I cant figgure out how
to add records to the accountdb table while also having the collectorid
cross reference

 

[Guest]

Hi,

Sounds like you need a link table between AuditDB and AccountDB, lets say
tblAudAccLnk which stores both ID Numbers & it's own ID number, that way you
maintain 2 one-to-may relationships, and aren't limited to 10 fields that may
later become 11.

TonyT..