Creating tables - Many to Many relationship

[Valery]

Hi,

Would like to create tables for Unit (apartments) Inspections data entry – need assistance in creating tables with proper links
Each section below is One-to-Many to the section below it…

TABLE – Inspection
InspectID PK - Autonumber
UnitNum Number (PK)
InspectDate Date


If the following are in the Inspection table, the unitnum and the inspectdate will be repeated in each record…

Area Number
Item Number
Rating Number (1 to 5)
Findings Text (multiple select field)
Comments Memo

So need a join table or tables…?

Areas
Kitchen
Stairway
Bath1
Bath2
Laundry
And so on…

Items (approx. 75)
Door
Walls
Ceilings
Electricity
Flooring
Toilet
Drain
And so on…

Findings (may select multiple answers)
Needs Repairs
Needs Replacement
Damaged by Tenant
And so on

 

[arnelgp]

add another fields in your master inspection table, ie,
Status="On going", "Closed", "On going repair"
StatusDate = Date

move the other fields to inspection detail table.
just leave inspection number, inspection date, status, status date, remarks on the master inspection.

 

[Valery]

Thank you Arnel.

OK for the following... add another fields in your master inspection table, ie, Status="On going", "Closed", "On going repair" StatusDate = Date

-------
move the other fields to inspection detail table - ALL the fields? Like the areas, and the items? Or are those in lookup tables?

Not sure I understand how I will define for example:

Kitchen - wall - needs repairs
Bathroom - wall - ...
DiningRm - wall - ...

and so on...

Just thinking I would not want the user to have to select each item to be identified for inspection through a dropdown list of 75-100 items.

Best if when Kitchen is selected (or already on the form), that only the items pertaining to that room come up ready for data entry of findings and other (findings).

Some items such as flooring and doors are common to all rooms but others like Fridge and Stove would only pertain to the Kitchen for example.

Do I not need to prepare/plan this at the table level?

I understand this OK : just leave inspection number, inspection date, status, status date, remarks on the master inspection.

THANK YOU

 

[yupstrips01]

(At least one because it doesn't make sense to place an order for no products.) ... We can't represent a many-to-many association directly in a relation scheme, ... in the database, and we need to show the scheme of this table in our diagram