# 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,StoreQpurch 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?

#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-C8Does any of this answer your question?MIKE

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 youDate Qpurch QSold UPC Description UnitPPrice Store14/05/2010 7 1004060 Drills masonary 25 mm \$6.21 Doncaster14/05/2010 10 1004060 Drills masonary 25 mm \$6.21 Doncaster14/05/2010 16 1004060 Drills masonary 25 mm \$6.21 Doncaster15/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 howeverFrom what I can see you do not have any Qsold.Also I don’t understand why your using Database functions?MIKEhttp://www.skeptic.com/

Report •
Related Solutions