Database Design

[bowks14]

Hi everyone,

I need to design a database to hold all deposits made to our company. These deposits come in "batches" ranging from batch 001-999. So for instance, batch 001 may have 10 deposits, then it will be closed. Batch 002 may have 7 deposits, then it will be closed. The batches may need to be edited at a later date.

The way I have it set up now is with a batch table that has fields: batch#, open date, close date. Then I have another main table that has: autonumber, and a bunch of other minor fields that pertain to each deposit (payment method, check #, account, etc.).

I have a one to many relationship between the two tables linking the "many" deposits (autonumber) to each "one" batch. Everything seems to work fine, but when I made a form with a subform on it to edit each batch and its respective deposits, I get an error saying it will violate a primary key. I have tried several different realtionships to try an avoid this, but it persists.

I am not very familiar with database design, so if anyone who can give me a hand, I would much appreciate it.

Thank you.

 

[neileg]

Have you got the link the right way round? you need to hold the primary key from the batches table as a foreign key in the deposits table. You will have many occurances of the batches PK in the deposits table.

tblBatches
BatchID PK autonumber (don't use # or any other symbols)
OpenDate (don't use spaces)
CloseDate

tblDeposits
DepositID PK autonumber
BatchID FK integer (link to tblBatches)
PaymentMethod
CheckNo
Account
etc

Create a mainform with the Batch details in the header. Create a subform for the deposit details and drag this onto the main form. Access will handle the parent/child links based on the BatchID.