Access 2003: Relating combo box, strings, & tables together

[compooper]

Hi there,

I have 5 tables, one for each company.
Each table holds invoice information for each company.
They are seperate because the invoices come in completely different formats depending on the company.

I would like to assign string values, such as "Company A" to the table corresponding to Company A.

That way when a user selects Company A from a combo box, the database knows that its supposed to use its corresponding table.

Any idea on how I am supposed to do that?

[orange]

Start with a data model. Get your "things" defined and set up in tables with relationships. You should NOT have a Table per company.

See this model for a sample.
http://www.databaseanswers.org/data_...ices/index.htm

See this link for info on Normalization and Entity Relationship Diagramming.
http://www.rogersaccesslibrary.com/forum/topic238.html

[compooper]

Thanks for getting back.

I'm currently going through the links you sent.

Just a quick question though:
Why shouldn't I keep the tables for each company separate?

Each company provides different information, uses different formats & different coding schemes for the services they provide.

I don't want to lose any information by merging the tables together.

[orange]

See if the Normalization and why gives you a better feel for a common Company Table.

Generally, and there may be a rationale for exceptions, you keep 1 subject per Table, and join the various tables.
Format is different than content, so how to present something is different that what it is you present.

Each company provides different information, uses different formats & different coding schemes for the services they provide.

What is the significance of this? Are you reacting to various clients or do you have a system and they are your clients? You have to determine what your application is; what feeds it; what outputs come from your system?

There must be something common -- that's a base table. If there are custom facts that could be in a separate table, but linked to the common table. Your going to have to explain your situation in more detail to get meaningful responses.

I don't think anyone here will tell you that a separate table per company is a design feature you should strive for. In fact, my guess is that other responders will suggest a Company table, and you'll have to determine what attributes of each Company are relevant to you and your business.

But let's see what others have to say.

[compooper]

Okay I think I'm convinced.

The only difficulty I was finding before when I tried merging the tables, was determing which company the record belongs to.
Is there a way for a column to be added that will identify which company provided the service?

I have an importing form, where the user imports the invoice on a routine basis. (used Ken Getz code)
However, I want them to describe the file through various combo boxes and option groups before its imported, which includes Company Name. (do not know how to code this)

I have also created a mapping that takes the various services provided by each company and maps it a common service name.
That way when I merge my tables I won't have different service codes/descriptions.

I will have to use the client's name as IDs in my merged table since the same policy# can belong to more than one person, depending on if they're in the same family.

[nicknameoscar]

This "other" person would say take a look at the Northwind sample database and see how it is laid out. One table for Company, one table for Products, one table for Employees, etc. I forget where you can find the Northwind database. Let us know if you have a problem finding it. I believe it is somewhere in C:\Program Files\Microsoft Office if you installed Office in the default location.

[orange]

Compooper,

Don't just start merging tables. Make a data model as suggested in earlier post.
Identify what will be in the Company Table.
If you need a field to show where/how this Company came into your system, you will have a spot for it. Such a field may be needed for historical reasons.

But get a structure sorted out before merging anything. If you have gone thru the Entity relationship Diagramming, you should have a "general feel" for what your Company Table will store. I'd be interested in the other tables in your database also.

Good luck.

[compooper]

Thanks for all your help.

I have been going through Entity-Relationship Diagramming & the other links you provided, and have come up with the following:

Clients
First Name*
Last Name*
Policy Number
Date of Birth*

Services

Service Code*
Service Description*
Price
Quantity

Orders

Date Ordered
Dare Completed
Company
Invoice Period


Where the * represent the Unique indexes & I have used a primaryID in each table.

My relationships are the following:
Clients
Many to Many
Services


Clients
Many to Many
Orders


Services
Many to Many
Orders


I have another question though, maybe you can redirect me to some helpful websites:

• how do i get my database to extract this information from the various tables automatically?
• for the orders table, should I have an unique index there too?

[orange]

Glad you got something started. Normalization is a key concept. Just a word of caution - when you are using Access it is best not to have spaces or special characters in field and object names.
see http://allenbrowne.com/AppIssueBadWord.html

Keep reading! You're going to have to resolve Many to Many. Be on the watch for Junction Tables.
You will be able to get info out of your database (multiple tables even) by using queries. Every table should have a Primary Key.
There are a lot of stories about Primary Keys. Here's an example
http://www.techrepublic.com/article/...debate/1045050

[compooper]

so here's the idea:
- user imports invoice, indicates which company
- invoice merges to its company table
- important data is extracted from the company tables and merged together (using queries)
- all queries, forms and reports use data from the table above (not the separate company tables)

Is this an efficient process?

[orange]

Where's the importing coming from? Describe the whole process so others who read this can get familiar and offer advice.

Did you see the sample data model at
http://www.databaseanswers.org/data_...ices/index.htm

It's probably more than you have or need but shows how the "things" are related.

Shouldn't an Invoice follow and Order? You Order something and then you get Invoiced.

Do you deal with Products or only Services?

[compooper]

Okay Orange.

I'm going to start a new thread, since my question is getting way off topic from my original post.

Thanks for all your help again!

[compooper]

https://www.accessforums.net/databas...ess-14306.html