Access Database Design Problem. (1 Viewer)

Hello, I must say that I am not very experienced with Access.. hence why I most likely have ran into this problem. However I did try to read manuals and tutorials to get me through it. (Failed ) Hopefully somebody from these forums can help.

The database that I am trying to create is for a Door-to-Door marketing company. They have many employees(Reps) that are organized in teams(With team leaders). Moreover they sell more than one product. (SKY Television, 3G Contract Phones, 02 Contract Phones, BT Lines etc...) Hence what I tried doing is:

1. Create a common customer database M_Customers (At first I tried to what I think is "normalization", so I had C_Address and C_Bank_Details) but that lead me to some problems. This M_Customers table has all the needed information for all the products. (However some information is not needed for some of the products)

2. Create a common Employees database M_Employees, which lists all employees and their details. (Including Address and Bank Details) Moreover I have created a M_Commission database, to list all the levels of commission for different type of employees (on trial, mid level, top) and for all the different products.

3. Then I went ahead and created Quaries for Q_Phone_Deals and Q_Sky_Deals... but they did not input data into the database. mad: "The same record is needed in M_Customers" ) They linked a M_Customers with C_Phone_Deals and respectively C_Sky_Deals (Those two tables contained information about the phone deal or the sky deal... like "Network" for and "handset type" in C_Phone_Deals and "Install Date" and "Installed - Y/N" in C_Sky_Deals. ) These quaries (I thought) would be the main inputting devices, as they would also have a Employee_ID lookup in order to see who did the sale and then somehow calculate the commision that he or she deserves.

PROBLEM: As you can see I have made something horribly wrong with my relationships and probobly the whole design. As I cant get the common customer database to work, and link it to C_Phone_Deals and C_Sky_Deals and later to more products. Moreover I am not sure how to make the commission system work.

Please help me out, I will be more than grateful!

Best Regards

Hi
Yes you have a fair bit to do and I probably wont get it all (almost certainly wont get it all!!) in one go.

First thoughts are table for Teams (ie team 1, team 2) then a table for employees, then a table for products (ie Sky, BT), then a table (one table) for customers. Also your table for commissions.

Link each employee to a team by having a teamID field in the employee table. If you need history of people moving between teams then need to go more complex but will assume you don't. This now gives you ability to call up a team and see all employees.
You also need a field in employees to carry the commission code to link into the commission table.

Now you need a sales table which has customerID, date, employeeID, productID and any other info you need.
You can now use this table to link sales to products, to customers, to employees - so Fred Smith was sold a BT line by Alan Jones on 12/7/06 and any other stuff you need. Also with a query using the sales table and commission table you can see that he gets a bonus of £3m for selling such a useless product(ho ho)

I think that gets you a long way down the road but if nothing else if you move to this sort of framework and find other issues I can try to help further.
Hope this helps
Good luck

Dear Malcy

Thank you for your reply. One question that I already have is:

My understanding is that I need a separate table for each product (Since each product has different attributes that need to be a part of the database i.e. "Install Date" and "Install Status" for Sky, "Credit Check Date" and "Network" for Phones etc.) Hence Alan Smith was sold a Sky Plus deal on the 06/07/06 by Jim Collins, which was NOT INSTALLED due to MASSES OF RUBBISH AT THE FRONT DOOR and he was assigned a rebook date of 22/07/06. Similarly Alan Smith was sold a Contractual Phone on 3G and VODAFONE, he requested a NOKIA 3130 and a Motorolla Razor, he passed the credit check on 22/07/06, phones were shipped 24/07/06.

Do I then have a number field in each product table "Product_ID" and manually assign an ID number to each product?

Thank you

I would have said that having one table per product was asking for a nightmare scenario.
You can always have fields left null in your table so you can have fields for dtmInstallDate, lngInstallStatus, dtmCreditCheck, lngNetwork (create a separate table for networks since much more efficient to store the number "6" 400 times than the string "Orange". If it is a Sky install then you leave the last two fields null, and if a mobile, then the first two fields stay null.
It sounds like you also need an installationID or will each "sale" result in only one set of actions? If a sale may result in a multiple set of actions better to also have an installation table with salesId and installation notes etc.
Does this clarify at all?

Thank you.

I will give it a go, and I will post the result here.

I feel rather stupid now... but it is not working as intended.

The things that I have done are as follows:

http://www.dmitri.distyle.co.uk/img/db_Tables.JPG (Tables that I have created)

http://www.dmitri.distyle.co.uk/img/db_Rel_Quary.JPG (Relationships + one Quary which does not input data into any of the tables)

I think that the problem is that I am trying to run a Quary and create a Customer, a Sale and Install at the same time... but it wont let me...

Hi
I tried looking at links but cannot read them. I think you can attach jpg files directly, otherwise try zipping your db and attaching.
I have a bit of time at the moment so may be able to help although I may have to head off for a bit

Right trying to attach my DB.

It does not have the quary that I want in it though . Gonna reattach now...

Reattachment.

Tried doing some changes... and it all does not work...

I took a wrong path somewhere.

Here's the attachment (Without the quary, but with forms)

Hi
I have played around with your database and so far as I can see it seems to be working but you know what you want it to do.
Several things
1. The file is the same name so make sure you open it to a different location to avoid overwriting yours (sorry - I rushed)
2. I have applied naming convention to tables and fields. I think you will find it makes sense later on
3. I have created new tables for regularly used descriptions eg marital status, residential status etc
4. I have set it to compact on close which saves bloat

See what you think and let me know