Seperate Customer Tables ? (Wholesale - Retail - Other)

[Will]

For an orders database...

Should I have a seperate table for each customer type...

tblCustomersWholesale
tblCustomersRetail
tblCustomersOther

Why do this?

You may want different fields for your wholesale customers than you do for
your retail customers.

Many retail customers are one time transactions and the retail customer
table may get very big and thus be difficult to search.

Wholesale customers on the other hand are generaly repeat customers and you
may want to keep different data for them.

the disadvantage may be in creating some types of customer reports... since
each type customer table may have different fields... creating reports may
or may not become more difficult.

What do you guys think and do?

Thanks for any help.

 

[Amy Blankenship]

You should have a tblCustomers with a CustomerType field. If you have data
you just use for one type of customer, you can choose to still store that
information in the tblCustomers table and not fill it for customers where it
doesn't apply or, if it is a lot of data, you might choose to split that out
into another table with a 1:1 relationship to the customer in tblCustomers.

You're unlikely to have problems with the table getting so big it is
difficult to search, and if that is a risk (millions of records) you should
be looking for a higher performance database than Access, such as SQL Server
or Firebird.

HTH;

Amy

 

[Guest]

I agree with Amy, Also you can run a delete query that can delete all
customers that match "Ratail" and whose last order date is more that one year
old.

 

[Guest]

Amy - is there an SQL discussion group like this on here, I looked to the
left and didn't see one?

 

[Lynn Trapp]

Larry,
There are quite a few SQL Server newsgroups available. They will be named
like this one:

microsoft.public.sqlserver.clients

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Amy Blankenship]

If you're looking for one on queries, try microsoft.public.access.queries

HTH;

Amy