help me please

Results 1 to 2 of 2

Thread: help me please

  1. #1
    ed Guest

    Default help me please

    my access database has following fields. <BR><BR>person1,ratio1,person2,ratio2,person3,rati o3,cost <BR>a,20%,b,30%,c,50%,US100 <BR>a,100%,"","","","",US100 <BR>d,50%,b,50%,"","",US200 <BR><BR>how can i use sql statement to cal the amount owned by diff user.the end result should be something like below. <BR><BR>a US120 (20% of US100 + 100% of US100) <BR>b US130 (30% of US100 + 50% of US200) <BR>c US50 (50% of US100) <BR>d US100 (50% of US 200)

  2. #2
    Join Date
    Dec 1969

    Default This is really tough...

    ...because you have a poor design to your DB!<BR><BR>It is not properly "normalized".<BR><BR>Suppose the data looked like this, instead:<BR><BR>person1,ratio1,person2,ratio2,pers on3,ratio3,cost <BR>a,20%,b,30%,c,50%,US100 <BR>x,10%,a,90%,"","",US100 <BR>d,50%,b,50%,"","",US200 <BR><BR>Now you have to get <BR>a = 20% of 100 + 90% of 100<BR>but a doesn&#039;t appear in the same column in both records!<BR><BR>SQL doesn&#039;t handle this kind of matching well, at all!<BR><BR>Can you reorganize your DB? So it has *TWO* tables, that look like this:<BR><BR>CostTable<BR>dataSet#, cost<BR>1, &nbsp; &nbsp; 100<BR>2, &nbsp; &nbsp; 100<BR>3, &nbsp; &nbsp; 200<BR><BR>RatioTable<BR>dataSet#, personID, ratio<BR>1,&nbsp; &nbsp; a, &nbsp; &nbsp; 20%<BR>1,&nbsp; &nbsp; b, &nbsp; &nbsp; 30%<BR>...<BR>2,&nbsp; &nbsp; a, &nbsp; &nbsp; 100%<BR>...<BR><BR>And now it&#039;s pretty easy:<BR><BR>SELECT R.personID, Sum( R.ratio * C.cost ) <BR>FROM CostTable AS C, RatioTable AS R<BR>WHERE C.dataSet# = R.dataSet#<BR>GROUP BY R.personID<BR><BR>*Can* you reorganize your DB to the "normalized" form?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts