Partially Duplicated Info

[QuantifyRisk]

Doesn't a FK essentially create a relationship in the same way?

[June7]

Just having the fields doesn't establish relationship. Access doesn't know there is relationship unless you define it. That's what Relationships window is for. However, as rpeare stated, many experienced developers don't bother building relationships. They build db so users do not interact directly with tables and queries and then manage data integrity with form design and code.

[QuantifyRisk]

I still have to build the UI for the data to be entered in for the reps.

[June7]

Correct. The UI is forms and reports managed with code.

[QuantifyRisk]

This is what I did last night. Originally, the 5 tables were connected. Then, a friend told me that I don't need junctions because I have a one-to-many on one side and a one-to-one of the other. (producers can only work for one agency). So I changed it on the left side and left the right side with the junctions (for now). I left the agency2producer junction in for reference.

[June7]

So producers are licensed, not the agencies?

[QuantifyRisk]

Agencies do have their own agency code, but it is different from the producer's.

[June7]

Agencies do not have licenses?

Same agency number is recognized by all states? Who assigns this agency number?

[QuantifyRisk]

Honestly, I'm not sure if their code is the same as a license. It would make sense to me that it's more of a code than a license. An agency has only one code that we use. I'd have to find out who assigns it. I get the feeling it's a reference number for us but I'm not positive.

[June7]

I don't think you need the junction table. Each license belongs to only one producer. Just put the ProducerNumber as foreign key in StateLicense table.

[QuantifyRisk]

Yup. That's my next step. Thanks guys.