Database Design

I now have two tables, the Country Table and the Product Family Table. The
Country table has an Autonumber CountryID which is the PK and the country
name. The ProductFamily Table has the ProductFamilyID which is the PK and the
PF name. I now want to create the third table which acts as a junction for
the above two tables for the many to many relationship. I have so far
included the ProductFamilyID and the CountryID but have errors like I said
when I combine the Country and the PF.

"Douglas J. Steele" wrote:

Don't create artificial concatenated fields like UKCar, ArgentinaCar, etc.
As I said else-thread, indexes can have up to 10 separate fields in them.
The Primary Key of the table should be comprised of whatever field(s)
uniquely identify Country and whatever field(s) unique indentify Product
Family.

List the tables you now have, the fields in each, and which fields are the
PK for each table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
When I try to set up a combination of Country and Product family its
telling
me that duplicate will be created and thats not possible. I have joined
the
country and the PFamily e.g UKCar, ArgentinaCar, USCar et.c

"Douglas J. Steele" wrote:

That sample data should be 4 rows:

Country Product Family ItemType ItemValue
Uk Car AllocatedCost1 56
Uk Car AllocatedCost2 34
Uk Car EventCost1 28
Uk Car EventCost2 67

(of course, more meaningful names than "AllocatedCost1" and "EventCost2"
would be useful)

I'd say yes, you do want a table that holds valid Country-Product Family
combinations, especially if there's additional information to be stored
at
that level.

You probably should also have a table of valid Cost types.

Your primary keys should be whatever's required to guarantee a unique
row.
Presumably in the Country_ProductFamily table, the two fields Country and
Product Family are sufficient. In the table I illustrate above, the
combination Country, Product Family and ItemType should be sufficient.
Note,
though, that there are many people who say you shouldn't use "natural
keys"
and should always have an Autonumber field as the PK.

I don't see a need for an output table: you should be able to derive that
using a query:

SELECT Country, ProductFamily, Sum(AllocatedCost) AS TotalAllocatedCost,
Sum(EventCost) AS TotalEventCost
FROM
(
SELECT Country, ProductFamily, Sum(ItemValue) AS AllocatedCost, 0 AS
EventCost
FROM MyTable
WHERE ItemType LIKE "AllocatedCost*"
GROUP BY Country, ProductFamily
UNION
SELECT Country, ProductFamily, 0 AS AllocatedCost, Sum(ItemValue) AS
EventCost
FROM MyTable
WHERE ItemType LIKE "EventCost*"
GROUP BY Country, ProductFamily
) AS FirstQuery
GROUP BY Country, ProductFamily

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I have a table which looks like the one below

Country Product Family AllocatedCost 1 AllocatedCost 2 EventCost
1
EventCost2
Uk Car 56 34
28 67

At the end as an output I need to have a table which shows me

Country ProductFamily TotalAllocatedCost TotalEventCost

My questions a Do I have to break this table into two one for
Allocated
Costs and the other for Event Costs but still maintaining the Country
and
the
product family?
What can I make primary key/s either in this table or if i break them?
Lastly how do i build calculations that would feed into the output
table?

Another question I have been trying to build a output table query
calculation that takes the information from other tables i.e adding
allocated
cost 1+allocated cost 2 to give me totalallocated cost but its asking
me
to
enter a parameter value???

Another thing the cost components are the components which are computed to
come up with the cost of the product family. So lets say a computer hardware
product family underlying its cost are components like labour, transport,
other components etc. So you would see that for another product family I have
the same components ie labour, transport, other components. They might be the
same for different PFs say Labour will be the same for all the PFs but they
might also differ I mean in value but not name. Say labour might be 30cents
for another PF and 24cents for the other but they fall under labour. So how
do I sort this?

"Douglas J. Steele" wrote:

That sample data should be 4 rows:

Country Product Family ItemType ItemValue
Uk Car AllocatedCost1 56
Uk Car AllocatedCost2 34
Uk Car EventCost1 28
Uk Car EventCost2 67

(of course, more meaningful names than "AllocatedCost1" and "EventCost2"
would be useful)

I'd say yes, you do want a table that holds valid Country-Product Family
combinations, especially if there's additional information to be stored at
that level.

You probably should also have a table of valid Cost types.

Your primary keys should be whatever's required to guarantee a unique row.
Presumably in the Country_ProductFamily table, the two fields Country and
Product Family are sufficient. In the table I illustrate above, the
combination Country, Product Family and ItemType should be sufficient. Note,
though, that there are many people who say you shouldn't use "natural keys"
and should always have an Autonumber field as the PK.

I don't see a need for an output table: you should be able to derive that
using a query:

SELECT Country, ProductFamily, Sum(AllocatedCost) AS TotalAllocatedCost,
Sum(EventCost) AS TotalEventCost
FROM
(
SELECT Country, ProductFamily, Sum(ItemValue) AS AllocatedCost, 0 AS
EventCost
FROM MyTable
WHERE ItemType LIKE "AllocatedCost*"
GROUP BY Country, ProductFamily
UNION
SELECT Country, ProductFamily, 0 AS AllocatedCost, Sum(ItemValue) AS
EventCost
FROM MyTable
WHERE ItemType LIKE "EventCost*"
GROUP BY Country, ProductFamily
) AS FirstQuery
GROUP BY Country, ProductFamily

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I have a table which looks like the one below

Country Product Family AllocatedCost 1 AllocatedCost 2 EventCost 1
EventCost2
Uk Car 56 34
28 67

At the end as an output I need to have a table which shows me

Country ProductFamily TotalAllocatedCost TotalEventCost

My questions a Do I have to break this table into two one for Allocated
Costs and the other for Event Costs but still maintaining the Country and
the
product family?
What can I make primary key/s either in this table or if i break them?
Lastly how do i build calculations that would feed into the output table?

Another question I have been trying to build a output table query
calculation that takes the information from other tables i.e adding
allocated
cost 1+allocated cost 2 to give me totalallocated cost but its asking me
to
enter a parameter value???

Your Primary Key for the third table should be the two fields
ProductFamilyID and CountryID. Is that what you've got? If so, then how are
you populating the table: are you perhaps putting in duplicates?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I now have two tables, the Country Table and the Product Family Table. The
Country table has an Autonumber CountryID which is the PK and the country
name. The ProductFamily Table has the ProductFamilyID which is the PK and
the
PF name. I now want to create the third table which acts as a junction for
the above two tables for the many to many relationship. I have so far
included the ProductFamilyID and the CountryID but have errors like I said
when I combine the Country and the PF.

"Douglas J. Steele" wrote:

Don't create artificial concatenated fields like UKCar, ArgentinaCar,
etc.
As I said else-thread, indexes can have up to 10 separate fields in them.
The Primary Key of the table should be comprised of whatever field(s)
uniquely identify Country and whatever field(s) unique indentify Product
Family.

List the tables you now have, the fields in each, and which fields are
the
PK for each table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
When I try to set up a combination of Country and Product family its
telling
me that duplicate will be created and thats not possible. I have joined
the
country and the PFamily e.g UKCar, ArgentinaCar, USCar et.c

"Douglas J. Steele" wrote:

That sample data should be 4 rows:

Country Product Family ItemType ItemValue
Uk Car AllocatedCost1 56
Uk Car AllocatedCost2 34
Uk Car EventCost1 28
Uk Car EventCost2 67

(of course, more meaningful names than "AllocatedCost1" and
"EventCost2"
would be useful)

I'd say yes, you do want a table that holds valid Country-Product
Family
combinations, especially if there's additional information to be
stored
at
that level.

You probably should also have a table of valid Cost types.

Your primary keys should be whatever's required to guarantee a unique
row.
Presumably in the Country_ProductFamily table, the two fields Country
and
Product Family are sufficient. In the table I illustrate above, the
combination Country, Product Family and ItemType should be sufficient.
Note,
though, that there are many people who say you shouldn't use "natural
keys"
and should always have an Autonumber field as the PK.

I don't see a need for an output table: you should be able to derive
that
using a query:

SELECT Country, ProductFamily, Sum(AllocatedCost) AS
TotalAllocatedCost,
Sum(EventCost) AS TotalEventCost
FROM
(
SELECT Country, ProductFamily, Sum(ItemValue) AS AllocatedCost, 0 AS
EventCost
FROM MyTable
WHERE ItemType LIKE "AllocatedCost*"
GROUP BY Country, ProductFamily
UNION
SELECT Country, ProductFamily, 0 AS AllocatedCost, Sum(ItemValue) AS
EventCost
FROM MyTable
WHERE ItemType LIKE "EventCost*"
GROUP BY Country, ProductFamily
) AS FirstQuery
GROUP BY Country, ProductFamily

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I have a table which looks like the one below

Country Product Family AllocatedCost 1 AllocatedCost 2
EventCost
1
EventCost2
Uk Car 56 34
28 67

At the end as an output I need to have a table which shows me

Country ProductFamily TotalAllocatedCost TotalEventCost

My questions a Do I have to break this table into two one for
Allocated
Costs and the other for Event Costs but still maintaining the
Country
and
the
product family?
What can I make primary key/s either in this table or if i break
them?
Lastly how do i build calculations that would feed into the output
table?

Another question I have been trying to build a output table query
calculation that takes the information from other tables i.e adding
allocated
cost 1+allocated cost 2 to give me totalallocated cost but its
asking
me
to
enter a parameter value???

I believe I answered that in another post.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
Another thing the cost components are the components which are computed to
come up with the cost of the product family. So lets say a computer
hardware
product family underlying its cost are components like labour, transport,
other components etc. So you would see that for another product family I
have
the same components ie labour, transport, other components. They might be
the
same for different PFs say Labour will be the same for all the PFs but
they
might also differ I mean in value but not name. Say labour might be
30cents
for another PF and 24cents for the other but they fall under labour. So
how
do I sort this?

"Douglas J. Steele" wrote:

That sample data should be 4 rows:

Country Product Family ItemType ItemValue
Uk Car AllocatedCost1 56
Uk Car AllocatedCost2 34
Uk Car EventCost1 28
Uk Car EventCost2 67

(of course, more meaningful names than "AllocatedCost1" and "EventCost2"
would be useful)

I'd say yes, you do want a table that holds valid Country-Product Family
combinations, especially if there's additional information to be stored
at
that level.

You probably should also have a table of valid Cost types.

Your primary keys should be whatever's required to guarantee a unique
row.
Presumably in the Country_ProductFamily table, the two fields Country and
Product Family are sufficient. In the table I illustrate above, the
combination Country, Product Family and ItemType should be sufficient.
Note,
though, that there are many people who say you shouldn't use "natural
keys"
and should always have an Autonumber field as the PK.

I don't see a need for an output table: you should be able to derive that
using a query:

SELECT Country, ProductFamily, Sum(AllocatedCost) AS TotalAllocatedCost,
Sum(EventCost) AS TotalEventCost
FROM
(
SELECT Country, ProductFamily, Sum(ItemValue) AS AllocatedCost, 0 AS
EventCost
FROM MyTable
WHERE ItemType LIKE "AllocatedCost*"
GROUP BY Country, ProductFamily
UNION
SELECT Country, ProductFamily, 0 AS AllocatedCost, Sum(ItemValue) AS
EventCost
FROM MyTable
WHERE ItemType LIKE "EventCost*"
GROUP BY Country, ProductFamily
) AS FirstQuery
GROUP BY Country, ProductFamily

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I have a table which looks like the one below

Country Product Family AllocatedCost 1 AllocatedCost 2 EventCost
1
EventCost2
Uk Car 56 34
28 67

At the end as an output I need to have a table which shows me

Country ProductFamily TotalAllocatedCost TotalEventCost

My questions a Do I have to break this table into two one for
Allocated
Costs and the other for Event Costs but still maintaining the Country
and
the
product family?
What can I make primary key/s either in this table or if i break them?
Lastly how do i build calculations that would feed into the output
table?

Another question I have been trying to build a output table query
calculation that takes the information from other tables i.e adding
allocated
cost 1+allocated cost 2 to give me totalallocated cost but its asking
me
to
enter a parameter value???

when I create a table with CountryID and PFId it means lets say CountryId is
1 then for that 1 there is 1-46 PFId's for that Country so If I try to make
both of them PK access say you have duplicates because I have a list of 1s
and a list of 2s etc

"Douglas J. Steele" wrote:

Your Primary Key for the third table should be the two fields
ProductFamilyID and CountryID. Is that what you've got? If so, then how are
you populating the table: are you perhaps putting in duplicates?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I now have two tables, the Country Table and the Product Family Table. The
Country table has an Autonumber CountryID which is the PK and the country
name. The ProductFamily Table has the ProductFamilyID which is the PK and
the
PF name. I now want to create the third table which acts as a junction for
the above two tables for the many to many relationship. I have so far
included the ProductFamilyID and the CountryID but have errors like I said
when I combine the Country and the PF.

"Douglas J. Steele" wrote:

Don't create artificial concatenated fields like UKCar, ArgentinaCar,
etc.
As I said else-thread, indexes can have up to 10 separate fields in them.
The Primary Key of the table should be comprised of whatever field(s)
uniquely identify Country and whatever field(s) unique indentify Product
Family.

List the tables you now have, the fields in each, and which fields are
the
PK for each table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
When I try to set up a combination of Country and Product family its
telling
me that duplicate will be created and thats not possible. I have joined
the
country and the PFamily e.g UKCar, ArgentinaCar, USCar et.c

"Douglas J. Steele" wrote:

That sample data should be 4 rows:

Country Product Family ItemType ItemValue
Uk Car AllocatedCost1 56
Uk Car AllocatedCost2 34
Uk Car EventCost1 28
Uk Car EventCost2 67

(of course, more meaningful names than "AllocatedCost1" and
"EventCost2"
would be useful)

I'd say yes, you do want a table that holds valid Country-Product
Family
combinations, especially if there's additional information to be
stored
at
that level.

You probably should also have a table of valid Cost types.

Your primary keys should be whatever's required to guarantee a unique
row.
Presumably in the Country_ProductFamily table, the two fields Country
and
Product Family are sufficient. In the table I illustrate above, the
combination Country, Product Family and ItemType should be sufficient.
Note,
though, that there are many people who say you shouldn't use "natural
keys"
and should always have an Autonumber field as the PK.

I don't see a need for an output table: you should be able to derive
that
using a query:

SELECT Country, ProductFamily, Sum(AllocatedCost) AS
TotalAllocatedCost,
Sum(EventCost) AS TotalEventCost
FROM
(
SELECT Country, ProductFamily, Sum(ItemValue) AS AllocatedCost, 0 AS
EventCost
FROM MyTable
WHERE ItemType LIKE "AllocatedCost*"
GROUP BY Country, ProductFamily
UNION
SELECT Country, ProductFamily, 0 AS AllocatedCost, Sum(ItemValue) AS
EventCost
FROM MyTable
WHERE ItemType LIKE "EventCost*"
GROUP BY Country, ProductFamily
) AS FirstQuery
GROUP BY Country, ProductFamily

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I have a table which looks like the one below

Country Product Family AllocatedCost 1 AllocatedCost 2
EventCost
1
EventCost2
Uk Car 56 34
28 67

At the end as an output I need to have a table which shows me

Country ProductFamily TotalAllocatedCost TotalEventCost

My questions a Do I have to break this table into two one for
Allocated
Costs and the other for Event Costs but still maintaining the
Country
and
the
product family?
What can I make primary key/s either in this table or if i break
them?
Lastly how do i build calculations that would feed into the output
table?

Another question I have been trying to build a output table query
calculation that takes the information from other tables i.e adding
allocated
cost 1+allocated cost 2 to give me totalallocated cost but its
asking
me
to
enter a parameter value???

How are you attempting to make them both the PK?

If you're doing this through the GUI, you select both fields (use the Shift
or Ctrl key), then set that combination as the PK.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
when I create a table with CountryID and PFId it means lets say CountryId
is
1 then for that 1 there is 1-46 PFId's for that Country so If I try to
make
both of them PK access say you have duplicates because I have a list of 1s
and a list of 2s etc

"Douglas J. Steele" wrote:

Your Primary Key for the third table should be the two fields
ProductFamilyID and CountryID. Is that what you've got? If so, then how
are
you populating the table: are you perhaps putting in duplicates?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I now have two tables, the Country Table and the Product Family Table.
The
Country table has an Autonumber CountryID which is the PK and the
country
name. The ProductFamily Table has the ProductFamilyID which is the PK
and
the
PF name. I now want to create the third table which acts as a junction
for
the above two tables for the many to many relationship. I have so far
included the ProductFamilyID and the CountryID but have errors like I
said
when I combine the Country and the PF.

"Douglas J. Steele" wrote:

Don't create artificial concatenated fields like UKCar, ArgentinaCar,
etc.
As I said else-thread, indexes can have up to 10 separate fields in
them.
The Primary Key of the table should be comprised of whatever field(s)
uniquely identify Country and whatever field(s) unique indentify
Product
Family.

List the tables you now have, the fields in each, and which fields are
the
PK for each table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
When I try to set up a combination of Country and Product family its
telling
me that duplicate will be created and thats not possible. I have
joined
the
country and the PFamily e.g UKCar, ArgentinaCar, USCar et.c

"Douglas J. Steele" wrote:

That sample data should be 4 rows:

Country Product Family ItemType ItemValue
Uk Car AllocatedCost1 56
Uk Car AllocatedCost2 34
Uk Car EventCost1 28
Uk Car EventCost2 67

(of course, more meaningful names than "AllocatedCost1" and
"EventCost2"
would be useful)

I'd say yes, you do want a table that holds valid Country-Product
Family
combinations, especially if there's additional information to be
stored
at
that level.

You probably should also have a table of valid Cost types.

Your primary keys should be whatever's required to guarantee a
unique
row.
Presumably in the Country_ProductFamily table, the two fields
Country
and
Product Family are sufficient. In the table I illustrate above, the
combination Country, Product Family and ItemType should be
sufficient.
Note,
though, that there are many people who say you shouldn't use
"natural
keys"
and should always have an Autonumber field as the PK.

I don't see a need for an output table: you should be able to
derive
that
using a query:

SELECT Country, ProductFamily, Sum(AllocatedCost) AS
TotalAllocatedCost,
Sum(EventCost) AS TotalEventCost
FROM
(
SELECT Country, ProductFamily, Sum(ItemValue) AS AllocatedCost, 0
AS
EventCost
FROM MyTable
WHERE ItemType LIKE "AllocatedCost*"
GROUP BY Country, ProductFamily
UNION
SELECT Country, ProductFamily, 0 AS AllocatedCost, Sum(ItemValue)
AS
EventCost
FROM MyTable
WHERE ItemType LIKE "EventCost*"
GROUP BY Country, ProductFamily
) AS FirstQuery
GROUP BY Country, ProductFamily

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Percy" wrote in message
...
I have a table which looks like the one below

Country Product Family AllocatedCost 1 AllocatedCost 2
EventCost
1
EventCost2
Uk Car 56 34
28 67

At the end as an output I need to have a table which shows me

Country ProductFamily TotalAllocatedCost TotalEventCost

My questions a Do I have to break this table into two one for
Allocated
Costs and the other for Event Costs but still maintaining the
Country
and
the
product family?
What can I make primary key/s either in this table or if i break
them?
Lastly how do i build calculations that would feed into the
output
table?

Another question I have been trying to build a output table query
calculation that takes the information from other tables i.e
adding
allocated
cost 1+allocated cost 2 to give me totalallocated cost but its
asking
me
to
enter a parameter value???

The last one Douglas. I now have created all the relationships. I am not good
at SQL but here is what I have

CountryID Country Product Family Cost Name Cost Compnt
Cost Value
1 UK Wireless router Allocated engineering
12
1 UK Wireless router Allocated overhead
24
1 UK Wireless router Allocated SDM
23
1 UK Wireless router Sales Event Sales
78
1 UK Wireless router Sales Event marketing
44

So this is the resultant big table I managed to come up with. What is the
SQL i need to input to group by

country product family Total Allocated
Total Sales Event
UK Wireless router Allocated Overhed+AllocatedSDM SE
Sales+SE mrkt


I also have some Cost which are like factors I mean like percentages or even
high, medium or low. I cannot put them under Cost value because they dont
have the sma format but I need them in my output table or query????

"Percy" wrote in message
...
The last one Douglas. I now have created all the relationships. I am not
good
at SQL but here is what I have

CountryID Country Product Family Cost Name Cost Compnt
Cost Value
1 UK Wireless router Allocated engineering
12
1 UK Wireless router Allocated overhead
24
1 UK Wireless router Allocated SDM
23
1 UK Wireless router Sales Event Sales
78
1 UK Wireless router Sales Event marketing
44

So this is the resultant big table I managed to come up with. What is the
SQL i need to input to group by
country product family Total Allocated
Total Sales Event
UK Wireless router Allocated Overhed+AllocatedSDM SE
Sales+SE mrkt

In the first post I made in this thread, I gave you SQL. Now that you've
made some changes to the data, you should be able to use:

SELECT Country, ProductFamily, Sum(AllocatedCost) AS TotalAllocated,
Sum(EventCost) AS TotalSalesEvent
FROM
(
SELECT Country, ProductFamily, Sum(CostValue) AS AllocatedCost, 0 AS
EventCost
FROM MyTable
WHERE CostName = "Allocated"
GROUP BY Country, ProductFamily
UNION
SELECT Country, ProductFamily, 0 AS AllocatedCost, Sum(CostValue) AS
EventCost
FROM MyTable
WHERE CostName = "Sales Event"
GROUP BY Country, ProductFamily
) AS FirstQuery
GROUP BY Country, ProductFamily



I also have some Cost which are like factors I mean like percentages or
even
high, medium or low. I cannot put them under Cost value because they dont
have the sma format but I need them in my output table or query????

Without some details, I can't help you.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)