If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help me Understand how to start
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 |
#2
|
|||
|
|||
Help me Understand how to start
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 "John" wrote in message ... 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 |
#3
|
|||
|
|||
Help me Understand how to start
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. 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 "John" wrote in message ... 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 |
#4
|
|||
|
|||
Help me Understand how to start
"John" wrote in message
... 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. 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 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 he http://www.members.shaw.ca/AlbertKal...icles/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 -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#5
|
|||
|
|||
Help me Understand how to start
John wrote:
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 |
#6
|
|||
|
|||
Help me Understand how to start
John wrote:
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 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 he 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 |
Thread Tools | |
Display Modes | |
|
|