many-to-many relationship

[Freeda]

Hi. I am a new access user. I actually posted my query already but I couldn't
find it anymore. Anyway, I hope someone can help me. I have only a month to
work on this project and I don't know if it's too much work for a new user.

Ultimately, I want to search for lenders that would fit the categories on my
2 multi-select list boxes. I would also have "And" and "Or" option buttons
in between the 2 list boxes and a command button to run the query.

So far these are the tables I made:

1) tblLender (Lender Contact Details)
Lender ID - primary key
Lender Name

2) tblLenderDetails
Lender Name
Phone
Contact

3) tblLoanType (I keep on typing the loan types over again because there are
many lenders that relate to the same loan type)
Loan Type (16 types)
Lender ID

4) tblPropertyType (Same thing.. I keep on typing the property types over
again..)
Property Type (20 types)
Lender ID

-- someone suggested that I make two more tables:

5) tblLoans
Loan Type - primary key

Set a one-to-many relationship from tblLoans to tblLoanType on Loan Type

6) tblProperties
Porperty Type - primary key

Set a one-to-many relationship from tblProperties to tblPropType on
PropertyType

-----

For the frmOption:

1) lstLoanType (list the 16 loan types)

2) lstPropType (list of the 20 property types)

3) optAnd

4) optOr

5) cmdRunQuery

----

Each lender can do various loan types and property types. I am not sure how
to relate my tables and if my tables and fields are right in the first place.


Your suggestions will be greatly appreciated.

 

[Klatuu]

First, let's discuss table structure.
Unless you have multiple contacts from the same lender, you only need one
lender table. Even if I had multiple contacts, I would stucture the tables
this way:
LenderMaster
LENDER_ID - primary key
All info about the lender that will always be the same regardless of contact
info.
If I had multiple contacts:
LenderContacts
LEND_CONTACT_ID - primary key
LENDER_ID - foreign key to Lender Master
All info specific to this contact.

The advice you got suggesting the additional tables is correct.
PropertyType
PROPERTY_ID primary key
All the info about the property type

LoanType
LOAD_TYPE_ID primary key

Now since multipe llenders can offer multiple loan types, you have a many to
many relationship which exists only in the real world, not the database
world. It is necessary to create cross reference tables to determine which
lenders offer which loan types. The same is true of property types.

LenderProperties
LEN_PROP_ID - primary key
LENDER_ID - foreign key to identify lender
PROPERTY_ID - foreign key to identify property type

LenderLoanTypes
LEN_LOAN_ID - primary key
LENDER_ID - foreign key to identify lender
LOAD_TYPE_ID foreign key to identify loan type

These two tables give you a unique list for each lender as to what loan
types they offer and what propery types they will loan on.

Now, as to your multi select list boxes. First, you And/Or option group may
create a challenge in a multi select situation where you have 2 list boxes.
I am not certain if you mean "These Loans" And "These Property Types" or
what, so you may need to consider how you do that. Next, you are going to
have to have a way to build selection criteria from the selections in the
list box. As an example, I have included the code below that I use to build
a Where condition for a report

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "tblBudgetVSActualLbrPO.BillableProductOffering = '"
& _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = "tblBudgetVSActualLbrPO.BillableProductOffering IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

 

[KARL DEWEY]

See my response under TABLE/Database Design.

 

[Freeda]

I did the following tables:

1) tblLenderMaster
Lender ID (auto number)
Lender Name
Contact

2) tblLoanType
Loan Type ID (number) 1 to 16
Loan Type

3) tblPropType
Property Type ID (number) 17 to 36
Property Type

4) tblLenderLoan
Lender Property ID (auto number)
Lender ID
Loan Type ID

5) tblLenderProp
Lender Property Type ID (auto number)
Lender ID
Property Type ID

I did the following relationships:

tblLenderMaster - tblLenderLoan (Lender ID)
tblLenderMaster - tblLenderProp (Lender ID)
tblLenderLoan - tblLoanType (Loan Type ID)
tblLenderProp - tblPropType (Property Type ID)

When I did a query, it gave me an error, data type mismatch in expression.

As for the two list boxes:

1) lstLoanType - lists the 16 different loan types; multi-select.
2) lstPropType - lists the 20 different loan types; multi-select.

I don't know if the two option buttons are necessary. If yes, do I place
the code below on the two option buttons? Since I'm not too familiar with
SQL yet, how do I build the code for the SQL statement for the command
button? I'm thinking of setting up a string for each of the list boxes and
the and/or condition. Do I need a stored query too?

 

[Freeda]

I already did the tables and the relationships. I also did a query and so
far everything seems to be working perfectly. I am now working on my form.
I have two list boxes: 1) lstLoanType and 2) lstPropType; two option buttons:
1) optAnd and 2) optOr; and one command button (cmdRunQuery). I did the
following on click of the command button:

1) Coding the option buttons

If value for optAnd is True then optOr is False.

2) Variables to hold choices from the list box/option button then get the
choices from the list boxes

Dim strLoanType as String
Dim strPropType as String
Dim strPropCondtion as String


- I am having trouble building the query's SQL statement then applying the
SQL statement to the stored query. Will this work?