How to setup prospects vs clients?

[Guest]

Is it possible to have a table as the lookup record source, but when the
record is not found, add a new record – but into a different table other than
the record source?

For our company, an order is considered a “prospect,†for lack of a better
term, until the credit card is charged successfully (not denied for any
reason); only then does it become an order. Because we get so many calls
looking for price, and so few actual orders, we don’t want “dirty†our “mainâ€
database with prospect data until it is a real order. But we also want to
keep the prospect data in case they call back to place an order and for
marketing purposes. I’ve considered categorizing the type of record
(prospect, client) but that will give us 10s of thousands of records we
really don’t need in our main file.

I have proposed to have a very similar set of tables that run in tandem.
Sales people use a “Prospect†form (built from multiple tables) to take a
tentative order, then when the credit card is successfully run, we append
that data to the “Main†DB containing all the respective tables (tblCustInfo,
tblOrderDetail, etc). But because we DO have repeat customers, we want to
minimize the duplicate data entry. Is there a way to have the Prospect form
draw current customer info from the “Main†tblCustInfo, but not add to
tblCustInfo when that customer is NOT found. So “Acme Lawn & Gardenâ€
information populates the prospect form when they order for a second time,
but “Acme Landscaping†is a new prospective customer. We want to type their
info into the Prospect form, but not add it to the “Main†tblCustInfo until
we know their money is good.

Is the only way to do this is to add all previous customers to the
underlying table of the “Prospect†form and them have it lookup to itself for
auto entry? How then do we handle when a customer moves and we have to change
their address? Would we have to maintain correct data in both tables, the
“Main†and the “Prospect�

Any suggestions?

 

[Jeff Boyce]

You did say "any suggestions"...<g>!

Don't! Don't create a second (essentially duplicate) table just to hold
"orders". This is how you'd probably have to do it if you were using a
spreadsheet, but Access is a relational database.

From your description, it sounds like the only difference between your
"prospect" and your "order" is the successful credit card charge. If that's
true, then add a field to your table to record the date/time the charge was
successful (NOTE: do not call it "Date" - that's a reserved word).

Your description sounds like folks may be working directly in the table.
Again, Access is not a spreadsheet on steroids. Yes, if you were using a
spreadsheet, you'd work "in the table". In Access, tables store data, but
you use forms and reports to display it.

So by working in forms, you get to create a query that shows all "prospects"
(in the query, exclude those records with [DateCreditCompleted] entries, and
another query that shows all "orders" (in this query, only include those
with the date/time entry). Use those two queries to feed your forms. And
if you want to get very clever, use a single form with a choice (option
group) for "Prospect"/"Order", and use the AfterUpdate event to change the
source of data for the form from one query to the other. Your concern about
"dirtying up" the table is moot.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I'm not new to Access, but I certainly don't know enough!

Let me assure you, we are working in forms, not in the tables. We even have
it split FE BE. The sales reps have very limited access (their one data entry
form).

I understand your suggestion about using different forms ... we do that now.
Our problem is the db has become huge and slow because we have thousands of
records we don't need in the admin side, but still need to keep. We have
setup most of our Admin forms so they are based upon a query(s) that produces
only "Customers" but then when a new one is added thoughtout the day it is
not included, so we need to refresh the query. The problem with that is
people forget to do that, so we need to automate the requery.

And I don't understand how your solution would allow for "lookup" to get
known customer data for repeat customers, if the query only gives prospects.

You did say "any suggestions"...<g>!

Don't! Don't create a second (essentially duplicate) table just to hold
"orders". This is how you'd probably have to do it if you were using a
spreadsheet, but Access is a relational database.

From your description, it sounds like the only difference between your
"prospect" and your "order" is the successful credit card charge. If that's
true, then add a field to your table to record the date/time the charge was
successful (NOTE: do not call it "Date" - that's a reserved word).

Your description sounds like folks may be working directly in the table.
Again, Access is not a spreadsheet on steroids. Yes, if you were using a
spreadsheet, you'd work "in the table". In Access, tables store data, but
you use forms and reports to display it.

So by working in forms, you get to create a query that shows all "prospects"
(in the query, exclude those records with [DateCreditCompleted] entries, and
another query that shows all "orders" (in this query, only include those
with the date/time entry). Use those two queries to feed your forms. And
if you want to get very clever, use a single form with a choice (option
group) for "Prospect"/"Order", and use the AfterUpdate event to change the
source of data for the form from one query to the other. Your concern about
"dirtying up" the table is moot.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is it possible to have a table as the lookup record source, but when the
record is not found, add a new record - but into a different table other
than
the record source?

For our company, an order is considered a "prospect," for lack of a better
term, until the credit card is charged successfully (not denied for any
reason); only then does it become an order. Because we get so many calls
looking for price, and so few actual orders, we don't want "dirty" our
"main"
database with prospect data until it is a real order. But we also want to
keep the prospect data in case they call back to place an order and for
marketing purposes. I've considered categorizing the type of record
(prospect, client) but that will give us 10s of thousands of records we
really don't need in our main file.

I have proposed to have a very similar set of tables that run in tandem.
Sales people use a "Prospect" form (built from multiple tables) to take a
tentative order, then when the credit card is successfully run, we append
that data to the "Main" DB containing all the respective tables
(tblCustInfo,
tblOrderDetail, etc). But because we DO have repeat customers, we want to
minimize the duplicate data entry. Is there a way to have the Prospect
form
draw current customer info from the "Main" tblCustInfo, but not add to
tblCustInfo when that customer is NOT found. So "Acme Lawn & Garden"
information populates the prospect form when they order for a second time,
but "Acme Landscaping" is a new prospective customer. We want to type
their
info into the Prospect form, but not add it to the "Main" tblCustInfo
until
we know their money is good.

Is the only way to do this is to add all previous customers to the
underlying table of the "Prospect" form and them have it lookup to itself
for
auto entry? How then do we handle when a customer moves and we have to
change
their address? Would we have to maintain correct data in both tables, the
"Main" and the "Prospect"?

Any suggestions?

 

[John W. Vinson]

I’ve considered categorizing the type of record
(prospect, client) but that will give us 10s of thousands of records we
really don’t need in our main file.

So?

A prospect is a person. So is a client. They share (I gather) most of the same
information, and a prospect can become a client.

It would be actually much simpler and more logical to use one table, with an
indexed Yes/No field [Client]. You could base your Client forms and reports on
a query selecting those records with this field being True; Prospect forms and
reports would select records where the field is False; some forms or reports
might just select all records. Tens of thousands of records is still quite
modest in size and your queries will be plenty fast, if designed correctly.

Having two tables for "the same kind" of data is generally not a good design,
and would not appear to be necessary.

 

[Guest]

I think I either asked the wrong question or muddled it. I guess it is really
the errant order data we don't need to see if the CC is denied. So I wanted
the Sales reps to be able to bring in the repeat client's info then add the
new prospective order info but not have false/denied CC orders in the admin
side.

I will try the suggestions so far.

I’ve considered categorizing the type of record
(prospect, client) but that will give us 10s of thousands of records we
really don’t need in our main file.

So?

A prospect is a person. So is a client. They share (I gather) most of the same
information, and a prospect can become a client.

It would be actually much simpler and more logical to use one table, with an
indexed Yes/No field [Client]. You could base your Client forms and reports on
a query selecting those records with this field being True; Prospect forms and
reports would select records where the field is False; some forms or reports
might just select all records. Tens of thousands of records is still quite
modest in size and your queries will be plenty fast, if designed correctly.

Having two tables for "the same kind" of data is generally not a good design,
and would not appear to be necessary.

 

[John W. Vinson]

I think I either asked the wrong question or muddled it. I guess it is really
the errant order data we don't need to see if the CC is denied. So I wanted
the Sales reps to be able to bring in the repeat client's info then add the
new prospective order info but not have false/denied CC orders in the admin
side.

That's precisely what my suggestion will accomplish - just use a Query to
select only undenied records.

 

[Fred]

I think that the respondents provided the best answer to your question as
written. However you alluded to one issue which you did not fully explain
which could change the answers. When most people doing this type of
application say that they don't want 10,000 "prospect" records piled in with
their "customer" records, their issue isn’t that the table is too big for
Access or that they don’t know that the records can be filtered based on
attributes. The underlying issue is often that there is a fundamental
difference between the data depending on where in the “sales funnel†it is
from. In this example, "Client" data comes from the narrower end of the
sales funnel. Being from there, it is data where it is both FEASIBLE and
NECESSARY and CUSTOMARY (customary = acceptable to the client/prospect to
gather) to obtain and record quite high grade, trustworthy, complete and
accurate data. "Prospect" data comes from a fatter of the funnel where
“feasible/necessary/customary†is often not the case. In those cases, e.g.
client and prospect data is fundamentally different (i.e. entered per
different data rules) despite superficial similarities.

There's no generalized answer for this. Sometimes it's metadata fields,
sometimes it's separate tables where what is ostensibly a "transfer" between
them is really a controlled conversion. But failure to deal with variations
in data quality one of the most common reasons for CRM systems often not
working very well.

Not sure whether or not this thought is applicable to your situation.

 

[Guest]

Yes, it does help. For us, there in not a fundamental difference in the data,
just the quantity/detail. To run a true order we need A LOT of data. To give
a price, we only need a few pieces. If we were to make all the required sales
fields "mandatory" on the prospect form, the reps would never be able to
quote.

Thanks to everyone for your input here.

CAC