Help me Understand how to start

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
Hi John,
some tables for customer and issue will be a good start.

Customer Table - CustID, name, address, phone numbers etc.
Issue Table - IssueID, CustID, issue description, priority, due date,
completed date, resolved(Yes/No)

One customer can have several complaints (issues).
This means a one to many relationship.
In the relationships window, drag the CustID field from the Customer table
and onto the CustID field in the IssueID.
In the dialog that opens, check the option for referential integrity.

Create a form where you can choose the customer from a combo.
Put a subform on this form where you can enter all the complaints (issues)
for this customer and the details of each issue.


You won't need the report table as shown in your post.

Create a report that shows all issues for each customer.
You can use a form to choose which customer to show when the report is
opened.
You can also design the form to allow you to show all resolved issues, or
other info about issues.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
you do have a good understanding of relational design, from your posted
tables design. one note: if you may have multiple phone numbers for a
customer, recommend a separate table for those - in fact for various forms
of contact info such as phone, fax, cell phone, email, as

tblContactTypes
TypeID (pk)
TypeName

example:
TypeID TypeName
1 Home phone
2 Work phone
3 Cell phone
4 Fax
5 Email address

tblCustContactInfo
PointID (pk)
CustID (foreign key from tblCustomers)
TypeID (fk from tblContactTypes)
Info (this field to store the actual number or email address, according to
the type entered in TypeID field)

there are a number of ways you could set up your data entry form(s),
depending on your workflow. my guess is that you enter new customer records
much less often than you add complaint report records, and you add new
report records less often than you need to update individual issue records
for the various complaint reports. to put everything in a single form, i'd
probably utilize a couple subforms, a listbox, and a tab control.

describing the setup outlined above will take some typing. i'm willing, if
you think we're on the same wavelength here, and that my suggestions may be
useful to you. if so, post back and i'll lay it out for you.
I am
getting older!!! Maybe I can't learn new thing.
Click to expand...

we're all getting older, my friend, so no worries there. to my mind,
relational design is the most difficult aspect of application development to
grasp; you've got that down, so you can get the rest, believe me.

hth
 
You have good advice in this thread already...I add my 2 cents more...

First, the use of the term "reports" threw us off..since we usually think of
that meaning a access report..not a "incident" report as you mean...
What I am trying to achieve is to have a single form 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.
Click to expand...

this makes sense, but in place of using a listbox, use a sub-form because
then that solves the problem of both editing that data, and MORE important
allows you to add additional reports.

And, for the customer, it likely better to have some type of search form to
search/find" a particular customer. You need this search/find process
anytime you update customer information, anytime you want to add reports, or
view reports which in turn allows you to view issues. So, you always "start"
by dealing with a particular customer.

Once you bring up the customer form, then you can have a sub form that
displays all the issues that belong to that customer.
how does
the issue sub form know to update
Click to expand...

The sub-form does not need to be "update" but you would certainly go into
the sub-form to "add" a new Report. (with your current design, that would
involve only typing in a report date. Sub-forms are nice since when you add
a record in the sub-form, the FK (foreign key) is set for you. Keep in mind
that the user never need see the "id" fields used here.

Report Table - ReportID, custID, report date

Just as a note, I assume that ReportID is a autonumber primary key field.
And custID would be a standard plain Jane long number field...right?

So we would start with a customer form/record. And then from there you be
able to add/view/edit each additional Report. And, then when viewing the
report, the VERY SAME process/design works again.

So, we have:

customers ----->reports------->Issues

You might even perhaps build a "search form" I have some screen shots here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Anyway, on our main customers form, we have a sub-form for reports:

You simply "add" a new report record into that sub-for (with date). Perhaps
you even add a comments column to the sub-form:
so,

Report Table - ReportID (PK), custID (FK), ReportDate, Comments

So, you add a new report to this sub-form
ReportDate Comments
02/14/2009 waiting for papers [edit Report details]
01/05/2009 [edit Report details]
etc. etc....

Note the "edit Report details" button. That simply going to be a button that
"repeats" just like those in the screen shots I showed you. That means
we using continues sub-forms (not datasheets, nor a listbox). You can whack
that button to launch up a form that is based on the ONE report
record. That report form will then have sub-form that is based on issues
(so, you just repeat the above design again for the issues). And again you
even put a button called "edit Issue details" This approach also makes
your application more consistent to the users. Once they learn how to add
Reports, then they instant know how to add issues when they finally get to
the Report form that has a issues sub-form.

The code behind the [edit issue details] button will be

if me.dirty = true then
' force write of record to disk
me.Dirty = false
end if

docmd.OpenForm "frmReports",,,"ReportID = " & me!ReportID
 
John said:
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
Click to expand...
 
John said:
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
Click to expand...

You might like to have a look at a very similar facility which has been
built into the "Northwind" sample database. I'm using Access 2003, and
I access this from the Help menu (back it up and tamper with the
backup!). I've learned a lot from Northwind, and quite recently (!) so
I'll be happy to clarify anything that isn't obvious. If you don't have
Northwind, there should be a copy on your Access CD somewhere (you may
have to run the installer to get it?) or someone here should be able to
make a copy available without cheating anyone.

If you have this, go into the list of Forms in the Database window and
open "Customer Orders". You'll see a few details for the customer, and
one subform showing orders for that customer. Click on any order, and
the bottom subform displays order items - close to what you want, I
think? You'd want to add a combo box to pick out a particular customer:
one way is to use the AfterUpdate event on the combo to apply a filter
to the form.

So how do the subforms work? The first one's easy: it uses CustomerID
in both the "Link Child Fields" and "Link Master Fields" properties, and
Access just does the rest for you, including any requerying. In fact,
if your one-to-many relationships are right, and you're using the same
name for the key field, Access will create this connection for you if
you simply drag one form onto another.

I had to puzzle (quite recently!) over how the bottom subform worked.
Again, it's "Link Child Fields" and "Link Master Fields" which provide
the connection, but in this case the "Link Master Fields" has to refer
to the OrderID of the selected Order in the other subform. It does this
by referring to that subform by name, and then drilling down to the
OrderID value: [Customer Orders Subform1].Form![OrderID]

There's a useful table of how to refer to various bits and pieces here:
http://www.mvps.org/access/forms/frm0031.htm

It's not over: you have to trigger a requery in the bottom subform
(Order Details) whenever a new Order is selected. So, in the OnCurrent
event handler for the Orders subform (the upper one), there's a call to
requery the bottom one.

Hope that's helpful - let us know.

Phil, London
 
Back
Top