Thanks for the input, here is more of the total picture.
See attached spreadsheet: (Couldnt attach to this site or
send directly to you.) I am obviously inexperienced with
Access, so thanks in advance for you patience. I sent the
email direct due to attachment. So I shouldn't have auto
numbers as primary key for client ID my thought was it
would generate on table1 then go to others but that is
wrong. After seeing the whole picture what change would
you recommend.
If you would like to EMail me, bear in mind that I'm a self-employed
consultant, volunteering my time on the newsgroup as a public service.
Private EMail support is therefore reserved for paying clients; I'd be
willing to send you a copy of my terms if you're interested. File
attachments are NOT welcome in this text-based newsgroup, so it's just
as well you didn't post it - nobody would download it anyway.
Comments about table structure inline. I'd suggest that you need to
get a good book on database structure and normalization; this
structure has quite a few problems!
Contact Manager1
Client Number AutoNumber
Salutation Drop Down Text - Mr., Mrs., Ms., Mr. & Mrs.
ClientFirstName Text
ClientMI Text
ClientLastName Text
SpouseFirstName Text
SpouseMI Text
SpouseLastName Text
You might want to consider a "people" table; having separate fields
for client and spouse can be a problem (leaving NULL data for single
clients, not to mention "families" which might have three or more
people with whom you will need to deal.
Address Text
City Text
State Text
ZipCode Text
ClientCompany Text
ClientTitle Text
SpouseCompany Text
SpouseTitle Text
HomePhone Text
WorkPhone Text
CellPhone Text
Fax Text
A Phones table related one-to-many to the Client table is probably
better here.
Email Text
ClientType Drop Down Text - Client, Referal,
Prospect, Lead
Marketing & Contact Manager2
Is this a field, or a table name?
ClientType Drop Down Text - Client, Referal,
Prospect, Lead
Is this redundant with the ClientType field in the table above? How is
this table related to the Client table?
LastDateContacted Date/Time
ContactLog Memo
If you're keeping a log of contacts you would be VASTLY better off to
keep it as a true log table:
ContactLog
ClientID Long Integer <link to Clients>
ContactDateTime Date/Time <these two fields are the Primary Key>
PersonContacted <link to an Employees table>
Subject Text
Notes Memo
This would have one record for each contact; the last date contacted
and next scheduled contact should be calculated on the fly, not
stored.
NextScheduledContact Date/Time
MarketingCampaign Text
ClientSource Relationship,Mailing,Ad,Calling
Other Client Information3
Client Number AutoNumber
ClientDOB Date/Time
ClientSoc# Text
SpouseDOB Date/Time
SpouseSoc# Text
Child1 Text
Child1DOB Date/Time
Child1Soc# Text
Child2 Text
Child2DOB Date/Time
Child2Soc# Text
Child3 Text
Child3DOB Date/Time
Child3Soc# Text
General Memo
Again... put the DOB, SOC# and other client information in the Client
table or the suggested People table; children are people too and
should also be in the People table, one record per child, with a Long
Integer ClientID linking to the Client table.
Asset Update4
Client Number AutoNumber
Account#1 Text
AccountType Text
InvestmentDescription Text
AssetSize Currency
SystematicInvestments Currency
TrailerBased Yes/No
ShareClass Drop Down A,B,C,T or FeeBased
AGAIN... one client, many accounts, using the ClientID as the linking
field in a one to many relationship.
You're using a relational database, not a spreadsheet! USE IT
RELATIONALLY!
