Relationships/query issue - Help!

[Chatholo]

Hi,

I have attached a dummy db to understand how to perform this on my real DB.

I have 4 tables:
1.product list
2.structure of the business (Buiness & business entities)
3.structure of the region

What I have been trying looks quite simple ... I think! Although I have tried every possible relationship and can't make it work!

I want to combine them together, so I want each line of table All lines of 2&3 to be matched to table 1. As per "wanted structure" in the dummyDB.
Basically my product list is global and I will need through a form to split my heads per business and country.

Thank you for your help!!


C.

[June7]

What are 'heads'?

tblProducts does not have a relationship with either of the other tables. The following query will cause every record of tblProducts to join with every record of tblMarkets (a Cartesian joining):

SELECT tblproduct.ProductID, tblproduct.[Product Description], tblproduct.Heads, tblMarkets.Region, tblMarkets.Market, tblMarkets.Entity FROM tblproduct, tblMarkets;

[Chatholo]

Heads are the headcount that were allocated to me per product line. I basically now need to create a new table in order to split this headcount for my countries and my business units. The new table will have as many identical product line items as different countries and in each country multiple business and so on…
I will then split the total headcount allocated to me through a form.

I had added APA region in the tblproduct (APA is the only region I look after) to create a relationship with the other tables, but didn’t work … not too sure how to achieve this …

[June7]

Does the suggested query produce the desired structure? If yes, use it to create table with those records. Then edit the Heads data.

[Chatholo]

It did! Thanks heaps!
Now I only have 1 issue. My heads are duplicated for each line when I should only have them against one line that would be my total. Can you suggest a way to do this?

I am thinking adding a specific line for the initial heads number in the tables structure, but I am not sure it will work..

[June7]

Maybe I could if I understood what you are trying to do. Unfortunately just don't get it. The new table resulted in 16 unique records.

[Chatholo]

This is my new table:

ProductID	Product Description	Heads	Region	Market	Entity
C2456	SealsA	89	APA	Japan	JVJapan1
C2456	SealsA	89	APA	Japan	JVJapan2
C2456	SealsA	89	APA	Vietnam	JVVietnam1
C2456	SealsA	89	APA	China	JVChina1
C5673	SealsB	190	APA	China	JVChina1
C5673	SealsB	190	APA	Japan	JVJapan2
C5673	SealsB	190	APA	Japan	JVJapan1
C5673	SealsB	190	APA	Vietnam	JVVietnam1
C7562	SealsC	445	APA	China	JVChina1
C7562	SealsC	445	APA	Vietnam	JVVietnam1
C7562	SealsC	445	APA	Japan	JVJapan1
C7562	SealsC	445	APA	Japan	JVJapan2
C7706	SealsD	557	APA	Japan	JVJapan2
C7706	SealsD	557	APA	China	JVChina1
C7706	SealsD	557	APA	Japan	JVJapan1
C7706	SealsD	557	APA	Vietnam	JVVietnam1

So that worked perfectly.

I was given a budget of 89 heads for the my first product for the whole APA Region. What I need to do is to allocate the 89 by market/entity which I will do through a form.
Before doing the form I would like the table to look like this:

ProductID	Product Description	Heads	Region	Market	Entity
C2456	SealsA		APA	Japan	JVJapan1
C2456	SealsA		APA	Japan	JVJapan2
C2456	SealsA		APA	Vietnam	JVVietnam1
C2456	SealsA		APA	China	JVChina1
C2456	SealsA	89	Total APA	Total APA	Total APA

So basically have the existing number of heads against one line, nothing against the others. I can then input allocated heads against each other line specific to a market/a business unit...
do I need to update the structure of my tables to add a "total APA" line or is there other options?

[Chatholo]

Is there anyone that can help me with my last question? Still haven't managed to get it right over the weekend ..
Thank you!
C.

[June7]

The total allocated for each product should be in Products table. Several ways to show this value on form.

1. form/subform arrangement - main form bound to Products and subform bound to new table

2. form has a RecordSource that joins the new table to the Products table, join type "show all records from new table ..."

3. DLookup expression in textbox

Now that you have the new table, remove the value from the Heads field. Only 16 records so can just edit each record manually or if you really want to - run an UPDATE query.

BTW, really don't need the product description in the new table.

[Chatholo]

Thanks June7. Will try each one of these and will see the one that best suits. Will let you know how I go.

[Chatholo]

June7.

Just tried form/subform which looks perfect for what I need. The only problem is I have added an additional field for my salary heads (text box) and when I want to fill in the heads for each line item in my new table (subform), it populates all the lines for this specific product.

Is it possible to change this to be able to update each line item in my subform individually?

[June7]

An unbound control that does not have an expression will show the same value in every record. Set textbox ControlSource to field.

[Chatholo]

Just to confirm. Are you saying "Set textbox ControlSource to "an existing" field"?

[Chatholo]

Got it! Thanks a lot for your help June7!