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
|
|||
|
|||
Table Structure for case tracking
Hi. I’m relatively new to working out how to structure tables, so hope
someone can help me I need to set up a case tracking database which tracks leads and where they come from and then when they turn into actual cases, the progress of the case is tracked. I already have the lead tracking part, but now need to expand it. So far I have Table – lead source Table – Leads received – Lead source, client name, email address, phone number, date lead received, follow up dates, case notes and then date lead turned into a case to process. I’m then stuck on how to structure the case tracking part of my data base. I need to record more client details than are needed for the lead and more follow up dates and case comments, but need to see the original notes too, plus details of their case (such as who we’re dealing with to resolve it). Should I break down my leads received so that customer details are in a separate table which I can add to? But if I do how do I structure the rest of the details I need to record? Can anyone help me please? |
#2
|
|||
|
|||
It sounds, from your description, like there can be multiple "events" that
you want to track, once a case becomes a case. If so, that means you have a one (case) to many (case events) relationship. Handle that with a CaseEvents table, using the rowID from tblCase as a foreign key in the CaseEvents table (if "foreign keys" are, well, foreign to you, the simple answer is they are how Access knows which rows in CaseEvents belong to which case -- read up on primary and foreign keys). -- Good luck Jeff Boyce Access MVP "Mattymoo" wrote in message ... Hi. I’m relatively new to working out how to structure tables, so hope someone can help me I need to set up a case tracking database which tracks leads and where they come from and then when they turn into actual cases, the progress of the case is tracked. I already have the lead tracking part, but now need to expand it. So far I have Table – lead source Table – Leads received – Lead source, client name, email address, phone number, date lead received, follow up dates, case notes and then date lead turned into a case to process. I’m then stuck on how to structure the case tracking part of my data base. I need to record more client details than are needed for the lead and more follow up dates and case comments, but need to see the original notes too, plus details of their case (such as who we’re dealing with to resolve it). Should I break down my leads received so that customer details are in a separate table which I can add to? But if I do how do I structure the rest of the details I need to record? Can anyone help me please? |
#3
|
|||
|
|||
Thank you that helps a lot. One thing I'm still a bit unsure of though is, I
still need to see details that were recorded when the case was a lead - do I use foreign keys on a one to one relationship to pick those up? "Jeff Boyce" wrote: It sounds, from your description, like there can be multiple "events" that you want to track, once a case becomes a case. If so, that means you have a one (case) to many (case events) relationship. Handle that with a CaseEvents table, using the rowID from tblCase as a foreign key in the CaseEvents table (if "foreign keys" are, well, foreign to you, the simple answer is they are how Access knows which rows in CaseEvents belong to which case -- read up on primary and foreign keys). -- Good luck Jeff Boyce Access MVP "Mattymoo" wrote in message ... Hi. I’m relatively new to working out how to structure tables, so hope someone can help me I need to set up a case tracking database which tracks leads and where they come from and then when they turn into actual cases, the progress of the case is tracked. I already have the lead tracking part, but now need to expand it. So far I have Table – lead source Table – Leads received – Lead source, client name, email address, phone number, date lead received, follow up dates, case notes and then date lead turned into a case to process. I’m then stuck on how to structure the case tracking part of my data base. I need to record more client details than are needed for the lead and more follow up dates and case comments, but need to see the original notes too, plus details of their case (such as who we’re dealing with to resolve it). Should I break down my leads received so that customer details are in a separate table which I can add to? But if I do how do I structure the rest of the details I need to record? Can anyone help me please? |
#4
|
|||
|
|||
One possibility is that you don't need to separate "leads" from "cases" ...
If you have a field for a date when the lead became a case, and the information about the lead is information you'd want to have about the case (for the most part), why not just leave it in the same table? By the way, your original post mentioned "follow up dates" and "case notes" as if they were fields in the table. These, too, sound like one-to-many relationships and may deserve their own table(s). -- Good luck Jeff Boyce Access MVP "Mattymoo" wrote in message ... Thank you that helps a lot. One thing I'm still a bit unsure of though is, I still need to see details that were recorded when the case was a lead - do I use foreign keys on a one to one relationship to pick those up? "Jeff Boyce" wrote: It sounds, from your description, like there can be multiple "events" that you want to track, once a case becomes a case. If so, that means you have a one (case) to many (case events) relationship. Handle that with a CaseEvents table, using the rowID from tblCase as a foreign key in the CaseEvents table (if "foreign keys" are, well, foreign to you, the simple answer is they are how Access knows which rows in CaseEvents belong to which case -- read up on primary and foreign keys). -- Good luck Jeff Boyce Access MVP "Mattymoo" wrote in message ... Hi. I’m relatively new to working out how to structure tables, so hope someone can help me I need to set up a case tracking database which tracks leads and where they come from and then when they turn into actual cases, the progress of the case is tracked. I already have the lead tracking part, but now need to expand it. So far I have Table – lead source Table – Leads received – Lead source, client name, email address, phone number, date lead received, follow up dates, case notes and then date lead turned into a case to process. I’m then stuck on how to structure the case tracking part of my data base. I need to record more client details than are needed for the lead and more follow up dates and case comments, but need to see the original notes too, plus details of their case (such as who we’re dealing with to resolve it). Should I break down my leads received so that customer details are in a separate table which I can add to? But if I do how do I structure the rest of the details I need to record? Can anyone help me please? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Table structure, Child/Parent relationship for Family Tree Databas | Mike | Database Design | 5 | July 17th, 2004 11:39 PM |
Name not showing ID is | René | Setting Up & Running Reports | 11 | June 29th, 2004 01:40 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |
Table design | BillT | New Users | 11 | May 25th, 2004 03:41 PM |