Working with mutlitple tables

[Summer]

Hello. I am using Access to manage many details about my customers--too many to fit into one table. I've divided the data into 8 tables. I think I understand from the previous comments, but I want to confirm:

• I've used the customer file number as the primary key in all 8 tables, changing the name for each table, but the number is identical (customerID, financialID, marketing ID...)
• It sounds like I should use an auto ID as the primary key for tables 2-7, and the customerID should be connected between all 8 tables as a one-to-one relationship--is that right?
• My goal is to add a customer record (in the Customer table) that also adds a corresponding record in each of the other 7 tables (without having to manually add the record to each table)
• A secondary concern is that I'd like to move the customer records to another database when they are complete. I've set up 8 append queries to do this for each record. However, I'd like to:
  
  • use one append query to copy all 8 records from database A to database B at one time
  • delete the record from all 8 tables in database A at one time

More details:

I am managing customers relocating from one work location to a new one. I track allowances being paid, real estate marketing for home sales, real estate disclosures, inspections, appraisals, home purchases in the destination. Ideally, I could add all fields to a single table, but there are too many. So I've divided them into logical categories (see below). I've set them up as one-to-one relationship using a customer ID in all 8 tables. I think the number of tables could be reduced, but I would still need to know the best way to connect them so I could manage adding new records and moving old records to a separate database.

Tables:

1. Customer - basic contact information, move type, etc.
2. Financial - payment of allowances and equity
3. Marketing - market reports, offers, internal audits
4. Disclosures - required disclosures to collect from customers
5. Inspections - home inspections
6. Appraisals - home appraisals
7. Destination - new home purchase details
8. Notes

Hopefully my database design is salvagable--I've spent many hours on it. Any advice to make it work more efficiently will be greatly appreciated!

--Summer

[June7]

So a customer can have only 1 inspection, 1 appraisal, 1 destination, etc? No repeat customers?

You would have a one-to-one relationship if customer file number is set as primary key in all 8 tables. If you use an autonumber PK and save it into related tables then there is no need for customer file number in related tables. It only belongs in main table.

Why do you need to copy data to another database?

[CJ_London]

My goal is to add a customer record (in the Customer table) that also adds a corresponding record in each of the other 7 tables (without having to manually add the record to each table)

what is the benefit of adding a record in the other tables automatically? Often you will get a 'brief' for the customer, the other data follows over a period of time

You seem to want a 1 to 1 relationship but a number of your table descriptions implies multiple records (i.e. one to many) e.g.

payment of allowances and equity
market reports, offers, internal audits

Also some of these would appear to contain unrelated data so would require separate tables - how does a market report related to an offer?

It sounds like you want to treat this like excel - all data on one line. Database forms don't work that way. you have a main form for the customer then separate subforms for each of the 'child' tables. Basis rule is one form one table.

Suspect rather than looking to reduce the number of tables, you need more - perhaps 15 or 20.

[mike60smart]

Hi

Would it be possible for you to upload a zipped copy of the database?

[Summer]

Hi, all--thanks for your responses.

I've decided to go a different direction. Based on others' comments, my data is better suited to an Excel workbook than to Access. I can use the Excel "form" function for data entry (one of the main reasons I was using Access). It is limited to 32 columns, so I still have to break up my tables, which I've set up as worksheets in the workbook, with field references to the CustomerID to tie the records together.

This will do what I need it to do, and will be less cumbersome to tweak (adding/changing field names, etc).

[mike60smart]

Hi Summer

I believe you are going the easy route by reverting to Excel.

In Excel as you have just mentioned you would need to tweak it by adding Columns

From your description of your Customer process you are going to need Multiple Related Tables which is what Access is designed to manage.

If you can upload a zipped copy of your database with no confidential data I am sure we would be able to help you.

[Summer]

RelocationDatabase.zip

I've attached the database minus customer records. Thanks for taking a look at it.

Hi Summer

I believe you are going the easy route by reverting to Excel.

In Excel as you have just mentioned you would need to tweak it by adding Columns

From your description of your Customer process you are going to need Multiple Related Tables which is what Access is designed to manage.

If you can upload a zipped copy of your database with no confidential data I am sure we would be able to help you.

[mike60smart]

Hi Summer

We initially need to look at your Customer Table which needs to be normalised.

I attach your revised database with what I believe to be the Customer Table normalised. This is only my guess at the process as I do not fully understand your business rules.

The Form that opens at Startup shows a Main Form / Subform layout with the Subform on a Tab Page.

You have a Combobox in the Customer Header which allows you to search for a Customer.

The Location History Form displays as a Single Form which shows details of Location with a Start Date.

If a Customer is due to move then you just need to enter a Date To to complete that locations record.

Click add New Location and enter details of New Location with a Start Date.

Come back with any questions.

[ssanfu]

You should not begin an object names with a number. The Access Gnomes can get persnickety and cause problems that are hard to find.