DB Design Question - Prospects/Clients

[Joe Williams]

I have been tasked to create a program to track sales for our company. This
would involve collecting some information about prospects, and then if they
decide to purchase, to turn them into active clients and process a sale
transaction.

I am a little unsure of the logic of how to put this database together. Has
anyone worked with this type of setup, and if so what is the "usual" process
of turning a prospect record into an active client record when a sale is
made?

I was thinking that on a sales form you could have a lookup of prospective
clients, and then somehow run an update to add them to the active clients
table. But also, not only could prospective clients place an order but
existing clients could also be placing an order, so this is where I got
confused.

Any help would be appreciated. Thanks

joe

 

[Allen Browne]

Hi Joe.

Just use ONE table to store both the prospective clients, active clients,
past clients, and so on. It makes like so much easier.

You can easily get Access to tell you whether it's a prospective client or
not. On your form, you could add a text box with control source like this:
=IIf(DLookup("OrderID", "tblOrder", "ClientID = " & Nz([ClientID],0)) Is
Null, "Prospective client", "Actual client")

If you prefer, you can even use 2 different forms.
The RecordSource for actual clients will be:
SELECT tblClient.* FROM tblClient
INNER JOIN tblOrder ON tblClient.ClientID = tblOrder.ClientID;

The RecordSource of the prospective clients form will be:
SELECT tblClient.* FROM tblClient
LEFT JOIN tblOrder ON tblClient.ClientID = tblOrder.ClientID
WHERE tblOrder.OrderID Is Null;

There is no maintenance to do, i.e. no shuffling records from one table to
another. The system can't go wrong: they just automatically turn up in the
other form as soon as they place an order. And there is only one place to
draw records from if you need to do a report to compare the count of
potential and actual clients for each salesperson.

 

[Joe Williams]

Allen,

Makes sense. I was overthinking it (as usual). Thanks!
Joe

Hi Joe.

Just use ONE table to store both the prospective clients, active clients,
past clients, and so on. It makes like so much easier.

You can easily get Access to tell you whether it's a prospective client or
not. On your form, you could add a text box with control source like this:
=IIf(DLookup("OrderID", "tblOrder", "ClientID = " & Nz([ClientID],0))
Is Null, "Prospective client", "Actual client")

If you prefer, you can even use 2 different forms.
The RecordSource for actual clients will be:
SELECT tblClient.* FROM tblClient
INNER JOIN tblOrder ON tblClient.ClientID = tblOrder.ClientID;

The RecordSource of the prospective clients form will be:
SELECT tblClient.* FROM tblClient
LEFT JOIN tblOrder ON tblClient.ClientID = tblOrder.ClientID
WHERE tblOrder.OrderID Is Null;

There is no maintenance to do, i.e. no shuffling records from one table to
another. The system can't go wrong: they just automatically turn up in the
other form as soon as they place an order. And there is only one place to
draw records from if you need to do a report to compare the count of
potential and actual clients for each salesperson.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have been tasked to create a program to track sales for our company.
This would involve collecting some information about prospects, and then
if they decide to purchase, to turn them into active clients and process a
sale transaction.

I am a little unsure of the logic of how to put this database together.
Has anyone worked with this type of setup, and if so what is the "usual"
process of turning a prospect record into an active client record when a
sale is made?

I was thinking that on a sales form you could have a lookup of
prospective clients, and then somehow run an update to add them to the
active clients table. But also, not only could prospective clients place
an order but existing clients could also be placing an order, so this is
where I got confused.

Any help would be appreciated. Thanks

joe