Agencies document tracker

Edmond Sane-Aka
Edmond Sane-Aka used Ask the Experts™
on
I'm creating an agencies document tracker database. I've already started the database which I've attached. But i want to format the database so that the DERA table will be the main table follow by the counties and agencies so that he can look like the second attachment.  Any assistance will be appreciated.
Agencies.accdb
Document.pdf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't see any foreign keys in the database.

If DERA is the main table and Counties is a child table, it will likely be a many-to-many relationship.

If so, you will need an intermediate table like dera_county that holds dera.id and county.id.

Same for the relationship between counties and agencies.  If ALL agencies are in the same county then you might get by with just adding a county_id column to the agencies table.

That said:  The agency and county tables are pretty much the same format.  I might think about merging them into a single documents table with a owner_type column with a 'C' or 'A' column to designate County or Agency.

If you have additional columns for county that don't belong to agencies then you can create separate county and agency tables but I would look to keep the document table independent with the appropriate foreign key column in it.

Author

Commented:
slightwv,

thanks for the advice I'll try that

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial