Option to Support multiple companies

[jsccorps]

Existing DB has lots of Queries (30+) and Forms based on a single company.
Now, need to add another company. The output reports for the new company
would be the same as the existing company. Would like to avoid having to
modify all of the Querys/Forms to support both the existing and new company.
For example, there is single table that contains Company Data - field is
callled CompanyData. Could I create a table called Company1 (existing
company) and a table called Company2 (new company), and somehow have the
Queries/Forms use the correct table? With minimal mods to the queries/forms.

 

[John W. Vinson]

Existing DB has lots of Queries (30+) and Forms based on a single company.
Now, need to add another company. The output reports for the new company
would be the same as the existing company. Would like to avoid having to
modify all of the Querys/Forms to support both the existing and new company.
For example, there is single table that contains Company Data - field is
callled CompanyData. Could I create a table called Company1 (existing
company) and a table called Company2 (new company), and somehow have the
Queries/Forms use the correct table? With minimal mods to the queries/forms.

That would not be the way to do this. Instead you would add a CompanyID or
CompanyName field to some table (since you chose not to post any description
of your table structure nobody here can tell you which table); you would then
simply add a criterion to this field to select which company you want.

"Fields are expensive... records are cheap".

 

[David H]

When you say that you need to support multiple companies, what exactly are
you trying to accomplish?

 

[jsccorps]

Existing company (Company1) is selling magazines. The magazine prices are
specific to this company. There is a table that include the magazine prices
for Company1. The magazine prices for the new company (Company2) will be
different. When I run sales reports, I will need to distingush between the
two companies. Since 95% of the reports will be the same format (only prices
will vary), I am trying to minimize the mods to the existing queries/reports
needed to support Company2.

 

[John W. Vinson]

Existing company (Company1) is selling magazines. The magazine prices are
specific to this company. There is a table that include the magazine prices
for Company1. The magazine prices for the new company (Company2) will be
different. When I run sales reports, I will need to distingush between the
two companies. Since 95% of the reports will be the same format (only prices
will vary), I am trying to minimize the mods to the existing queries/reports
needed to support Company2.

That's a helpful *business level* description of the problem.

If we're going to be able to help you, we'll need a *table level* description
of your database.

We can't see it from here, so we cannot tell you how to modify your table
design.

 

[jsccorps]

Thanks for your patience. Following are key fields in 3 tables.

CUSTOMER DATA
Receipt#
CustomerName
Sales Date

MAGAZINE ORDER INFO
Receipt#
Mag#
NewOrder
Renewal

MAGAZINE DATA
Mag#
MagPrice

 

[John W. Vinson]

Thanks for your patience. Following are key fields in 3 tables.

CUSTOMER DATA
Receipt#
CustomerName
Sales Date

MAGAZINE ORDER INFO
Receipt#
Mag#
NewOrder
Renewal

MAGAZINE DATA
Mag#
MagPrice

Well, you'll need a Company table with a CompanyID primary key; and it looks
like your Magazine Data and Magazine Order Info tables will need a CompanyID
to indicate which company is involved in the sale.

I don't understand your CUSTOMER DATA table though. A customer doesn't have a
sales date, or a receipt number! Surely you hope for repeat business? Wouldn't
those fields go into the MAGAZINE ORDER INFO table? I'd expect the CUSTOMER
DATA info to have a CustomerID primary key, and information like the
customer's FirstName, LastName, Address (maybe a separate Addresses table with
records for billing address and mailing address if they're different); the
OrderInfo table would have a CustomerID, OrderDate, ReceiptNo (don't use # in
fieldnames, it's a date delimiter) and would be related one to many to the
customer info table.