Am I missing something...query will not populate that includes a junction table

[Nathanlceh]

June7,

In my case, each establishment will have only one license. (I should have clarified earlier, our department is only tracking the state license for the establishment). Sorry for the confusion, and yeah, an establishment can have a state license, liquor license, resort license, etc.... but we are not tracking all types.

I am working on your previous suggestions.

[June7]

You have License Type table which led me to think there were multiple licenses for each establishment but if this means there are different classes of state licenses and only 1 per establishment, then yes, saving license number into EstablishmentT now makes sense. But I don't think you want to add a suffix to the license number unless that is actually how it is issued, which I doubt is the case.

[Nathanlceh]

Davegri, thanks for the revised db...I will learn and duplicate everything you revised. I'm sure questions will follow!

An to answer ArviLaanemets question, kinda.... some owners own several of the same type of establishments across the area, but in most cases, there is only one licensable establishment per building/property. Only if the owner owns both establishments within the same building/property (strip mall) he/she does have the ability to have both establishments under one license.

[Nathanlceh]

June7, I'm in the process of changing my PK for LicenseT to an Autonumber. Thanks for the input!

and to answer your past question, an establishment license number does not transfer with the sale or owner. Once an establishment is sold, the existing license number becomes "void". The new owner will receive a new license number for the establishment, even if the establishment (Dairy Queen for example) stays the same.

[June7]

So then you would replace the LicenseID in EstablishmentT. This means the old license number record becomes an 'orphan' since it is no longer related to an establishment. You don't even have history since there is no way to know which establishment the license was originally issued to. So we are back to the premise that an establishment can have multiple licenses, from an historical perspective. I.e., over the life of an establishment it could have multiple licenses. And then you have the rare case where one license has multiple establishments. So looking at a M:M relationship if you want to maintain history and not have orphans.

[Nathanlceh]

Yes, great insight!