If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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??? |
#12
|
|||
|
|||
Database Design
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??? |
#13
|
|||
|
|||
Database Design
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??? |
#14
|
|||
|
|||
Database Design
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??? |
#15
|
|||
|
|||
Database Design
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??? |
#16
|
|||
|
|||
Database Design
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??? |
#17
|
|||
|
|||
Database Design
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???? |
#18
|
|||
|
|||
Database Design
"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) |
|
Thread Tools | |
Display Modes | |
|
|