Hi there, long-time lurker, first-time poster.

I'm working on an Access project at work, and I was hoping I could get some pointers/tips on my data modeling. Basically, attorney information is being kept on an Excel spreadsheet, and I ported it over to an Access database. About half of the firms have a contact attorney, the other half doesn't, but regardless of attorneys there is still data for each firm.

In many cases, one firm can have multiple locations across the United States. Also, there are unique records that pertain to a firm that are spread out across each of their locations, and there is also information that is unique to each individual location within the same firm entity.

Here are my tables:

tblGlobalFirmAttributes
FirmID
Firm
FirmURL
TINNumber
CurrentFeeAgreement
FirmSpecialty
FirmStatus

tblFirmLocationAttributes
FirmID
LocID
Address1
Address2
City
State
Zip
Fax
Phone

tblAttorneyAttributes
AttorneyKey
LocID
Title
FirstName
MiddleName
LastName
Surname
AttorneyStatus
AttorneySpecialty
AttorneyRate
AttorneyPreviousRate
EmailAddress
MobileNumber
AttorneyNotes

My thought process is the following. Each attorney has a location ID, pertaining to a location in tblFirmLocationAttributes (LocID). There is a FirmID key in tblFirmLocationAttributes that connects a location to the firm it belongs to, which is done via the FirmID key in tblGlobalFirmAttributes. Many attorneys can work in the same location, and there can be many locations that pertain to the same firm, and I set up my relationships accordingly.

Attached is a picture showing my relationships. If you guys can offer some tips and/or advice, it would be greatly appreciated! This is my first time using Access extensively, so i'm all ears for suggestions/constructive criticism. Thanks!

Couple of pointers. I wouldn't have the 5 ACTMailing felds in tbleGlobalFirmAttributes. Instead just have 1 field; ACTMailingLocID as a Foreign key to your Locs table. I would have lookup tables for things like Attormey specialty, Firm Specialty etc. Not sure how standard ther Rate and fee agreements are, but if they are you might have a separate a table for them with a join table to link them to the Attorney and/or firm.

Couple of pointers. I wouldn't have the 5 ACTMailing felds in tbleGlobalFirmAttributes. Instead just have 1 field; ACTMailingLocID as a Foreign key to your Locs table. I would have lookup tables for things like Attormey specialty, Firm Specialty etc. Not sure how standard ther Rate and fee agreements are, but if they are you might have a separate a table for them with a join table to link them to the Attorney and/or firm.
The reason I put it in my global firm table is because ACT mailing addresses are unique per firm. Even if Firm Z has 10 locations across the U.S., the ACT mailing address will be the same for all locations. Rather than have multiple ACT mailing address info for a number of locations, I just put it in the global firm table. Since each location ties to a unique firm, having only one instance of the ACT mailing address info per firm and having that info tied to multiple locations seemed like my best option. The same goes for the other fields in the global firm table (fee agmts, tin #s, vidn #'s, firm status info)

I apologize for not providing more info on all of the other tables, I noticed a lot more tables are in my actual database than I had originally typed out in my first post.

Anyhow, is my rationale for the ACT info sound? Or would you still suggest that I put that information in my Location tables and have multiple instances of the ACT information?

The reason I put it in my global firm table is because ACT mailing addresses are unique per firm. Even if Firm Z has 10 locations across the U.S., the ACT mailing address will be the same for all locations. Rather than have multiple ACT mailing address info for a number of locations, I just put it in the global firm table. Since each location ties to a unique firm, having only one instance of the ACT mailing address info per firm and having that info tied to multiple locations seemed like my best option. The same goes for the other fields in the global firm table (fee agmts, tin #s, vidn #'s, firm status info)

That's not the point. The address also exist in the Location table. Therefore, you are being redundant by including it in the global table. By indicating which location contains the ACT address using a foreign key, you eliminate the redundant info.

I apologize for not providing more info on all of the other tables, I noticed a lot more tables are in my actual database than I had originally typed out in my first post.

Anyhow, is my rationale for the ACT info sound? Or would you still suggest that I put that information in my Location tables and have multiple instances of the ACT information?

As indicated I stand by my original recommendation. What I don't get is why you think that makes for "multiple instances of the ACT information". Each firm will have at least ONE location. All those locations should be in your locations table. If there is only one Location for a firm that record is licked as the ACT info. If there are multiple locations, then the ACT FK indicates whihc LOC contains the ACT info.

Ah, thanks for the pointers! I see what you mean now more clearly after your last post, and it makes sense to me now.

Thanks for the help, I really appreciate it :)