Sort month and Year

[Guest]

I have the data as followings:
Date of Service No of Service
4/12/2004 50
12/25/2004 60
2/20/2005 20
8/01/2005 25

I format the Date: Format([Date of Service], "mmm yy") AS DOS. However, it
shows:

Apr 04 50
Aug 05 25
Dec 04 60
Feb 05 20

I would like it to sort by month and year (i.e. Apr 04 then Dec 04 then Feb
05 and Aug 05), not by alphabetical as showed. Please help!

 

[Guest]

Try this

Select Format([Date of Service], "mmm yy") AS DOS, [No of Service] From
TableName Order By [Date of Service]

 

[Tom Ellison]

Dear Bltony:

When you "Format()" the [Date of Service] you get a text value, which will
sort as text, as you have seen.

If you include both the [Date of Service] (unformatted!) and the Formatted
value, you can then ORDER BY (sort) by the unformatted value, while
displaying the Formatted (text) value. That should do it for you.

If you have difficulty with this, please post the SQL of your query and I'll
show you how to do this.

Tom Ellison

 

[Guest]

I'm sorry I still have problem. Here is my SQL:

SELECT [Kept Data].[Name], Sum(IIf([Kept Data].[Name]<>"",1,0)) AS [Count],
[Kept Data].[Date Of Service], Format([Kept Data].[Date Of Service],"mmm yy")
AS DOS
FROM [Kept Data]
GROUP BY [Kept Data].[Name], [Kept Data].[Date Of Service]

Dear Bltony:

When you "Format()" the [Date of Service] you get a text value, which will
sort as text, as you have seen.

If you include both the [Date of Service] (unformatted!) and the Formatted
value, you can then ORDER BY (sort) by the unformatted value, while
displaying the Formatted (text) value. That should do it for you.

If you have difficulty with this, please post the SQL of your query and I'll
show you how to do this.

Tom Ellison

I have the data as followings:
Date of Service No of Service
4/12/2004 50
12/25/2004 60
2/20/2005 20
8/01/2005 25

I format the Date: Format([Date of Service], "mmm yy") AS DOS. However, it
shows:

Apr 04 50
Aug 05 25
Dec 04 60
Feb 05 20

I would like it to sort by month and year (i.e. Apr 04 then Dec 04 then Feb
05 and Aug 05), not by alphabetical as showed. Please help!

 

[Guest]

You are missing the Order By

SELECT [Kept Data].[Name], Sum(IIf([Kept Data].[Name]<>"",1,0)) AS [Count],
[Kept Data].[Date Of Service], Format([Kept Data].[Date Of Service],"mmm yy")
AS DOS
FROM [Kept Data]
GROUP BY [Kept Data].[Name], [Kept Data].[Date Of Service]
Order By [Kept Data].[Date Of Service]

--
I hope that helped
Good luck

I'm sorry I still have problem. Here is my SQL:

SELECT [Kept Data].[Name], Sum(IIf([Kept Data].[Name]<>"",1,0)) AS [Count],
[Kept Data].[Date Of Service], Format([Kept Data].[Date Of Service],"mmm yy")
AS DOS
FROM [Kept Data]
GROUP BY [Kept Data].[Name], [Kept Data].[Date Of Service]

Dear Bltony:

When you "Format()" the [Date of Service] you get a text value, which will
sort as text, as you have seen.

If you include both the [Date of Service] (unformatted!) and the Formatted
value, you can then ORDER BY (sort) by the unformatted value, while
displaying the Formatted (text) value. That should do it for you.

If you have difficulty with this, please post the SQL of your query and I'll
show you how to do this.

Tom Ellison

I have the data as followings:
Date of Service No of Service
4/12/2004 50
12/25/2004 60
2/20/2005 20
8/01/2005 25

I format the Date: Format([Date of Service], "mmm yy") AS DOS. However, it
shows:

Apr 04 50
Aug 05 25
Dec 04 60
Feb 05 20

I would like it to sort by month and year (i.e. Apr 04 then Dec 04 then Feb
05 and Aug 05), not by alphabetical as showed. Please help!

 

[Tom Ellison]

Dear Bltony:

I'm going to say the same thing (pretty much) as Ofer, and consistent with
what I posted previously. You can order this query by the [Date of Service]
unformatted.

One thing I don't understand. The output you specified doesn't include
[Name]. Using what I show below, what would happen if you eliminate the
[Name] from that? Or is that what you want?

SELECT [Name], Sum(IIf([Name]<>"",1,0)) AS [Count],
[Date Of Service], Format([Date Of Service],"mmm yy") AS DOS
FROM [Kept Data]
GROUP BY [Name], [Date Of Service]
ORDER BY [Date Of Service]

It is also quite confusing that you are COUNTing (summing 0 or 1) only those
rows that have a [Name] entered. Also, is [Name] ever NULL? Do you want to
count those?

Nothing you ask seems to be difficult, but I just don't know what all this
is about.

Tom Ellison

I'm sorry I still have problem. Here is my SQL:

SELECT [Kept Data].[Name], Sum(IIf([Kept Data].[Name]<>"",1,0)) AS [Count],
[Kept Data].[Date Of Service], Format([Kept Data].[Date Of Service],"mmm yy")
AS DOS
FROM [Kept Data]
GROUP BY [Kept Data].[Name], [Kept Data].[Date Of Service]

Dear Bltony:

When you "Format()" the [Date of Service] you get a text value, which will
sort as text, as you have seen.

If you include both the [Date of Service] (unformatted!) and the Formatted
value, you can then ORDER BY (sort) by the unformatted value, while
displaying the Formatted (text) value. That should do it for you.

If you have difficulty with this, please post the SQL of your query and I'll
show you how to do this.

Tom Ellison

I have the data as followings:
Date of Service No of Service
4/12/2004 50
12/25/2004 60
2/20/2005 20
8/01/2005 25

I format the Date: Format([Date of Service], "mmm yy") AS DOS. However, it
shows:

Apr 04 50
Aug 05 25
Dec 04 60
Feb 05 20

I would like it to sort by month and year (i.e. Apr 04 then Dec 04

then
Feb

05 and Aug 05), not by alphabetical as showed. Please help!

 

[Guest]

You are right, I missed the "order by". Thanks.

My database included names and I need to sum the names to get the total. If
I didn't include the name, then I won't have the "No of Service". Anyway,
many thanks.

Dear Bltony:

I'm going to say the same thing (pretty much) as Ofer, and consistent with
what I posted previously. You can order this query by the [Date of Service]
unformatted.

One thing I don't understand. The output you specified doesn't include
[Name]. Using what I show below, what would happen if you eliminate the
[Name] from that? Or is that what you want?

SELECT [Name], Sum(IIf([Name]<>"",1,0)) AS [Count],
[Date Of Service], Format([Date Of Service],"mmm yy") AS DOS
FROM [Kept Data]
GROUP BY [Name], [Date Of Service]
ORDER BY [Date Of Service]

It is also quite confusing that you are COUNTing (summing 0 or 1) only those
rows that have a [Name] entered. Also, is [Name] ever NULL? Do you want to
count those?

Nothing you ask seems to be difficult, but I just don't know what all this
is about.

Tom Ellison

I'm sorry I still have problem. Here is my SQL:

SELECT [Kept Data].[Name], Sum(IIf([Kept Data].[Name]<>"",1,0)) AS [Count],
[Kept Data].[Date Of Service], Format([Kept Data].[Date Of Service],"mmm yy")
AS DOS
FROM [Kept Data]
GROUP BY [Kept Data].[Name], [Kept Data].[Date Of Service]

Dear Bltony:

When you "Format()" the [Date of Service] you get a text value, which will
sort as text, as you have seen.

If you include both the [Date of Service] (unformatted!) and the Formatted
value, you can then ORDER BY (sort) by the unformatted value, while
displaying the Formatted (text) value. That should do it for you.

If you have difficulty with this, please post the SQL of your query and I'll
show you how to do this.

Tom Ellison


I have the data as followings:
Date of Service No of Service
4/12/2004 50
12/25/2004 60
2/20/2005 20
8/01/2005 25

I format the Date: Format([Date of Service], "mmm yy") AS DOS. However, it
shows:

Apr 04 50
Aug 05 25
Dec 04 60
Feb 05 20

I would like it to sort by month and year (i.e. Apr 04 then Dec 04 then
Feb
05 and Aug 05), not by alphabetical as showed. Please help!

 

[Tom Ellison]

Dear Bltony:

You can "sum the names" (that is, effectively, count those that have names,
according to your code) without GROUPing by them.

I was suggesting something like this:

SELECT Sum(IIf([Name]<>"",1,0)) AS [Count],
[Date Of Service], Format([Date Of Service],"mmm yy") AS DOS
FROM [Kept Data]
GROUP BY [Date Of Service]
ORDER BY [Date Of Service]

If you display the Name, you must group by it. If you use Name in an
aggregate function, you do NOT need to GROUP BY it. As you will see, the
result is very different, giving the Count for each Date Of Service, without
respect to Name.

Please let me know if this is helpful, and if you need any additional help.

Tom Ellison

You are right, I missed the "order by". Thanks.

My database included names and I need to sum the names to get the total. If
I didn't include the name, then I won't have the "No of Service". Anyway,
many thanks.

Dear Bltony:

I'm going to say the same thing (pretty much) as Ofer, and consistent with
what I posted previously. You can order this query by the [Date of Service]
unformatted.

One thing I don't understand. The output you specified doesn't include
[Name]. Using what I show below, what would happen if you eliminate the
[Name] from that? Or is that what you want?

SELECT [Name], Sum(IIf([Name]<>"",1,0)) AS [Count],
[Date Of Service], Format([Date Of Service],"mmm yy") AS DOS
FROM [Kept Data]
GROUP BY [Name], [Date Of Service]
ORDER BY [Date Of Service]

It is also quite confusing that you are COUNTing (summing 0 or 1) only those
rows that have a [Name] entered. Also, is [Name] ever NULL? Do you want to
count those?

Nothing you ask seems to be difficult, but I just don't know what all this
is about.

Tom Ellison

I'm sorry I still have problem. Here is my SQL:

SELECT [Kept Data].[Name], Sum(IIf([Kept Data].[Name]<>"",1,0)) AS [Count],
[Kept Data].[Date Of Service], Format([Kept Data].[Date Of

Service],"mmm
yy")

AS DOS
FROM [Kept Data]
GROUP BY [Kept Data].[Name], [Kept Data].[Date Of Service]



:

Dear Bltony:

When you "Format()" the [Date of Service] you get a text value,

which
will

sort as text, as you have seen.

If you include both the [Date of Service] (unformatted!) and the Formatted
value, you can then ORDER BY (sort) by the unformatted value, while
displaying the Formatted (text) value. That should do it for you.

If you have difficulty with this, please post the SQL of your query

and
I'll

show you how to do this.

Tom Ellison


I have the data as followings:
Date of Service No of Service
4/12/2004 50
12/25/2004 60
2/20/2005 20
8/01/2005 25

I format the Date: Format([Date of Service], "mmm yy") AS DOS. However, it
shows:

Apr 04 50
Aug 05 25
Dec 04 60
Feb 05 20

I would like it to sort by month and year (i.e. Apr 04 then Dec 04 then
Feb
05 and Aug 05), not by alphabetical as showed. Please help!

 

[Tom Ellison]

Dear Bltony:

For what it's worth, to me a more natural way to write this would be:

SELECT FORMAT([Date Of Service], "mmm yy") AS DOS,
COUNT(*) AS [Count]
FROM [Kept Data]
WHERE [Name] <> ""
GROUP BY [Date Of Service]
ORDER BY [Date Of Service]

Also, if there could be NULLs in [Name]:

SELECT FORMAT([Date Of Service], "mmm yy") AS DOS,
COUNT(*) AS [Count]
FROM [Kept Data]
WHERE Nz([Name], "") <> ""
GROUP BY [Date Of Service]
ORDER BY [Date Of Service]

You could put Name back in as a column here, if that's what you need.

Tom Ellison

You are right, I missed the "order by". Thanks.

My database included names and I need to sum the names to get the total. If
I didn't include the name, then I won't have the "No of Service". Anyway,
many thanks.

Dear Bltony:

I'm going to say the same thing (pretty much) as Ofer, and consistent with
what I posted previously. You can order this query by the [Date of Service]
unformatted.

One thing I don't understand. The output you specified doesn't include
[Name]. Using what I show below, what would happen if you eliminate the
[Name] from that? Or is that what you want?

SELECT [Name], Sum(IIf([Name]<>"",1,0)) AS [Count],
[Date Of Service], Format([Date Of Service],"mmm yy") AS DOS
FROM [Kept Data]
GROUP BY [Name], [Date Of Service]
ORDER BY [Date Of Service]

It is also quite confusing that you are COUNTing (summing 0 or 1) only those
rows that have a [Name] entered. Also, is [Name] ever NULL? Do you want to
count those?

Nothing you ask seems to be difficult, but I just don't know what all this
is about.

Tom Ellison

I'm sorry I still have problem. Here is my SQL:

SELECT [Kept Data].[Name], Sum(IIf([Kept Data].[Name]<>"",1,0)) AS [Count],
[Kept Data].[Date Of Service], Format([Kept Data].[Date Of

Service],"mmm
yy")

AS DOS
FROM [Kept Data]
GROUP BY [Kept Data].[Name], [Kept Data].[Date Of Service]



:

Dear Bltony:

When you "Format()" the [Date of Service] you get a text value,

which
will

sort as text, as you have seen.

If you include both the [Date of Service] (unformatted!) and the Formatted
value, you can then ORDER BY (sort) by the unformatted value, while
displaying the Formatted (text) value. That should do it for you.

If you have difficulty with this, please post the SQL of your query

and
I'll

show you how to do this.

Tom Ellison


I have the data as followings:
Date of Service No of Service
4/12/2004 50
12/25/2004 60
2/20/2005 20
8/01/2005 25

I format the Date: Format([Date of Service], "mmm yy") AS DOS. However, it
shows:

Apr 04 50
Aug 05 25
Dec 04 60
Feb 05 20

I would like it to sort by month and year (i.e. Apr 04 then Dec 04 then
Feb
05 and Aug 05), not by alphabetical as showed. Please help!