First time poster and I've been scouring the internet for the last week trying to find solutions. I'm fairly new to Access and I'm attempting to build a contract management/transaction database. To start, I've got a screenshot of my tables and current relationships.
Starting at the far right, department and status are present for combo boxes. Status I think I can change to be nothing more than a yes/no field on the Contracts table, though (a contract is either active or inactive).
The Contracts table is exactly that; I don't know I need a sequence field on this table since that's its own table. This table is for the basic information about the contract; who it's with, term, originating department, etc.
The FOAPAL table is our internal accounting descriptor. I don't know if I need to set the primary key as it is, but I wanted to avoid redundant information.
The Sequences table is where I think I start hitting problems. The way the contracts work is each contract can have 1-??? sequences (literally, just sequential identifiers). Each sequence identifies a specific funding source (FOAPAL) for that contract. Every contract will have Sequence 1, so I created a compound key of ContractNum + Sequence for the table. Contract A Sequence 1 is unique, Contract B Sequence 1 can exist and is also unique. Each sequence for each contract will have a specific funding source; it's possible for that to be a repeat, thus the 1:M relationship FOAPAL:Sequences.
Categories feed into the transaction table for expense/revenue.
The Transactions table is the ledger part of the DB and used to record the transactions as it relates to each sequence of a contract.
Are there apparent flaws with the relationship structure and/or table design? If so, what are they and what advice can be offered to resolve them?