Query group by Product Name

[jhuncabas via AccessMonster.com]

Product UnitCost Date IN OUT Daily
Total Inventory

ProductOne 120 10-Sep-2003 100 100
ProductOne 110 11-Sep-2003 10 90
ProductOne 95 12-Sep-2003 500 590

ProductTwo 100 12-Sep-2003 60 60
ProductTwo 105 15-Sep-2003 90 150
ProductTwo 107 17-Sep-2003 50 100


Query:

SELECT T1.Product, T1.UnitCost, T1.Date, T1.[IN], T1.OUT, (SELECT SUM([IN]-
[OUT]) FROM DailyInventory T2 WHERE T2.[date] <= T1.[date]) AS [Daily
Total Inventory]
FROM DailyInventory AS T1
ORDER BY T1.Date;


Is there any chance we can group the PRODUCT HAVING DAILY TOTAL INVENTORY?
Like the sample above? With corresponding UnitCost?

Thanks and really appreaciate your help!


Jhun C.

 

[Dale Fye]

Not quite sure what you are getting at? Is this for a report? If so, you
could probably just add a textbox to the right of the In and Out textboxes,
set it's control source to something like:

= NZ([IN], 0) - NZ([Out], 0)

Then set it's running sum property to "Over Group". If you do this, you
should not need to include the imbedded SELECT clause in the Reports query.

However, if you want to present this in a query, it might look like:

SELECT T1.Product, T1.UnitCost, T1.Date, T1.[IN], T1.OUT,
(SELECT SUM(NZ([IN],0)-NZ([OUT],0))
FROM DailyInventory T2
WHERE T2.[Product] = T1.[Product]
AND T2.[date] <= T1.[date]) AS [Inventory]
FROM DailyInventory AS T1
ORDER BY T1.Product, T1.Date;


As to the query, it
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[jhuncabas via AccessMonster.com]

Thanks Dale,

Thanks Dale to your effort, Inventory "IN" and "OUT" is almost the same with
"DEBIT" and "CREDIT", Am I correct Mr. Dale? Once again, thanks to your
effort.

Not quite sure what you are getting at? Is this for a report? If so, you
could probably just add a textbox to the right of the In and Out textboxes,
set it's control source to something like:

= NZ([IN], 0) - NZ([Out], 0)

Then set it's running sum property to "Over Group". If you do this, you
should not need to include the imbedded SELECT clause in the Reports query.

However, if you want to present this in a query, it might look like:

SELECT T1.Product, T1.UnitCost, T1.Date, T1.[IN], T1.OUT,
(SELECT SUM(NZ([IN],0)-NZ([OUT],0))
FROM DailyInventory T2
WHERE T2.[Product] = T1.[Product]
AND T2.[date] <= T1.[date]) AS [Inventory]
FROM DailyInventory AS T1
ORDER BY T1.Product, T1.Date;

As to the query, it

Product UnitCost Date IN OUT Daily
Total Inventory

[quoted text clipped - 21 lines]

Jhun C.