Query Help Please! Is a Weekly data format Possible?

  • Thread starter Thread starter Marie James via AccessMonster.com
  • Start date Start date
M

Marie James via AccessMonster.com

Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED) AS
SumOfRECEIVED
FROM Management INNER JOIN Queue ON Management.queue = Queue.Queue
WHERE (((Queue.Group) Like "*exl*"))
GROUP BY Management.Date, Management.queue
HAVING (((Management.Date) Like "*2005*"));
and this gives me the results I need everyday for the entire 2005 year.
Is there a way to have this query group the data in a weekly format for the
entire 2005 year?
Any assistance is Greatly Appreciated!

Kind Regards,

Marie James
 
Dear Marie:

By "weekly" format do you mean having one row per week instead of one row
per day? If so:

SELECT DatePart("ww", Management.Date), Management.queue,
Sum(Management.RECEIVED) AS SumOfRECEIVED
FROM Management
INNER JOIN Queue
ON Management.queue = Queue.Queue
WHERE [Queue.Group] Like "*exl*"
GROUP BY DatePart("ww", Management.Date), Management.queue
HAVING DatePart("yyyy", [Management.Date] = 2005

Please let me know if this helped, and if I can be of any other assistance.

The above is for a Sunday to Saturday week. It can be set to another basis.
See the online help for DatePart.

Tom Ellison
 
What is the data type and format of Management.Date?
It appears from you post it is a text field, but I need to know if it is
"yyyymmdd", or what?
 
That should do if Management.Date is a date/time field. Based on the OP, It
appears to be a text field. That is why I asked for the format. It will
take the DateSerial function to make it into a date.
Also, I am suprised it is working at all with a field named Date. It should
be renamed or in brackets. It will have to be in brackets to use the date
function, or I think Access is going to get really confused.

Tom Ellison said:
Dear Marie:

By "weekly" format do you mean having one row per week instead of one row
per day? If so:

SELECT DatePart("ww", Management.Date), Management.queue,
Sum(Management.RECEIVED) AS SumOfRECEIVED
FROM Management
INNER JOIN Queue
ON Management.queue = Queue.Queue
WHERE [Queue.Group] Like "*exl*"
GROUP BY DatePart("ww", Management.Date), Management.queue
HAVING DatePart("yyyy", [Management.Date] = 2005

Please let me know if this helped, and if I can be of any other assistance.

The above is for a Sunday to Saturday week. It can be set to another basis.
See the online help for DatePart.

Tom Ellison


Marie James via AccessMonster.com said:
Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED) AS
SumOfRECEIVED
FROM Management INNER JOIN Queue ON Management.queue = Queue.Queue
WHERE (((Queue.Group) Like "*exl*"))
GROUP BY Management.Date, Management.queue
HAVING (((Management.Date) Like "*2005*"));
and this gives me the results I need everyday for the entire 2005 year.
Is there a way to have this query group the data in a weekly format for
the
entire 2005 year?
Any assistance is Greatly Appreciated!

Kind Regards,

Marie James
Click to expand...
Click to expand...
 
Dear Klatuu:

It is not necessarily true that the query shows that the Management Date
must be a string. The statement:

(((Management.Date) Like "*2005*"));

works for strings or dates the same. The Like operatore causes its operands
to be converted to strings, even if it is a date. Since neither day nor
month can be 4 digits, this can only match the year portion.

Also, the DatePart function I used will work on a date that is in string
form, within limits. Many string formats for dates can be recognized
automatically.

I suggest that there is not sufficient evidence to conclude that the
original post can only mean it is a string. And, if it is, this does not
mean my solution won't work.

I was fully aware of this as I posted. But thanks for checking up on me.

Tom Ellison



Klatuu said:
That should do if Management.Date is a date/time field. Based on the OP,
It
appears to be a text field. That is why I asked for the format. It will
take the DateSerial function to make it into a date.
Also, I am suprised it is working at all with a field named Date. It
should
be renamed or in brackets. It will have to be in brackets to use the date
function, or I think Access is going to get really confused.

Tom Ellison said:
Dear Marie:

By "weekly" format do you mean having one row per week instead of one row
per day? If so:

SELECT DatePart("ww", Management.Date), Management.queue,
Sum(Management.RECEIVED) AS SumOfRECEIVED
FROM Management
INNER JOIN Queue
ON Management.queue = Queue.Queue
WHERE [Queue.Group] Like "*exl*"
GROUP BY DatePart("ww", Management.Date), Management.queue
HAVING DatePart("yyyy", [Management.Date] = 2005

Please let me know if this helped, and if I can be of any other
assistance.

The above is for a Sunday to Saturday week. It can be set to another
basis.
See the online help for DatePart.

Tom Ellison


Marie James via AccessMonster.com said:
Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED) AS
SumOfRECEIVED
FROM Management INNER JOIN Queue ON Management.queue = Queue.Queue
WHERE (((Queue.Group) Like "*exl*"))
GROUP BY Management.Date, Management.queue
HAVING (((Management.Date) Like "*2005*"));
and this gives me the results I need everyday for the entire 2005 year.
Is there a way to have this query group the data in a weekly format for
the
entire 2005 year?
Any assistance is Greatly Appreciated!

Kind Regards,

Marie James
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the reminder, Tom. I had forgotten about the type conversion for
the Like operator. It is true that neither month nor day can be four digits.
As long is the application is using USA date formatting, only the year would
match. I am not positive, but I think it is possible, that using Euro date
formatting, 2005 could possibly match May 20.
As to the Datepart, it required delimiters. datepart("m","05252006") will
return and error 13 (Type Mismatch)

Tom Ellison said:
Dear Klatuu:

It is not necessarily true that the query shows that the Management Date
must be a string. The statement:

(((Management.Date) Like "*2005*"));

works for strings or dates the same. The Like operatore causes its operands
to be converted to strings, even if it is a date. Since neither day nor
month can be 4 digits, this can only match the year portion.

Also, the DatePart function I used will work on a date that is in string
form, within limits. Many string formats for dates can be recognized
automatically.

I suggest that there is not sufficient evidence to conclude that the
original post can only mean it is a string. And, if it is, this does not
mean my solution won't work.

I was fully aware of this as I posted. But thanks for checking up on me.

Tom Ellison



Klatuu said:
That should do if Management.Date is a date/time field. Based on the OP,
It
appears to be a text field. That is why I asked for the format. It will
take the DateSerial function to make it into a date.
Also, I am suprised it is working at all with a field named Date. It
should
be renamed or in brackets. It will have to be in brackets to use the date
function, or I think Access is going to get really confused.

Tom Ellison said:
Dear Marie:

By "weekly" format do you mean having one row per week instead of one row
per day? If so:

SELECT DatePart("ww", Management.Date), Management.queue,
Sum(Management.RECEIVED) AS SumOfRECEIVED
FROM Management
INNER JOIN Queue
ON Management.queue = Queue.Queue
WHERE [Queue.Group] Like "*exl*"
GROUP BY DatePart("ww", Management.Date), Management.queue
HAVING DatePart("yyyy", [Management.Date] = 2005

Please let me know if this helped, and if I can be of any other
assistance.

The above is for a Sunday to Saturday week. It can be set to another
basis.
See the online help for DatePart.

Tom Ellison


Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED) AS
SumOfRECEIVED
FROM Management INNER JOIN Queue ON Management.queue = Queue.Queue
WHERE (((Queue.Group) Like "*exl*"))
GROUP BY Management.Date, Management.queue
HAVING (((Management.Date) Like "*2005*"));
and this gives me the results I need everyday for the entire 2005 year.
Is there a way to have this query group the data in a weekly format for
the
entire 2005 year?
Any assistance is Greatly Appreciated!

Kind Regards,

Marie James
Click to expand...
Click to expand...
Click to expand...
 
Dear Klatuu:

Yes, and DatePart("ww", "11/8/2006") works fine. Depends on the format of
the text. As I said,

"Many string formats for dates can be recognized automatically."

There are a number of string formats that work, and some that don't.

Tom Ellison


Klatuu said:
Thanks for the reminder, Tom. I had forgotten about the type conversion
for
the Like operator. It is true that neither month nor day can be four
digits.
As long is the application is using USA date formatting, only the year
would
match. I am not positive, but I think it is possible, that using Euro
date
formatting, 2005 could possibly match May 20.
As to the Datepart, it required delimiters. datepart("m","05252006") will
return and error 13 (Type Mismatch)

Tom Ellison said:
Dear Klatuu:

It is not necessarily true that the query shows that the Management Date
must be a string. The statement:

(((Management.Date) Like "*2005*"));

works for strings or dates the same. The Like operatore causes its
operands
to be converted to strings, even if it is a date. Since neither day nor
month can be 4 digits, this can only match the year portion.

Also, the DatePart function I used will work on a date that is in string
form, within limits. Many string formats for dates can be recognized
automatically.

I suggest that there is not sufficient evidence to conclude that the
original post can only mean it is a string. And, if it is, this does not
mean my solution won't work.

I was fully aware of this as I posted. But thanks for checking up on me.

Tom Ellison



Klatuu said:
That should do if Management.Date is a date/time field. Based on the
OP,
It
appears to be a text field. That is why I asked for the format. It
will
take the DateSerial function to make it into a date.
Also, I am suprised it is working at all with a field named Date. It
should
be renamed or in brackets. It will have to be in brackets to use the
date
function, or I think Access is going to get really confused.

:

Dear Marie:

By "weekly" format do you mean having one row per week instead of one
row
per day? If so:

SELECT DatePart("ww", Management.Date), Management.queue,
Sum(Management.RECEIVED) AS SumOfRECEIVED
FROM Management
INNER JOIN Queue
ON Management.queue = Queue.Queue
WHERE [Queue.Group] Like "*exl*"
GROUP BY DatePart("ww", Management.Date), Management.queue
HAVING DatePart("yyyy", [Management.Date] = 2005

Please let me know if this helped, and if I can be of any other
assistance.

The above is for a Sunday to Saturday week. It can be set to another
basis.
See the online help for DatePart.

Tom Ellison


Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED)
AS
SumOfRECEIVED
FROM Management INNER JOIN Queue ON Management.queue = Queue.Queue
WHERE (((Queue.Group) Like "*exl*"))
GROUP BY Management.Date, Management.queue
HAVING (((Management.Date) Like "*2005*"));
and this gives me the results I need everyday for the entire 2005
year.
Is there a way to have this query group the data in a weekly format
for
the
entire 2005 year?
Any assistance is Greatly Appreciated!

Kind Regards,

Marie James
Click to expand...
Click to expand...
Click to expand...
 
Thanks Tom for the Advice I am going to go ahead and try it here shortly. Yes
for the data I need I wanted the query to return a total sum for Sunday thru
Saturday by week for the entire 2005 year. Here is what my query looks like
before I try to implement your hints.

Date bucket SumOfRECEIVED
1/1/2005 w Change PL 155
1/1/2005 w Change Pre 8
1/1/2005 w Change Pri 410
1/1/2005 w Change Pr 1
1/1/2005 e Change Pl 685
1/1/2005 e Change Pre 43
1/1/2005 e Change Pri 880
1/1/2005 e Change Pr 0
1/2/2005 w Change PL 239
1/2/2005 w Change Pre 35
1/2/2005 w Change Pri 572
1/2/2005 w Change Pr 1
1/2/2005 e Change Pl 938
1/2/2005 e Change Pre 64
1/2/2005 e Change Pri 1117
so in order to get a weekly total for Sunday through Sat from this I had to
transfer to excel and write a formula.
I was thinking wait a minute I should not have to go through all that. Right?
Will let you know how things turn out thank you so much!

Regards M.James
Tom said:
Dear Marie:

By "weekly" format do you mean having one row per week instead of one row
per day? If so:

SELECT DatePart("ww", Management.Date), Management.queue,
Sum(Management.RECEIVED) AS SumOfRECEIVED
FROM Management
INNER JOIN Queue
ON Management.queue = Queue.Queue
WHERE [Queue.Group] Like "*exl*"
GROUP BY DatePart("ww", Management.Date), Management.queue
HAVING DatePart("yyyy", [Management.Date] = 2005

Please let me know if this helped, and if I can be of any other assistance.

The above is for a Sunday to Saturday week. It can be set to another basis.
See the online help for DatePart.

Tom Ellison
Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED) AS
Click to expand...
[quoted text clipped - 12 lines]
Marie James
Click to expand...
Click to expand...
 
Dear Klatuu,

I really appreciate the feedback and comments you have provided. I have
posted an example of my current query results. Will reply back as soon as I
have had a chance to try out the tips you all have posted. Thank you so much
for your Time & Support!

Kind Regards,
MJ
What is the data type and format of Management.Date?
It appears from you post it is a text field, but I need to know if it is
"yyyymmdd", or what?
Hello! I have a query built like this:
SELECT Management.Date, Management.queue, Sum(Management.RECEIVED) AS
Click to expand...
[quoted text clipped - 11 lines]
Marie James
Click to expand...
Click to expand...
 
Back
Top