A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with table design



 
 
Thread Tools Display Modes
  #1  
Old February 10th, 2010, 06:06 PM posted to microsoft.public.access.tablesdbdesign
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default 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  
Old February 10th, 2010, 07:40 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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]
  #3  
Old February 10th, 2010, 07:56 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Help with table design

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


"TonyWilliams via AccessMonster.com" u56994@uwe wrote in message
news:a36edac5281bb@uwe...
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



  #4  
Old February 10th, 2010, 10:12 PM posted to microsoft.public.access.tablesdbdesign
Stop$teve
external usenet poster
 
Posts: 76
Default 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  
Old February 11th, 2010, 10:21 AM posted to microsoft.public.access.tablesdbdesign
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default 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  
Old February 11th, 2010, 07:57 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old February 11th, 2010, 08:19 PM posted to microsoft.public.access.tablesdbdesign
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.