Weekly count

[Ron]

Hi,

I would like to produce a query that can display a weekly count of items
that are recorded on a daily basis in a base table (several items per day).

The weeks run from Friday to Thursday (for some reason!).


Any help would be appreciated - I have a brain block on this problem.

Regards,
Ron.

 

[Guest]

Hi,

I would like to produce a query that can display a weekly count of items
that are recorded on a daily basis in a base table (several items per day).

The weeks run from Friday to Thursday (for some reason!).


Any help would be appreciated - I have a brain block on this problem.

Regards,
Ron.

Ron,
You want to research the DatePart() function, to extract a number
representing the week from the date field in question. Then you can use an
aggregate query to total the number falling within the weeks. It's a
left-handed GROUP BY.
HTH,
Chris

 

[PC Datasheet]

1. Put the following expression in a blank field in your query:
WeekOfItemDateatePart("ww",[ItemDate],vbFriday)
Set Sort Ascending

2. Include field for the name of your items. Set Sort ascending

3. Include field for the name of your items again

4. Click on the Sigma (looks like capital E) button on the toolbar at the
top of thescreen

5. Under the field for the name of your items in 3, change Group By to
Count.

When you run this query, you should get for each week, a list of your items
and the number of each item recorder for the week where the week is defined
by Friday to Thursday.

 

[Ron]

Thanks guys,

I'm ok with sql but am not familiar with Access. Problem solved thanks to
your help!

Ron.

1. Put the following expression in a blank field in your query:
WeekOfItemDateatePart("ww",[ItemDate],vbFriday)
Set Sort Ascending

2. Include field for the name of your items. Set Sort ascending

3. Include field for the name of your items again

4. Click on the Sigma (looks like capital E) button on the toolbar at the
top of thescreen

5. Under the field for the name of your items in 3, change Group By to
Count.

When you run this query, you should get for each week, a list of your
items and the number of each item recorder for the week where the week is
defined by Friday to Thursday.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Hi,

I would like to produce a query that can display a weekly count of items
that are recorded on a daily basis in a base table (several items per
day).

The weeks run from Friday to Thursday (for some reason!).


Any help would be appreciated - I have a brain block on this problem.

Regards,
Ron.