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 |
#1
|
|||
|
|||
Help with table design
I am building a database of financial deals for companies.
I will build a table with company details, including a field called txtcompany and then another table with the details of the deals with a field called txtdealnbr and also a field called txtcompany to link to tblcompany. For each txtdealnbr there will be a field called txttotalline which represents the value of the deal. Now comes my question. Each deal needs to be analysed to show what percentage of the deal is either any one of 7 categories. So a deal could be 50% cat1, 20% cat4, 10% cat5 and 20% cat6. However the user wants to be able to view on a form all 7 categories even in the %age is zero. So do I have 7 fields to hold the percentage value for each category or do I build a table of categories and if I do how do I show the value of each on my form? How would I construct the tables and theirrelationships. Hope I've explained that clearly, any help would be apppreciated. Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#2
|
|||
|
|||
Help with table design
On Wed, 10 Feb 2010 17:06:59 GMT, "TonyWilliams via AccessMonster.com"
u56994@uwe wrote: Now comes my question. Each deal needs to be analysed to show what percentage of the deal is either any one of 7 categories. So a deal could be 50% cat1, 20% cat4, 10% cat5 and 20% cat6. However the user wants to be able to view on a form all 7 categories even in the %age is zero. So do I have 7 fields to hold the percentage value for each category or do I build a table of categories and if I do how do I show the value of each on my form? How would I construct the tables and theirrelationships. You need a (seven row) table of categories, and a "resolver table" related one to many to the Deals table and to the Categories table. This table would have seven rows for each deal, with fields for the deal ID, the category ID, and the percentage (which could be zero). A Subform based on this resolver table, using the deal ID as the master/child link field, would display the data as you wish. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Help with table design
"Steve" schreef in bericht ... Hi Tony, A little revamp of your tables is needed: TblCategory CategoryID Category TblCompany CompanyID CompnayName other company fields TblDeal DealID CompanyID CategoryID DealCategoryValue With these tables you can create a form/subform where the main form is based on TblCompany and the subform is based on a query that includes TblCategory and TblDeal. You need to design the query to display all the categories and the DealCategoryValue for each category. -- Maybe $teve can help, but mind you... he is after your money... http://home.tiscali.nl/arracom/whoissteve.html Regards, Arno R |
#5
|
|||
|
|||
Help with table design
Thanks John and Steve for those suggestions. I think I understand the concept,
I've just done something similar for another of my posts. However, Steve, how would I create a query that shows all 7 categories on a form when I'm only holding one value? Thanks Tony Steve wrote: Hi Tony, A little revamp of your tables is needed: TblCategory CategoryID Category TblCompany CompanyID CompnayName other company fields TblDeal DealID CompanyID CategoryID DealCategoryValue With these tables you can create a form/subform where the main form is based on TblCompany and the subform is based on a query that includes TblCategory and TblDeal. You need to design the query to display all the categories and the DealCategoryValue for each category. Steve I am building a database of financial deals for companies. [quoted text clipped - 21 lines] Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Help with table design
On Thu, 11 Feb 2010 09:21:24 GMT, "TonyWilliams via AccessMonster.com"
u56994@uwe wrote: Thanks John and Steve for those suggestions. I think I understand the concept, I've just done something similar for another of my posts. However, Steve, how would I create a query that shows all 7 categories on a form when I'm only holding one value? You'ld use a Continuous Subform. It might be necessary to base it on a Query joining the seven-row categories table to the junction table using a Left Outer Join to show the category even if that category has not been picked. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Help with table design
Thanks John this sounds like a job for the weekend! I'll post back if I have
any problems. Thanks agian Tony John W. Vinson wrote: Thanks John and Steve for those suggestions. I think I understand the concept, I've just done something similar for another of my posts. However, Steve, how would I create a query that shows all 7 categories on a form when I'm only holding one value? You'ld use a Continuous Subform. It might be necessary to base it on a Query joining the seven-row categories table to the junction table using a Left Outer Join to show the category even if that category has not been picked. -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
Thread Tools | |
Display Modes | |
|
|