Table Structure

[AdamO]

Can you give me some advice about table structure. Currently I have:

INTRODUCER IntroducerID & name, address & contact details for introducer including their target. A separate form will be based on this table.

CUSTOMER CustomerID, IntroducerID & name, address, contact details & personal details (occupation, income, date of birth etc. A separate form will be based on this table.

CHILDREN ChildrenID, CustomerID & name & date of birth. This will be a subform within the Customer Form.

MARKETING PROJECT: MarketingProjID & name & detail about individual marketing projects. A separate form will be based on this table.

MARKETING DETAILS: MarketingDetailsID, CustomerID, MarketingProjID & date. This will be a subform within the Customer Form and also a separate form listing the customers within each marketing project.

It is now I have some doubts. I had in mind:

PRODUCTS ProductID, Product Area (this will be either Liability, Heath or General), Product type (there are about 10 types for each product area). A separate form will be based on this table. Should this be two tables?

Each of the three product area does have varying information, which needs to be recorded. It is consistent for each product area though. For example Liability has a set of fields, health has a different set & so does General.

On the Customer Form there will be a three control forms which open forms for Liability, Heath and General. They will be linked to the CustomerID and show only the customers own Product types they may have. The customer could have any number of Product Types within each of these Product Areas and more Product Area could apply. For example, a customer could have Liabilty products A, B & C, Health products Y & Z and General products T,G,H & D. Each product will have a commission earned amount. Whilst there is an Introducer associated with the Customer records, they may be a different Introducer associated with each Product type.

I did originally consider three tables for each product area, and not have the Products table mentioned above:

LIABILITY Liability ID, IntroducerID, ClientID & fields associated with liability including commission
HEALTH Health ID, IntroducerID, ClientID & fields associated with liability including commission
GENERAL General ID, IntroducerID, ClientID & fields associated with liability including commission

However, the forms worked fine but I had problems building a query which produced a report with the following headings:

Introducer – Liability Commission – Health Commission – General Commission – Total commission – Average Commission - Target – Variance against target.

I can get it to work for one product area but not all three combined.

I could combine all of the fields for the product types in one table rather than three which may assist with the commission calculations. But I am conscious that only a third for a given Product type ID will be filled at any one time. Perhaps I am worrying unduly. I know when I create the forms I need only add those appropriate to each Product area.

I am new to Access but recognise the importance of getting table structure correct first. Should my table structure for the Product details be in one table or one for each product area? Apologies if I have not explained well - getting confused myself!

Any guidance would be welcome. Thank you.

 

[Cosmos75]

Why not a table for Product Area and a seperate table for Product Type?

Does each Product Area have their own specific types? I.e. no one type is repeated across Product Areas
- One Product Area to Many Product Types?

Are each (or more than one) Poduct Types (10 of them) included in the Product Area (3 of them)?
- Many Product Types to Many Product Areas?

A table should contain the same type of information (and related info that can't further be put in their own respective tables), for example you wouldn't (if you were tracking car sales) have a table for each company (Dodge, Toyota, Honda, Volvo, VolksWagon...) BUT one table containing all car manufacturers. Maybe another table containing car types (2-door hatchback, 4-door sedan, SUV, Truck...). Since each Manufacturer makes most of the car types and each car type is made by most the maufacturers, we have a many-to-many relationship.

We can accomplish this by having a Juntion Table. Say we call this CarDetails, ok?

Manufactuer has a many-to-one relationship with CarDetails.
CarType has a many-to-one relationship with CarDetails.
Hence we now have a many-to-many relationship between Manufacturer and CarType (via CarDetails).

Car Details can contain things like Model name, HP rating, Gas Capacity.......

I hope this helps you make up decide on how to design your tables. If you have any more problems, post back, and I'll try to help as best I can.

 

[AdamO]

Thanks for your reply. I have structured my database accordingly and it ‘feels’ more correct now. I have a Product Area table, a Product type table and a Product detail table. The relationships are One Product Area to Many Product Types.

Apologies if this is not posted in the correct place – but it follows on. I am having problems writing a query & then a report for the following:

Introducer ID xxx
Liability commission xxx
Health commission xxx
General commission xxx
Total targeted commission xxx (Liability+ Heath + General)
Average targeted comm. Xxx
Introducer target xxx (in Introducer table)
Variance to target xxx
Accident commission xxx (there will be a fourth Product Area but Introdcers are not targeted against this)
Total commission xxx (Total targeted +Accident commission)

This ‘block’ of information above would follow on a report for each Introducer in sequence.

I can produce a query which sums all of the commissions for each Introducer (IntroducerID – ProductArea – SumOfCommission) but am at a loss to get this & the other information into a report like to above example. All the detail area is shown as ProductArea in design form and I cannot separate out between Product Areas and amend. I have tried separate commission queries for each product area and then tried to insert into a blank report, Dlookup for query calculations in the report & subreports. I am aware on how to do the multiplication fields in a report (e.g. totals & variances)

I know I am new to Access but cannot get this to work. Any guidance would be greatly appreciated.

 

[Cosmos75]

I don't have much time right now ( )but here's some help that might get you started in the right direction.

Sometimes you need to perform more than one query to get the infomation that you need. So try to think of your final "calculations" in terms of parts with each part needing its own query. Maybe this weekend, I'll have more time to post a more specific reply.

Hope this helps!:

 

[AdamO]

Thanks for getting back. I guessed I may require more than one query and I have calculated two which provide the information:

- one which shows for each Introducer their total Liabilty, total Health & total General commissions
- as above for the new product area.

My problem has been getting the above all onto one report. I figured if I can show this information I can add fields relatively easily which sum, average & calculate variance against target.

The new product area is the problem as it is not targeted. I have tried to slot this in as a subreport or as a new field but it does not seem to work. As I have initially based the report on one query, are these the ways of getting additional information into the report. Do i have to try and get all information into one final query to base the report on?

I presume I am probably looking at this the wrong way. If you do get chance to consider, I would appreciate it.

 

[Cosmos75]

AdamO,

I am sorry to taking so long to reply to you! I haven't had time to help you out.

Been busy trying to get help with my own databases!!!

Hopefully you've managed to get to the answer on your own or have gotten help from some of the helpful people here!

If still not, feel free to post you database here and I'll take a look at it!