transfer data from one form to a new form

[Brian]

I have a form where I enter prospective sales leads into a leads table. Some
of these leads may result in a new sale. I have forms to enter data into the
respective tables seperately.

frmLeads
------------
leadID
nameLast
nameFirst
address
city
state
zip
phone


frmNewSales
-------------------
custID
nameLast
nameFirst
address
city
state
zip
phone
saleDate
invoice#
ProductInfo .............

I would like to be able to view the record of a lead and, with the click of
a button export that data that is common to both forms into the New Sale form
so I can add the additional information.
The leads table and customers tables are seperate tables
The frmNewSales is also used to add new sales not generated from leads

I'm sure this is possible or is there a better way.

 

[Jeff Boyce]

Brian

I'm sure that the way you've come up with would work ... if you were using
a spreadsheet. Since Access is a relational database, you definitely can
make better use of its features and functions.

First, you don't need to re-(dundantly) capture or save the name, address,
etc. information. Instead, if you use a single table to capture
person-related info, and use an ID field (if you don't have a good "natural"
key, you could use an Autonumber primary key).

To capture the data about your Sales, you'd only need to store the ID from
the person table. Use a query to join the ID in the Sales table back to
find the address, name, etc. of the person.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian]

So in other words, I should modify my customers table to include some
indicator whether they are a customer or a lead and store all lead
information in the same table as the customer data, then change that
indicator to reflect the change in status from lead to customer. I have
several queries and forms that are based on a customer ID# assuming that a
custID has an associated sales invoice, so I would also need to add this
indicator field to the numerous queries that rely on a customer ID# ..... I
guess it's not that difficult to do ......... hmmmmmm I will ponder this.

Thanks!

 

[Jeff Boyce]

Brian

I may not have done a sufficient job of explaining...

You do NOT need to add information about what role a Person is playing into
the Person table. In a relational database, a Person table ONLY stores
information about persons (e.g., firstname, DOB, ...).

I believe I suggested that you create another table (?Sales) in which you
include a field for the PersonID (from the Person table). THIS is how you'd
know who was a Sales or not.

And if you are going to have multiple categories in addition to "lead" and
"sale", you'll probably want to add a table for looking up this "status"
information, and not use a "Sales" table, but rather, a PersonRole table
that captures a PersonID and a RoleID. This resolves the many-to-many
relationship you'd have between persons and the roles they might play.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian]

I see ... something like

tblcustomer (existing table)
-------------
custID
nameFirst
nameLast
address .......
-----------------------------------------------------

tblsales
------------
custID
invoice#
....

-------------------------------------------------------
So, if everybody (leads and customers) are input into the tblCustomers (the
existing person table) how would I track those leads that eventually become
sales

Example:
I go to a trade show, get 160 leads, input these names and data into the
tblCustomers table - From these leads, 15 leads purchase a product. Another
trade show, 100 leads and 30 sales. How would I be able to breakdown the
sales response each trade show generated. Would I need to create a lead
source (trade show) table - with the appropriate key - and use a query to
determine these results.

tblLeadSource
----------------
sourceID
custID
......

then run queries to compare custID between tblLeadSource and tblSales

 

[Jeff Boyce]

Sounds quite workable!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian]

Thanks - it looks like I got tripped up by that normalization stuff again
appreciate you pointing back on track