tables and relationships

[rick_it]

I'm in trouble with three of my tables.
1) tblCommunities (CommunityID is the PK)
2) tblModels (ModelID is the PK and CommunityID is the FK)
3) tblSalesLeads (LeadID is the PK and CommunityID is the FK)

The users should be able to enter a new sales lead and either pick one of
the communities without selecting any of the models or pick one of the
communities and be able to choose one or more models for said community. How
can I get to this result?

I tried to create another table where the PK was concatenated (CommunityID,
ModelID, LeadID) however with this solution I was not able to satisfy the
first option (pick a community without specifing any of the models).

Thanks for any suggestions.

 

[Damian S]

Hi rick_it,

Ensure that your FK fields do not default to 0. This is a trap for young
players, as it will attempt to default to 0 if you don't select an item, in
effect causing an integrity error (as you have no related record with ID 0).
If you set it up like that (without the 0 default), you will be able to enter
a Sales Lead without selecting a Community.

You don't actually specify how you go about selecting Models, but the same
would hold true.

Damian.

 

[rick_it]

Damian,

I can set the FK to Null however I won't be able to select more than one
model per sales lead.

Let me try to be a little bit more clear on my request:

For example, one of the communities is called Alexander Park and has three
models (Sunset, Twilight, and Aurora).

When the users select this community, I'd like to be able to display three
checkboxes. At this point he can submit the form without checking any of the
boxes or he can check any combination of boxes (this is done using asp).

How do I "translate" this on my tables? The system will add only 1 record to
the tblSalesLead and this record will reference to only 1 community in the
tblCommunities. Now how do I reference to more than one model for the 1
community?

Thanks

Rick

 

[Damian S]

Hi Rick,

To simulate a many-to-many relationship in Access, you need to create an
intermediary table to link the other two tables, like this:

tblSalesLeadModels
SalesLeadID number
ModelID number

Link SalesLeadID to tblSalesLead. Link ModelID to tblModels.

Then you have one record for each model selected, recording the SalesLeadID
against each.

Hope that helps.

Damian.

 

[rick_it]

Damian,

would it not make more sense to add in the intermediary table the community
ID as well?

tblSalesLeadModels
intSalesLeadID
intCommunityID
intModelID

When a user starts filling out the form, the system assign an autonumber to
the Sales Lead (intSalesLeadID), then the user has to choose a Community from
a drop down list (intCommunityID), once the community is selected I'd like to
show all the available models for said community (intModelID) and allow the
user to make a multiple selection (if he's interested in more than one model).

I can't see how I can get to the models if I don't ask first the community.
What am I doing wrong?

Thanks.