Customer, Prospect, Service Table design

W

Wayne Minor

Thanks in advance for any suggestions
I am building a database to track, contact, report sold customers, prospects
that walk in, prospects that call in, customers and prospects that come in
from service and prospects that email us. I need to be able to cross
reference this information and determine when and if a customer has
purchased from us before and how many times. If a customer contacts us I
need to be able to determine if they have PURCHASED in the past by name,
phone number, and/or address, OR determine if they have CONTACTED us before
by the same information. we had a commercial solution but I dropped it
because it costs $500 a month, I can't query it like I want to, and I just
plain didn't like it because it was so limited. I have no problem importing
the info from the MANY sources the data comes from (4tops excel import). I
have tried to normalize the information but I think I didn't do it right. As
one table the database has over 130 fields. when i tried to normalize the
table I split it into 16 tables, but now i think i need to split it into
more. Below is the documenter info for each type of the table i have. I'm
sure i need to update the field sizes and such. Again thanks in advance for
any suggestions.

tblCustomer is the main table.

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblCallBright Page: 1
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 574 Updatable: True
Columns
Name Type Size
TOLL FREE NUMBER Text 50
TOLL FREE NAME Text 50
DURATION Text 50
CALLSTART Text 20
VANITYNUMBER Text 50
APPOINTMENT Text 50
CALLED Text 50
SURVEY1 Text 50
SURVEY2 Text 50
SURVEY3 Text 50
SURVEY4 Text 50
REVIEWED Text 50
ARCHIVED Text 50
ORGANIZATION Text 50
AUDIO Text 50
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblComments Page: 2
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 21903 Updatable: True
Columns
Name Type Size
COMMENT Text 255
NOTE Text 255
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblCustomer Page: 3
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 21851 Updatable: True
Columns
Name Type Size
ID Long Integer 4
BUYER-NAME Text 255
FULL NAME Text 255
FIRST NAME Text 50
MIDDLE NAME Text 50
LAST NAME Text 50
PHONE Text 255
BUS-PHONE Text 255
E-MAIL ADDRESS 1 Text 255
E-MAIL DESCRIPTION 1 Text 255
SOLD Text 50
DO NOT CALL LIST Text 50
FI-WIP Double 8
tblService_ID Long Integer 4
tblRCL_ID Long Integer 4
tblMfgLeadInfo_ID Long Integer 4
tblMfgLeadFinancial_ID Long Integer 4
tblCallBright_ID Long Integer 4
tblDMSProspect_ID Long Integer 4
tblDlrFinance_ID Long Integer 4
tblFinance_ID Long Integer 4
tblTrade_ID Long Integer 4
tblVehicle_ID Long Integer 4
tblEmployee_ID Long Integer 4
tblComments_ID Long Integer 4
tblCustomerAddress_ID Long Integer 4
tblCustomerInfo_ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblCustomerAddress Page: 4
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 18569 Updatable: True
Columns
Name Type Size
STREET Text 255
ADDRESS2 Text 50
CITY Text 255
ST Text 255
ZIP Text 255
COUNTY Text 255
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblCustomerInfo Page: 5
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 9163 Updatable: True
Columns
Name Type Size
BIRTH Text 50
COBUYER BIRTHDATE Text 255
SSN-NUMBER Text 50
DEAL-NO Double 8
ENTRY Text 50
STATUS Text 255
CALLID Text 50
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblDlrFinance Page: 6
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 7424 Updatable: True
Columns
Name Type Size
APR Double 8
CASH-DOWN Currency 8
TOTAL COMM/DEALER Currency 8
FINANCE-TOTAL Currency 8
DLR FINANCE RESERVE Currency 8
MBI/DEALER COMM Currency 8
REBATE AMOUNT Currency 8
VEHICLE $ WE OWE SALE TOTAL Currency 8
COMM ON SALE/DEALER Currency 8
CR-LIFE COMM/DEALER Currency 8
INS 1/DEALER COMM Currency 8
INS 2/DEALER COMM Currency 8
INS 3/DEALER COMM Currency 8
INSURANCE COMM/DLR Currency 8
A&H INS/DEALER COMM Currency 8
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblDMSProspect Page: 7
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 5175 Updatable: True
Columns
Name Type Size
FIRST CONTACT Date/Time 8
STATUS CODE Text 50
PROSPECT NUMBER Text 50
EVENT CODE Text 50
ADVERTISING CODE Text 50
QUOTE NUMBER Text 50
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblEmployee Page: 8
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 18264 Updatable: True
Columns
Name Type Size
SALESMAN Text 255
SALESMAN2 Text 255
SALESMANAGER Text 50
USERNAME Text 50
MANAGER Text 50
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblEvents Page: 9
Properties
DateCreated: 2/19/2004 2:31:08 AM GUID: {guid {8B790ED4-AB3B-4E7F-
8FAB-D37F9FE3C024}}
LastUpdated: 2/28/2004 2:36:41 PM RecordCount: 0
Updatable: True
Columns
Name Type Size
EventID Long Integer 4
EventName Text 50
Status Text 20
StartDate Date/Time 8
EndDate Date/Time 8
EventDescription Memo -
Notes Memo -
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblFinance Page: 10
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 7627 Updatable: True
Columns
Name Type Size
FINANCE-CO Text 255
FINANCE-CO NAME Text 255
FI-MGR Text 255
PAYMENT Currency 8
TERM Double 8
NUMBER OF PAYMENTS Double 8
VEHICLE - PRICE1 Currency 8
WE OWE COST BACK Currency 8
WE OWE COST Currency 8
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblMfgLeadFinancial Page: 11
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 203 Updatable: True
Columns
Name Type Size
CAMPAIGN TRACKING CODE Text 50
CONSUMER CM KEY Text 50
SALUTATION Text 50
SUFFIX Text 50
PHONE CALL-THRU Text 50
ORGANIZATION NAME Text 50
EDW ACCOUNT NUMBER Text 50
FCCUSTOMER OLD ACCOUNT NUMBER Text 50
LAST PAY DATE Text 50
PAYMENTS MADE COUNTER Text 50
OUTSTANDING BALANCE Text 50
PAYMENTS REMAINING Text 50
PAYMENTS SCHEDULED AMOUNT Text 50
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblMfgLeadInfo Page: 12
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 203 Updatable: True
Columns
Name Type Size
FMC BRANCH Text 50
FCDEALER PREFIX NUMBER TX Text 50
FCDLR NUMBER Text 50
PRE-APPROVED AMOUNT Text 50
OFFER EXPIRATION DATE Text 50
EQUITY AMOUNT Currency 8
RATE-APR Text 50
CONTRACT DATE Text 50
SALES PRICE Currency 8
OFFER DESCRIPTION Text 50
NUMBER VEHICLES HOUSEHOLD Long Integer 4
COUPON Text 50
FLEET Text 50
DISTANCE Text 50
CUSTOMER ID Text 50
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblRCL Page: 13
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 203 Updatable: True
Columns
Name Type Size
BODY STYLE Text 50
TRIM Text 50
A-PLAN Text 50
APP Text 50
SCHEDULED TERM DATE Text 50
NET CAP COST Currency 8
DOWN PMT Currency 8
LEV Currency 8
SECURITY DEPOSIT Currency 8
PREVIOUS RENEWAL Text 50
NUMBER OF TIMES Long Integer 4
WEARCARE Text 50
EARLY BIRD VINCENT NUMBER Text 50
EARLY BIRD EXPIRATION Text 50
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblService Page: 14
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 8527 Updatable: True
Columns
Name Type Size
VEH LAST SVC DATE Text 50
SVC PAY AMOUNT Currency 8
SVC PAY DATE Text 50
LAST SVC DATE Text 50
DMS CUST NUMBER Text 50
LTDATE Text 50
MILEAGE Long Integer 4
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblTrade Page: 15
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 5138 Updatable: True
Columns
Name Type Size
TRADE1 MAKE Text 255
TRADE1 MODEL TYPE Text 255
TRADE1 SERIAL NO Text 255
TRADE1 STOCK NO Text 255
TRADE1 GROSS VALUE Currency 8
ACV AMOUNT Currency 8
TRADE1 ACV Currency 8
TRADE2 GROSS VALUE Currency 8
TRADE2 ACV Currency 8
TRADE2 MAKE Text 255
ID Long Integer 4

C:\My Documents\BDCManager\BDC.mdb Wednesday, March 03, 2004
Table: tblVehicle Page: 16
Properties
DateCreated: 2/28/2004 12:56:03 AM LastUpdated: 2/28/2004 12:56:03 AM
RecordCount: 20468 Updatable: True
Columns
Name Type Size
NEW/USED Text 255
STOCK-NO Text 255
VIN Text 255
YEAR Double 8
MAKE Text 255
MODEL Text 255
MODEL-TYPE Text 255
COLOR Text 255
OPTIONS Text 255
ID Long Integer 4

Below is all the information AS ONE table

C:\My Documents\BDCManager\M_BDCtable.mdb Wednesday, March 03, 2004
Table: M_BDC Page: 1
Properties
DateCreated: 2/28/2004 2:06:16 PM LastUpdated: 2/28/2004 2:06:20 PM
RecordCount: 21852 Updatable: True
Columns
Name Type Size
ID Long Integer 4
COMMENT Text 255
BUYER-NAME Text 255
FULL NAME Text 255
FIRST NAME Text 50
MIDDLE NAME Text 50
LAST NAME Text 50
STREET Text 255
ADDRESS2 Text 50
CITY Text 255
ST Text 255
ZIP Text 255
PHONE Text 255
BUS-PHONE Text 255
E-MAIL ADDRESS 1 Text 255
E-MAIL DESCRIPTION 1 Text 255
BIRTH Text 50
COBUYER BIRTHDATE Text 255
COUNTY Text 255
SOLD Text 50
NEW/USED Text 255
STOCK-NO Text 255
VIN Text 255
YEAR Double 8
MAKE Text 255
MODEL Text 255
MODEL-TYPE Text 255
COLOR Text 255
SALESMAN Text 255
SALESMAN2 Text 255
NOTE Text 255
DO NOT CALL LIST Text 50
SSN-NUMBER Text 50
TRADE1 MAKE Text 255
TRADE1 MODEL TYPE Text 255
TRADE1 SERIAL NO Text 255
TRADE1 STOCK NO Text 255
TRADE1 GROSS VALUE Currency 8
ACV AMOUNT Currency 8
TRADE1 ACV Currency 8
TRADE2 GROSS VALUE Currency 8
TRADE2 ACV Currency 8
TRADE2 MAKE Text 255
FI-WIP Double 8
DEAL-NO Double 8
ENTRY Text 50
FINANCE-CO Text 255
FINANCE-CO NAME Text 255
FI-MGR Text 255
PAYMENT Currency 8
TERM Double 8
NUMBER OF PAYMENTS Double 8
VEHICLE - PRICE1 Currency 8
APR Double 8
CASH-DOWN Currency 8
TOTAL COMM/DEALER Currency 8
FINANCE-TOTAL Currency 8
DLR FINANCE RESERVE Currency 8
MBI/DEALER COMM Currency 8
REBATE AMOUNT Currency 8
VEHICLE $ WE OWE SALE TOTAL Currency 8
COMM ON SALE/DEALER Currency 8
CR-LIFE COMM/DEALER Currency 8
INS 1/DEALER COMM Currency 8
INS 2/DEALER COMM Currency 8
INS 3/DEALER COMM Currency 8
INSURANCE COMM/DLR Currency 8
WE OWE COST BACK Currency 8
OPTIONS Text 255
STATUS Text 255
WE OWE COST Currency 8
A&H INS/DEALER COMM Currency 8
FIRST CONTACT Date/Time 8
STATUS CODE Text 50
PROSPECT NUMBER Text 50
EVENT CODE Text 50
SALESMANAGER Text 50
ADVERTISING CODE Text 50
QUOTE NUMBER Text 50
TOLL FREE NUMBER Text 50
TOLL FREE NAME Text 50
DURATION Text 50
CALLSTART Text 20
VANITYNUMBER Text 50
APPOINTMENT Text 50
CALLED Text 50
USERNAME Text 50
SURVEY1 Text 50
SURVEY2 Text 50
SURVEY3 Text 50
SURVEY4 Text 50
REVIEWED Text 50
ARCHIVED Text 50
MANAGER Text 50
ORGANIZATION Text 50
AUDIO Text 50
CALLID Text 50
CAMPAIGN TRACKING CODE Text 50
CONSUMER CM KEY Text 50
SALUTATION Text 50
SUFFIX Text 50
PHONE CALL-THRU Text 50
ORGANIZATION NAME Text 50
EDW ACCOUNT NUMBER Text 50
FCCUSTOMER OLD ACCOUNT NUMBER Text 50
LAST PAY DATE Text 50
PAYMENTS MADE COUNTER Text 50
OUTSTANDING BALANCE Text 50
PAYMENTS REMAINING Text 50
PAYMENTS SCHEDULED AMOUNT Text 50
FMC BRANCH Text 50
FCDEALER PREFIX NUMBER TX Text 50
FCDLR NUMBER Text 50
PRE-APPROVED AMOUNT Text 50
OFFER EXPIRATION DATE Text 50
EQUITY AMOUNT Currency 8
RATE-APR Text 50
CONTRACT DATE Text 50
SALES PRICE Currency 8
OFFER DESCRIPTION Text 50
NUMBER VEHICLES HOUSEHOLD Long Integer 4
COUPON Text 50
FLEET Text 50
DISTANCE Text 50
CUSTOMER ID Text 50
BODY STYLE Text 50
TRIM Text 50
A-PLAN Text 50
APP Text 50
SCHEDULED TERM DATE Text 50
NET CAP COST Currency 8
DOWN PMT Currency 8
LEV Currency 8
SECURITY DEPOSIT Currency 8
PREVIOUS RENEWAL Text 50
NUMBER OF TIMES Long Integer 4
WEARCARE Text 50
EARLY BIRD VINCENT NUMBER Text 50
EARLY BIRD EXPIRATION Text 50
VEH LAST SVC DATE Text 50
SVC PAY AMOUNT Currency 8
SVC PAY DATE Text 50
LAST SVC DATE Text 50
DMS CUST NUMBER Text 50
LTDATE Text 50
MILEAGE Long Integer 4

Thanks again
 
J

Jeff Boyce

Wayne

Just as you've found the size of your task somewhat daunting, it's a bit
tough to get a handle on where to start, given the size of your post.

Is there a chance you can focus in on a single aspect and re-post?

I would agree that the place to start is in normalizing your data structure.
And I agree that 100+ fields is too many for a single table!

Have you stepped away from the computer and used paper/pencil to draw out
your entities and relationships? This is a crucial first step. After that,
I'd look to find homes for all the attributes. Finally, I'd ask myself
about what queries/reports I wanted, and make sure that the required
underlying data was in my earlier work.

THEN, I'd start building tables <g>!
 
W

Wayne Minor

Thanks very much. I guess the main question is since i have so much
information coming from so many sources, and the only "common" info is name,
address, and/or phone number and maybe the vehicle number, how/what should i
do with all of the other fields from each source. I am reading "using access
2003" and "access inside and out" but i'm missing it somewhere. Thanks again
for your help.
 
W

Wayne Minor

Thanks very much for you advice. I'll get to reading right away...

--
Wayne Minor
Roger Carlson said:
I suggest you get a copy of Michael Hernandez's: Database Design for Mere
Mortals. When you've read that, I have some tutorials on my website that
help illustrate the process he gives. You can find them here:
http://www.rogersaccesslibrary.com/TutorialsDesign.html

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Wayne Minor said:
Thanks in advance for any suggestions
I am building a database to track, contact, report sold customers, prospects
that walk in, prospects that call in, customers and prospects that come in
from service and prospects that email us. I need to be able to cross
reference this information and determine when and if a customer has
purchased from us before and how many times. If a customer contacts us I
need to be able to determine if they have PURCHASED in the past by name,
phone number, and/or address, OR determine if they have CONTACTED us before
by the same information. we had a commercial solution but I dropped it
because it costs $500 a month, I can't query it like I want to, and I just
plain didn't like it because it was so limited. I have no problem importing
the info from the MANY sources the data comes from (4tops excel import). I
have tried to normalize the information but I think I didn't do it
Click to expand...
right.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table Design Review of Fields and Tables 9
Need Feedback on table setup 1
Tasks, Assignments and Projects Database Structure. 2
WCG Stats Wednesday 03 May 2023 2
table columns don't show when table opened or in query output disp 1
WCG Stats Thursday 11 May 2023 3
WCG Stats Friday 12 May 2023 3
WCG Stats Friday 21 April 2023 2

Top