is there a database formula to use?

Microsoft Windows xp professional w/serv...
May 30, 2010 at 17:17:54
Specs: Windows XP
Hi,
I have an extract of a data that is sorted as follows: Date,Qpurch,QSold,UPC,Description,UnitPPrice,Store

Qpurch has numbers purchased, Qsold has number sold. I want to subtract Qsold from Qpurch and then give a total. Is there a database function that I can use to make this happen?

Thanks


See More: is there a database formula to use?

Report •


#1
May 30, 2010 at 18:34:23
Not completely sure what it is your asking.

But if your data is arrainged as:

 A          B      C    D    E     F      G     H       
Date	  Qpurch QSold UPC Desc UPPrice Store Totals    
1/30/2010  100    50                           50 =B2-C2
2/30/2010  101    49                           52 =B3-C3
3/30/2010  102    48                           54 =B4-C4
4/30/2010  103    47                           56 =B5-C5
5/30/2010  104    46                           58 =B6-C6
---------------------------------------------------------
   Totals  510   240                          270 =B8-C8
    =sum(B2:B6) =sum(C2:C6)

To get the Individual Qpurch - Qsold is =B2-C2 etc. etc.
To get the Total Qpurch =sum(B2:B6)
To get the Total Qsold =sum(C2:C6)
To get the Total Qpurch - Qsold is =B8-C8

Does any of this answer your question?

MIKE

http://www.skeptic.com/


Report •

#2
May 30, 2010 at 20:49:45
Hi, Thanks for your response.

I have copied below an extract of a database in excel. My objective is to value closing balance of inventory of this particular item (UPC 1004060). Opening balance is Nil. I know that total of Qpurch - total of Qsold will give me this answer however, I have been using functions in excel to calculate similar information, particularly the Dcounta and Dsum functions. Can I use the same functions or is there another function that I can use to calculate my objective?

Thank you


Date Qpurch QSold UPC Description UnitPPrice Store
14/05/2010 7 1004060 Drills masonary 25 mm $6.21 Doncaster
14/05/2010 10 1004060 Drills masonary 25 mm $6.21 Doncaster
14/05/2010 16 1004060 Drills masonary 25 mm $6.21 Doncaster
15/05/2010 6 1004060 Drills masonary 25 mm $6.21 Doncaster


Report •

#3
May 31, 2010 at 08:44:46
Tip:
If you use the pre tags found above the comments box, you can line up your example data to make it easier for us to read.
Also you can preview your reply by clicking “Preview Follow Up” before actually posting and straighten out anything that doesn’t look right.
And you can repeat the process by checking the box next to "Check To Show Confirmation Page Again"


I'm not sure if this is what your table should look like....

Date       Qpurch Qsold UPC         Description        UnitPPrice  Store
14/05/2010   7          1004060  Drills Masonary 25_mm   $6.21    Doncaster
14/05/2010  10          1004060  Drills Masonary 25_mm   $6.21    Doncaster
14/05/2010  16          1004060  Drills Masonary 25_mm   $6.21    Doncaster
15/05/2010   6          1004060  Drills Masonary 25_mm   $6.21    Doncaster

I know that total of Qpurch - total of Qsold will give me this answer however
From what I can see you do not have any Qsold.
Also I don’t understand why your using Database functions?

MIKE

http://www.skeptic.com/


Report •

Related Solutions


Ask Question