Looks to me but more experienced eyes may make out what you are trying to do.
Do you want the sales value summed for each Date (day) in Currency or Count of Items / Records ?
Do you know how to do a Select Query and get the Records that make up the data you wish to Sum?
If you can do a query, paste the sql here and you should get help with turning it into a total query.
Otherwise, supply some table and field names, types etc.
I have worked out the following query but do not know how to take it forward.
TRANSFORM Count(OBSale.saleCount) AS VolCount
SELECT OBSale[BGrp]
FROM OBSale
WHERE OBSale[Created Date] Between Format("1/9/2009","m/dd/yyyy") And Format(Date(),"m/dd/yyyy")
GROUP BY OBSale[BGrp]
PIVOT DateAdd("ww",DateDiff("ww",0,OBSale.[Created Date]),0)-1;
I meant the OBSale table. The trouble you're going to have without putting the numbers in the table is that a crosstab query will just leave blanks where there isn't a value/date combination.
I think I'd do it in this order:
An append query to create the rows in OBSale, populating the date field and leaving the sale count field blank.
Then, (not sure about this bit), update the sale count field to max of sale count, grouped by Bgroup, where sale count is null.
As long as your BETWEEN statement is not going to return hundreds of columns you should be fine.
This should give you some idea on how to create the query (without using a crosstab):
1. Create a query to return the DISTINCT dates i.e. the individual dates between 1/9/2009 and Today's Date like in your criteria.
2. Create another query that will return the DISTINCT BGrps.
3. Loop through the query in step 1 (using a recordset, sort in ASC) and append the distinct dates gotten from it to the query in step 2 as alias fields.
4. The aliased fields in step 3 will need to perform a count BETWEEN the min date from step 1 AND aliased field date. You can get the min date by saving it in a variable (from the recordset) in step 3, instead of using DMin().
I'm hoping that BGrp has a number ID.
The aliased field will need to use a subquery to perform the count. So the aliased field will end up looking like (take 1/16/2009 as an example):
Code:
(Select Count(*) From OBSale AS Q WHERE Q.CreatedDate BETWEEN (#1/9/2009# AND #1/16/2009#) AND Q.BGrpID = OBSale.BGrpID) AS [1/16/2009]
Notice that 1/9/2009 is the min date I mentioned you should save in a variable. You would need to build the query using code.
The final query could look like this:
Code:
[COLOR=Red]SELECT [BGrp][/COLOR][COLOR=Red],[/COLOR]
[COLOR=DarkGreen](Select Count(*) From OBSale AS Q WHERE Q.CreatedDate BETWEEN (#1/9/2009# AND #1/9/2009#) AND Q.BGrpID = qryDistinctBGroups.BGrpID) AS [1/9/2009][COLOR=Red][B],[/B] [/COLOR]
(Select Count(*) From OBSale AS Q WHERE Q.CreatedDate BETWEEN (#1/9/2009# AND #1/16/2009#) AND Q.BGrpID = qryDistinctBGroups.BGrpID) AS [1/16/2009][COLOR=Red][B],[/B][/COLOR] ...[/COLOR]
[COLOR=Red]FROM qryDistinctBGroups
ORDER BY [BGrp][/COLOR]
The subqueries are in green and you can see how each date is increasing.
thanks for all the suggestions so far, I was wondering if the this would be a simpler and direct way of obtaining the desired result.
I decided to use a DSUM function but have yet to get it work. The logic is to adopt your "Between" statement and sum up the figure.
E.g: date is between 1/2/2009 and 1/9/2009 (difference of 7 days)
This is the query I have came out so far:
SELECT OBSale.BusGrp, OBSale.[Created Date], DSum("saleCount","OBSale","OBSale.[Created Date]between OBSale.[Created Date] AND OBSale.[Created Date]+7") AS Expr1
FROM OBSale
GROUP BY OBSale.BusGrp, OBSale.[Created Date]
ORDER BY OBSale.[Created Date];
The result returned sumed all the figure that are outside of the date range as such:
How do I fix that so that the figure are sumed within the date specified?
As long as your BETWEEN statement is not going to return hundreds of columns you should be fine.
This should give you some idea on how to create the query (without using a crosstab):
1. Create a query to return the DISTINCT dates i.e. the individual dates between 1/9/2009 and Today's Date like in your criteria.
2. Create another query that will return the DISTINCT BGrps.
3. Loop through the query in step 1 (using a recordset, sort in ASC) and append the distinct dates gotten from it to the query in step 2 as alias fields.
4. The aliased fields in step 3 will need to perform a count BETWEEN the min date from step 1 AND aliased field date. You can get the min date by saving it in a variable (from the recordset) in step 3, instead of using DMin().
I'm hoping that BGrp has a number ID.
The aliased field will need to use a subquery to perform the count. So the aliased field will end up looking like (take 1/16/2009 as an example):
Code:
(Select Count(*) From OBSale AS Q WHERE Q.CreatedDate BETWEEN (#1/9/2009# AND #1/16/2009#) AND Q.BGrpID = OBSale.BGrpID) AS [1/16/2009]
Notice that 1/9/2009 is the min date I mentioned you should save in a variable. You would need to build the query using code.
The final query could look like this:
Code:
[COLOR=red]SELECT [BGrp][/COLOR][COLOR=red],[/COLOR]
[COLOR=darkgreen](Select Count(*) From OBSale AS Q WHERE Q.CreatedDate BETWEEN (#1/9/2009# AND #1/9/2009#) AND Q.BGrpID = qryDistinctBGroups.BGrpID) AS [1/9/2009][COLOR=red][B],[/B] [/COLOR]
(Select Count(*) From OBSale AS Q WHERE Q.CreatedDate BETWEEN (#1/9/2009# AND #1/16/2009#) AND Q.BGrpID = qryDistinctBGroups.BGrpID) AS [1/16/2009][COLOR=red][B],[/B][/COLOR] ...[/COLOR]
[COLOR=red]FROM qryDistinctBGroups[/COLOR]
[COLOR=red]ORDER BY [BGrp][/COLOR]
The subqueries are in green and you can see how each date is increasing.
My observation is that the "desired result" is not desirable. The original crosstab shows fairly simply what occurred and when for each BGrp and so, to me, portrays trends and dynamics that might be important to a business.
The "desired result", by contrast, is a sort of inventory view which doesn't seem like important information to be tabulated for every day that you move a product.
Before going through the trouble, I'd reasses whether the objective is worthwhile.