Help with table design

[TonyWilliams via AccessMonster.com]

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

 

[John W. Vinson]

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.

 

[Steve]

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
(e-mail address removed)

 

[TonyWilliams via AccessMonster.com]

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

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
(e-mail address removed)

I am building a database of financial deals for companies.

[quoted text clipped - 21 lines]

Thanks
Tony

 

[John W. Vinson]

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.

 

[TonyWilliams via AccessMonster.com]

Thanks John this sounds like a job for the weekend! I'll post back if I have
any problems.
Thanks agian
Tony