Total Product Query

[SpikeyA]

Hi,

Just wondering if anyone has any suggestions on how to total up date.

I have enclosed two images, 1 of the query I am running and 2 a snippet of the results.

The problem I am having is that I cannot get the [Stock No] to total. Is there any way this can be done?

The SQL is showing as follows but my knowledge of coding is limited to say the least. Any help would be appreciated.

SELECT [Purchase Orders].[Stock No], [Purchase Orders].Balance, [Purchase Orders].[Due Date], Month([Due Date]) AS PurchaseMonth
FROM [Purchase Orders]
WHERE (((Month([Due Date]))=Month(Now())))
ORDER BY [Purchase Orders].[Stock No];

Additionally, is it possible on the Criteria: Month(Now()) to set +1, +2, +3, +4 so I can see the products that are due in the future.

Ideally I am looking to set 4 Tables (now, +1, +2, +3, +4) then feed this back into generate a stock forecast based on free stock, average sales and quantity due.

Thanks again.

Ant

 

[CJ_London]

suggest you need a crosstab query - one of the options in the query window
SELECT [Purchase Orders].[Stock No], [Purchase Orders].Balance, [Purchase Orders].[Due Date], Month([Due Date]) AS PurchaseMonth
FROM [Purchase Orders]
WHERE (((Month([Due Date]))=Month(Now())))
ORDER BY [Purchase Orders].[Stock No];

Change the criteria to

WHERE [Due Date]>Date()

set stockno to groupby and row
set purchasemonth to groupby and column
set balance to sum and value
untick the show box for due date

Note: month means that the way this is set at the moment, balances due for 1st March 2015 and 20th March 2016 will be added together under March. You might want to consider an alternative as yearmonth e.g. 201403, 201503