How do I set up this Database?

[John]

I have a good understanding of database systems, and I am an intermediate
programmer of VBA, but for the life of me, I can not figure Access out. I am
getting older!!! Maybe I can't learn new thing.

I would like to create a database to track customer complaints on a
delivered manufactured home.
When the customer calls or we visit them, they usually have from 3 to 20
complaints.

My thoughts on a simple level:
Customer Table - CustID, name, address, phone numbers etc.
Report Table - ReportID, custID, report date
Issue Table - IssueID, reportID, issue description, priority, due date,
completed date, resolved(Yes/No)

Relationships will be obvious to you all since you are smarter than me!

What I am trying to achieve is to have a single for with, say, a list box
with all customers in the customer table. Upon selecting a customer it
would show all reports that have been generatedin a list box, then clicking
a report would show the listing of issues in that report.

I just dont understand how to link it all together into one form; how does
the issue sub form know to update; do I have to requery each time a customer
is selected or a new report. What if I choose a customer. Can I add a new
report and have it show an empty issue report where I can enter in the
issues.

I know I can do it with VBA, but I also know that it would be sloppy, since
this is what Access is here for.

Please help and guide me

John

 

[ken]

John:

Your model assumes that each customer purchases only one home, which
is generally likely to be true, but, on the basis that a well designed
database caters for what's possible not just what's expected, unless
there is a business constraint which disallows the sale of more than
one home to the same customer, which could be sales at different times
of course, the Reports table also needs a HomeID foreign key
referencing a Homes table. The relationship is this a three–way
(ternary) one between Customers, Homes and Reports.

As regards the interface an unbound form with an unbound list box of
customers, and two correlated subforms, each in continuous form view,
would do what you have in mind. The list box would be set up as
follows:

RowSource: SELECT CustID, CustName
FROM Customers ORDER BY CustName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access
will automatically convert the last one. The important thing is that
the first dimension is zero to hide the first column and that the
second is at least as wide as the combo box.

The subform control for Reports would have as its LinkMasterFields
property the name of the list box, e.g. lstCustomers, and as its
LinkChildFileds property the name of the corresponding column in the
subform's underlying table, i.e. CustID.

To correlate the Issues subform with the Reports subform add a text
box txtReportIDHidden to the parent form and set its Visible property
to False (No). As its ControlSource reference the ReportID of the
Reports subform with:

=sfcReports.Form!ReportID

where sfcReports is the name of the subform control housing the
reports subform, not the name of its source form object (unless both
have the same name).

The LinkMasterFields property of the Issues subform control will be
the name of the hidden text box, txtReportIDHidden, and its
LinkChildFields property will be the name of the corresponding column
in its underlying table, ReportID.

When you select a customer from the list box the Repoprts subform will
be requeried to show the reports for that customer only. As you
select a report in the subform the Issues subform will be requeried,
by means of the link via the hidden text box, to show the issues
relating to the selected report.

If you wanted to cater for a customer purchasing more than one home
you could also include a list box of Homes in the parent form with a
RowSource property such as:

SELECT HomeID, HomeAddress FROM Homes WHERE CustID = Form!lstCustomers
ORDER BY HomeAddress;

Note the use of the Form property rather than a full reference to the
current form here. The list box's other properties would be as fir
lstCustomers. In lstCustomers AfterUpdate event procedure requery the
Homes list box with:

Me.lstHomes.Requery

The LinkMasterFields property for the Reports subform control would
now be:

lstCustomers;lstHomes

and its LinkChildFields property would be:

CustID;HomeID

Ken Sheridan
Stafford, England