Table Structure for case tracking

[Guest]

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?

 

[Jeff Boyce]

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).

 

[Guest]

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]

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).