many-to-many relationship

[Freeda]

I am a new Access user. I just got the program and some tutorial books. I
am not sure how to relate my tables. I have 3 tables:

1) tblLender (Lender Contact Details)
Lender ID
Lender Name
Address
Contact
Phone

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

3) tblPropertyType (Same here... I keep on typing the property types over
again..)
Property Type
Lender ID

-- Each Lender can do various loan types and property types. I am not sure
how to relate my table and if my tables and fields are right in the first
place. Your suggestions will be greatly appreciated.

Best regards

 

[KARL DEWEY]

Not much to go. What do you want to collect data for? What kind of reports
– totals dollars loaned for single dwelling versa apartments?

 

[Freeda]

Hi Karl

Ulitimately, i want a form with two multi-select list boxes, "And" and "Or"
option buttons in between the list boxes and a command button to run the
query.

List boxes:
1) lstLoanType (list the loan types)
2) lstPropType (list the property types)

After making the choices for each of the list boxes and the option button,
run the query and list the lenders that would fall under those categories.

I only have a month to work on this project. Is it doable? Or is it too
much for a new user? Please advise.

Thanks!

 

[KARL DEWEY]

First I would add two tables – Loans and Properties.
Loans --
Loan Type – primary key

Properties –
Property Type - primary key

Set a one-to-many relations from Loans to tblLoanType on Loan Type.
Set a one-to-many relations from Properties to tblPropertyType on Property
Type.

In the form header put you two listbox, one command buttons, and a checkbox
for AND/OR.

One command button will not work unless you omit selecting and manually do
it twice.

This is tested SQL –

SELECT [Old-New].NewID, [Old-New].OldID
FROM [Old-New]
WHERE ((([Old-New].NewID)=[Forms]![AND-OR Form]![List2]) AND
(([Old-New].OldID)=[Forms]![AND-OR Form]![List4]) AND (([Forms]![AND-OR
Form]![check6])=0)) OR ((([Old-New].OldID)=[Forms]![AND-OR Form]![List4]) AND
(([Forms]![AND-OR Form]![check6])=-1)) OR ((([Old-New].NewID)=[Forms]![AND-OR
Form]![List2]) AND (([Forms]![AND-OR Form]![check6])=-1));

 

[KARL DEWEY]

Here is the data from the form --
P:\Access Databases\Test1.mdb Friday, December 02, 2005
Form: AND-OR Form Page: 1
Properties
AllowAdditions: True AllowDatasheetView: True
AllowDeletions: True AllowDesignChanges: True
AllowEditing: True AllowEdits: True
AllowFilters: True AllowFormView: True
AllowPivotChartView: True AllowPivotTableView: True
AllowUpdating: No AutoCenter: False
AutoResize: True BorderStyle: Sizable
CloseButton: True Container: Forms
ControlBox: True Count: 11
CurrentView: 0 Cycle: All Records
DataEntry: False DatasheetBackColor: 16777215
DatasheetBorderLineStyle: 1 DatasheetCellsEffect: Flat
DatasheetColumnHeaderUnde 1 DatasheetFontHeight: 10
DatasheetFontItalic: False DatasheetFontName: Arial
DatasheetFontUnderline: False DatasheetFontWeight: Normal
DatasheetForeColor: 0 DatasheetGridlinesBehavior: Both
DatasheetGridlinesColor: 12632256 DateCreated: 12/2/2005 3:23:56 PM
DefaultEditing: 2 DefaultView: Continuous Forms
DividingLines: True FastLaserPrinting: True
FetchDefaults: True FilterOn: False
FrozenColumns: 1 GridX: 24
GridY: 24 GUID: {guid {FA57A500-A8A3-42F4-
B0CB-2712D9B596E8}}
HasModule: True HelpContextId: 0
HorizontalDatasheetGridlineSt 1 Hwnd: 6816720
InsideHeight: 4920 InsideWidth: 8985
KeyPreview: False LastUpdated: 12/2/2005 3:23:56 PM
LayoutForPrint: False LogicalPageWidth: 9360
MaxButton: True MinButton: True
MinMaxButtons: Both Enabled Modal: False
Moveable: True NameMap: Long binary data
NavigationButtons: True OrderByOn: False
Orientation: Left-to-Right Owner: admin
Painting: True PaletteSource: (Default)
Picture: (none) PictureAlignment: Center
PictureSizeMode: Clip PictureTiling: False
PictureType: 0 PopUp: False
PrtDevMode: Long binary data PrtDevNames: Long binary data
PrtMip: Long binary data RecordLocks: No Locks
RecordSelectors: True RecordsetType: Dynaset
RecordSource: AND-OR RowHeight: Default
ScrollBars: Both ShortcutMenu: True
ShowGrid: True SubdatasheetExpanded: False
SubdatasheetHeight: 0 TimerInterval: 0
UserName: admin VerticalDatasheetGridlineStyle 1
ViewsAllowed: Both Visible: False
WhatsThisButton: False Width: 9420
WindowHeight: 5745 WindowLeft: 705
WindowTop: 1035 WindowWidth: 9420

P:\Access Databases\Test1.mdb Friday, December 02, 2005
Form: AND-OR Form Page: 2
Objects
Section: Detail
BackColor: -2147483633 CanGrow: False
CanShrink: False DisplayWhen: Always
EventProcPrefix: Detail ForceNewPage: None
HasContinued: False Height: 300
InSelection: False KeepTogether: False
Name: Detail NewRowOrCol: None
SpecialEffect: Flat Visible: True
WillContinue: False
Section: FormFooter
BackColor: -2147483633 CanGrow: False
CanShrink: False DisplayWhen: Always
EventProcPrefix: FormFooter ForceNewPage: None
HasContinued: False Height: 360
InSelection: False KeepTogether: False
Name: FormFooter NewRowOrCol: None
SpecialEffect: Flat Visible: True
WillContinue: False
Section: FormHeader
BackColor: -2147483633 CanGrow: False
CanShrink: False DisplayWhen: Always
EventProcPrefix: FormHeader ForceNewPage: None
HasContinued: False Height: 525
InSelection: False KeepTogether: False
Name: FormHeader NewRowOrCol: None
SpecialEffect: Flat Visible: True
WillContinue: False
Check Box: Check6
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Solid BorderWidth: Hairline
ColumnHidden: False ColumnOrder: Default
ColumnWidth: Default ControlType: 106
DisplayWhen: Always Enabled: True
EventProcPrefix: Check6 Height: 240
HelpContextId: 0 Left: 6420
Locked: False Name: Check6
OldBorderStyle: 1 ReadingOrder: Context
Section: 1 SpecialEffect: Sunken
TabIndex: 2 TabStop: True
Top: 90 TripleState: False
Visible: True Width: 720
Command Button: Command8
AutoRepeat: False Cancel: False
Caption: Refresh ControlType: 104
Default: False DisplayWhen: Always
Enabled: True EventProcPrefix: Command8
FontBold: No FontItalic: False
FontName: Tahoma FontSize: 8
FontUnderline: False FontWeight: Normal
ForeColor: -2147483630 Height: 405
HelpContextId: 0 Left: 7980
Name: Command8 OnClick: Macro1.ReRunQuery

P:\Access Databases\Test1.mdb Friday, December 02, 2005
Form: AND-OR Form Page: 3
Picture: (none) PictureType: 0
ReadingOrder: Context Section: 1
TabIndex: 3 TabStop: True
TextFontCharSet: 0 Top: 120
Transparent: False Visible: True
Width: 810
Label: Label0
BackColor: 16777215 BackStyle: Transparent
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 Caption: NewID:
ControlType: 100 DisplayWhen: Always
EventProcPrefix: Label0 FontBold: No
FontItalic: False FontName: Tahoma
FontSize: 8 FontUnderline: False
FontWeight: Normal ForeColor: 0
Height: 240 HelpContextId: 0
Left: 0 LeftMargin: 0
LineSpacing: 0 Name: Label0
NumeralShapes: System OldBorderStyle: 0
ReadingOrder: Context RightMargin: 0
Section: 0 SpecialEffect: Flat
TextAlign: General TextFontCharSet: 0
Top: 0 TopMargin: 0
Vertical: False Visible: True
Width: 615
Label: Label1
BackColor: 16777215 BackStyle: Transparent
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 Caption: OldID:
ControlType: 100 DisplayWhen: Always
EventProcPrefix: Label1 FontBold: No
FontItalic: False FontName: Tahoma
FontSize: 8 FontUnderline: False
FontWeight: Normal ForeColor: 0
Height: 240 HelpContextId: 0
Left: 3120 LeftMargin: 0
LineSpacing: 0 Name: Label1
NumeralShapes: System OldBorderStyle: 0
ReadingOrder: Context RightMargin: 0
Section: 0 SpecialEffect: Flat
TextAlign: General TextFontCharSet: 0
Top: 0 TopMargin: 0
Vertical: False Visible: True
Width: 540
Label: Label5
BackColor: 16777215 BackStyle: Transparent
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 Caption: OLD
ControlType: 100 DisplayWhen: Always
EventProcPrefix: Label5 FontBold: No
FontItalic: False FontName: Tahoma
FontSize: 8 FontUnderline: False

P:\Access Databases\Test1.mdb Friday, December 02, 2005
Form: AND-OR Form Page: 4
FontWeight: Normal ForeColor: 0
Height: 245 HelpContextId: 0
Left: 3900 LeftMargin: 0
LineSpacing: 0 Name: Label5
NumeralShapes: System OldBorderStyle: 0
ReadingOrder: Context RightMargin: 0
Section: 1 SpecialEffect: Flat
TextAlign: General TextFontCharSet: 0
Top: 60 TopMargin: 0
Vertical: False Visible: True
Width: 660
Label: Label7
BackColor: 16777215 BackStyle: Transparent
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 Caption: AND - OR
ControlType: 100 DisplayWhen: Always
EventProcPrefix: Label7 FontBold: No
FontItalic: False FontName: Tahoma
FontSize: 8 FontUnderline: False
FontWeight: Normal ForeColor: 0
Height: 240 HelpContextId: 0
Left: 6650 LeftMargin: 0
LineSpacing: 0 Name: Label7
NumeralShapes: System OldBorderStyle: 0
ReadingOrder: Context RightMargin: 0
Section: 1 SpecialEffect: Flat
TextAlign: General TextFontCharSet: 0
Top: 60 TopMargin: 0
Vertical: False Visible: True
Width: 975
List Box: List2
BackColor: 16777215 BorderColor: 0
BorderLineStyle: Solid BorderStyle: Solid
BorderWidth: Hairline BoundColumn: 1
ColumnCount: 1 ColumnHeads: False
ColumnHidden: False ColumnOrder: Default
ColumnWidth: Default ColumnWidths: 1440
ControlType: 110 DisplayWhen: Always
Enabled: True EventProcPrefix: List2
FontBold: No FontItalic: False
FontName: Tahoma FontSize: 8
FontUnderline: False FontWeight: Normal
ForeColor: 0 Height: 360
HelpContextId: 0 IMEHold: False
IMEMode: 0 IMESentenceMode: 3
Left: 1560 Locked: False
MultiSelect: No Name: List2
NumeralShapes: System OldBorderStyle: 1
ReadingOrder: Context RowSource: SELECT [Old-New].NewID FROM [Old-
New];
RowSourceType: Table/Query ScrollBarAlign: System
Section: 1 SpecialEffect: Sunken
TabIndex: 0 TabStop: True
TextFontCharSet: 0 Top: 0
Visible: True Width: 1440

P:\Access Databases\Test1.mdb Friday, December 02, 2005
Form: AND-OR Form Page: 5
List Box: List4
BackColor: 16777215 BorderColor: 0
BorderLineStyle: Solid BorderStyle: Solid
BorderWidth: Hairline BoundColumn: 1
ColumnCount: 1 ColumnHeads: False
ColumnHidden: False ColumnOrder: Default
ColumnWidth: Default ColumnWidths: 1440
ControlType: 110 DisplayWhen: Always
Enabled: True EventProcPrefix: List4
FontBold: No FontItalic: False
FontName: Tahoma FontSize: 8
FontUnderline: False FontWeight: Normal
ForeColor: 0 Height: 300
HelpContextId: 0 IMEHold: False
IMEMode: 0 IMESentenceMode: 3
Left: 4620 Locked: False
MultiSelect: No Name: List4
NumeralShapes: System OldBorderStyle: 1
ReadingOrder: Context RowSource: SELECT [Old-New].OldID FROM [Old-
New];
RowSourceType: Table/Query ScrollBarAlign: System
Section: 1 SpecialEffect: Sunken
TabIndex: 1 TabStop: True
TextFontCharSet: 0 Top: 60
Visible: True Width: 1440
Text Box: NewID
AllowAutoCorrect: True AutoTab: False
BackColor: 16777215 BackStyle: Normal
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 CanGrow: False
CanShrink: False ColumnHidden: False
ColumnOrder: Default ColumnWidth: Default
ControlSource: NewID ControlType: 109
DecimalPlaces: Auto DisplayWhen: Always
Enabled: True EnterKeyBehavior: False
EventProcPrefix: NewID FilterLookup: Database Default
FontBold: No FontItalic: False
FontName: Tahoma FontSize: 8
FontUnderline: False FontWeight: Normal
ForeColor: 0 Height: 240
HelpContextId: 0 IMEHold: False
IMEMode: 0 IMESentenceMode: 3
IsHyperlink: False KeyboardLanguage: 0
Left: 1440 LeftMargin: 0
LineSpacing: 0 Locked: False
Name: NewID NumeralShapes: System
OldBorderStyle: 0 ReadingOrder: Context
RightMargin: 0 ScrollBarAlign: System
ScrollBars: Neither Section: 0
SpecialEffect: Sunken TabIndex: 0
TabStop: True TextAlign: General
TextFontCharSet: 0 Top: 0
TopMargin: 0 Vertical: False
Visible: True Width: 1440

P:\Access Databases\Test1.mdb Friday, December 02, 2005
Form: AND-OR Form Page: 6
Label: NewID1_Label
BackColor: 16777215 BackStyle: Transparent
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 Caption: New
ControlType: 100 DisplayWhen: Always
EventProcPrefix: NewID1_Label FontBold: No
FontItalic: False FontName: Tahoma
FontSize: 8 FontUnderline: False
FontWeight: Normal ForeColor: 0
Height: 245 HelpContextId: 0
Left: 720 LeftMargin: 0
LineSpacing: 0 Name: NewID1_Label
NumeralShapes: System OldBorderStyle: 0
ReadingOrder: Context RightMargin: 0
Section: 1 SpecialEffect: Flat
TextAlign: General TextFontCharSet: 0
Top: 120 TopMargin: 0
Vertical: False Visible: True
Width: 720
Text Box: OldID
AllowAutoCorrect: True AutoTab: False
BackColor: 16777215 BackStyle: Normal
BorderColor: 0 BorderLineStyle: Solid
BorderStyle: Transparent BorderWidth: Hairline
BottomMargin: 0 CanGrow: False
CanShrink: False ColumnHidden: False
ColumnOrder: Default ColumnWidth: Default
ControlSource: OldID ControlType: 109
DecimalPlaces: Auto DisplayWhen: Always
Enabled: True EnterKeyBehavior: False
EventProcPrefix: OldID FilterLookup: Database Default
FontBold: No FontItalic: False
FontName: Tahoma FontSize: 8
FontUnderline: False FontWeight: Normal
ForeColor: 0 Height: 240
HelpContextId: 0 IMEHold: False
IMEMode: 0 IMESentenceMode: 3
IsHyperlink: False KeyboardLanguage: 0
Left: 3720 LeftMargin: 0
LineSpacing: 0 Locked: False
Name: OldID NumeralShapes: System
OldBorderStyle: 0 ReadingOrder: Context
RightMargin: 0 ScrollBarAlign: System
ScrollBars: Neither Section: 0
SpecialEffect: Sunken TabIndex: 1
TabStop: True TextAlign: General
TextFontCharSet: 0 Top: 0
TopMargin: 0 Vertical: False
Visible: True Width: 1440

 

[Freeda]

Karl, 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 property types; multi-select.

I don't know if the two option buttons are necessary. If yes, do I place
the code below (given by Klatuu) 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?

---

For the SQL code you gave me, do I need to put that on my option buttons? I
do apologize for the confusion.

Thanks again.