grouping dates with a query

[Jay]

I have a problem. I am trying to put data into date "buckets", but the
grouping interval needs to be variable and inputted with a form. For
example, I want to look at how many orders were placed every "x" days,
where x is inputted on the form. The first date needs to be the first
date out of the data. If my first record has a date of 12/1/04, and my
form has the number 2 inputted on it, then the output of the query
should be this:
Date Sum of itm_proc
12/1/04-12/3/04 3
12/2/04-12/4/04 4
12/3/04-12/5/04 2

And so on. A pivot table would work, but I need to be able to adjust
the grouping levels based on the input on the form.

 

[Guest]

First you should not use a field named Date as that is an Access reserved word.

Use a Totals query with this --
Date Group: Format(Left(Partition([Date],0,999999,2),7),"mm/dd/yyyy") & " :
" & Format(Right(Partition([Date],0,999999,2),5),"mm/dd/yyyy")

 

[Jay]

I am not really using a field named date, and I am pretty sure I have
the format down, but I am not sure how to place the sum of items in the
buckets based off of user entry. If you could give me any info on
this, I would really appreciate it.

 

[Guest]

I tried to used a form to feed the query and it did not work.

I do not understand your comment -- sum of items in the buckets

To use what I gave to you create a select query and change to Totals query
by clicking on the Greek symbol for summation on the icon bar while in design
view.
Paste my post as your left-most column. I gave it to you to pull groups of
two days. For different size groups replae the '2' in
....Partition([Date],0,999999,2),5)... with your number of days. Replace
[Date] with your field name in brackets.

Drag itm_proc from your table to the FIELD row of the design grid and change
the TOTAL from Group By to Sum.

 

[Jay]

Hey, it worked. Thanks a lot. This is going to save me a lot of time.
I appreciate it.

 

[Jay]

Wait, there is an issue with this. It groups in intervals of two days,
which is great, but I need it to pull two day intervals for every day.
Something like this:

Date Group Sum
1/1/2003 3 (=sum of 1/1/2003 and 1/2/2003)
1/2/2003 4 (=sum of 1/2/2003 and 1/3/2003)
1/3/2003 5 (=sum of 1/3/2003 and 1/4/2003)

I still need the two day group, but I need it to pull for every day

 

[Jay]

There is a problem with the query. This is what it shows:
Date Group Sumofitm_proc
1/3/2003 : 23
1/5/2003 : 18
1/7/2003 : 38
and so on....

The problem with this is that some of the totals are wrong, I manually
counted the records that should have been in the grouping and it was
off. Also, I need it to show groups like this, with the groups
sequential:
Date Group Sumofitm_proc
1/3/2003 - 1/4/2003 23 (=sum of 1/3/2003 and 1/4/2003)
1/4/2003 - 1/5/2003 15 (=sum of 1/4/2003 and 1/5/2003)
1/5/2003 - 1/6/2003 18 (=sum of 1/5/2003 and 1/6/3003)

If you know how to do this, that would really help.

 

[Jay]

If you want to feed the query using a form, you replace the 2 in the
date group field with [Forms![FormName]![textboxorcombobox]

 

[peregenem]

I am not sure how to place the sum of items in the
buckets based off of user entry.

SQL was invented for data management. Report writers (I understand
Access ships with one) were invented to do this kind of formatting.
Don't get me wrong: this kind of exercise in pure SQL is a fun
challenge. In solving it this way, though, you're supposed to be left
with the impression that SQL is the wrong tool for the job.

If you table looks like this

CREATE TABLE Items (
itm_proc INTEGER NOT NULL,
itm_date DATETIME NOT NULL
)
;

and your 'sequence' table - the table of integers everyone has - looks
like this

CREATE TABLE [Sequence] (seq INTEGER)
;
INSERT INTO [Sequence] VALUES (1)
;
INSERT INTO [Sequence] VALUES (2)
;
INSERT INTO [Sequence] VALUES (reasonably high number here)
;

etc etc (or you may want to create the data in Excel and import it <g>)
then your procedure (Parameter Query) could like something like this:

CREATE PROCEDURE TestProc
interval_days INTEGER = 3)
AS
SELECT R1.report_date, SUM(Items.itm_proc)
FROM Items
INNER JOIN (
SELECT (
SELECT MIN(itm_date)
FROM Items
) - :interval_days + (S1.seq * :interval_days) AS report_date
FROM [Sequence] AS S1
WHERE (
SELECT MIN(itm_date)
FROM Items
) - :interval_days + (S1.seq * :interval_days) <= DATE()
) AS R1
ON (Items.itm_date
BETWEEN R1.report_date AND (R1.report_date + :interval_days))
GROUP BY R1.report_date

 

[Jay]

Thanks for the help, I have another post working on the same issues, so
I will try them both out. I'll post my results on Saturday. Thanks
again.