Group only by certain day

[instereo911 via AccessMonster.com]

Good afternoon,

I have a query set up where i want the results to show only Thursdays within
a Date Field.
I have no idea how to accomplish this. I went through the datepart and date
different functions but didn't exactly understand em'. Can anyone help me out
please.. Sorry to bug everyone.. Hopefully this is an easy one.

My query is below:


SELECT [A-LargeGroupTotalInventory].Date, Count([A-LargeGroupTotalInventory].
AgedDays) AS CountOfAgedDays
FROM [A-LargeGroupTotalInventory]
GROUP BY [A-LargeGroupTotalInventory].Date;


Thank you everyone.

 

[Rob Parker]

SELECT [A-LargeGroupTotalInventory].Date,
Count([A-LargeGroupTotalInventory].AgedDays) AS CountOfAgedDays
FROM [A-LargeGroupTotalInventory]
GROUP BY [A-LargeGroupTotalInventory].Date
WHERE Weekday([Date])=5;

Note that Date is a bad choice for a field name; it's a reserved word, and
you may have problems when Access confuses this with the built-in Date
function.

HTH,

Rob

 

[Conan Kelly]

instereo911,

How about something like this:

SELECT [A-LargeGroupTotalInventory].Date,
Count([A-LargeGroupTotalInventory].
AgedDays) AS CountOfAgedDays
FROM [A-LargeGroupTotalInventory]
WHERE Weekday([A-LargeGroupTotalInventory].Date) = 5
GROUP BY [A-LargeGroupTotalInventory].Date;

I have not tested that, but that should select only dates that fall on
Thursdays.

HTH,

Conan

 

[instereo911 via AccessMonster.com]

Worked perfect. Thanks conan !!! I am glad it was something easy

instereo911,

How about something like this:

SELECT [A-LargeGroupTotalInventory].Date,
Count([A-LargeGroupTotalInventory].
AgedDays) AS CountOfAgedDays
FROM [A-LargeGroupTotalInventory]
WHERE Weekday([A-LargeGroupTotalInventory].Date) = 5
GROUP BY [A-LargeGroupTotalInventory].Date;

I have not tested that, but that should select only dates that fall on
Thursdays.

HTH,

Conan

Good afternoon,

[quoted text clipped - 16 lines]

Thank you everyone.

 

[Rob Parker]

Slight oops ...

The WHERE clause must appear before the GROUP BY clause.

Or, alternatively, change WHERE to HAVING and leave after the GROUP BY.

Rob

SELECT [A-LargeGroupTotalInventory].Date,
Count([A-LargeGroupTotalInventory].AgedDays) AS CountOfAgedDays
FROM [A-LargeGroupTotalInventory]
GROUP BY [A-LargeGroupTotalInventory].Date
WHERE Weekday([Date])=5;

Note that Date is a bad choice for a field name; it's a reserved word, and
you may have problems when Access confuses this with the built-in Date
function.

HTH,

Rob

Good afternoon,

I have a query set up where i want the results to show only Thursdays
within
a Date Field.
I have no idea how to accomplish this. I went through the datepart and
date
different functions but didn't exactly understand em'. Can anyone help me
out
please.. Sorry to bug everyone.. Hopefully this is an easy one.

My query is below:


SELECT [A-LargeGroupTotalInventory].Date,
Count([A-LargeGroupTotalInventory].
AgedDays) AS CountOfAgedDays
FROM [A-LargeGroupTotalInventory]
GROUP BY [A-LargeGroupTotalInventory].Date;


Thank you everyone.