Sorting by Distinct Month

[Chad]

I have a table that has a bunch of dates for about and I would like to write
a query that returns each DISTINCT month found in that table:

Here is the query that I have now, which sorts them alphabetically:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mmmm");

If I try to sort it on the actual data on in the field, like:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY tblCalendar].[Day;

It returns an error saying it conflicts with my DISTINCT statement. Does
anyone know of any ways around this?

Thanks,
Chad

 

[Chad]

Karl,
Thanks for the quick reply, unfortunately that returns unique values for
for month and year.

For Example:
If I have data spanning more than one yearthen it will return the month
more than once.


Is there a way to only return each Distinct Month?

Thanks,
Chad

Try this ---
SELECT Format([tblCalendar].[Day],"yyyymm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

I have a table that has a bunch of dates for about and I would like to write
a query that returns each DISTINCT month found in that table:

Here is the query that I have now, which sorts them alphabetically:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mmmm");

If I try to sort it on the actual data on in the field, like:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY tblCalendar].[Day;

It returns an error saying it conflicts with my DISTINCT statement. Does
anyone know of any ways around this?

Thanks,
Chad

 

[KARL DEWEY]

Try this ---
SELECT Format([tblCalendar].[Day],"mm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mm");

--
KARL DEWEY
Build a little - Test a little

Karl,
Thanks for the quick reply, unfortunately that returns unique values for
for month and year.

For Example:
If I have data spanning more than one yearthen it will return the month
more than once.


Is there a way to only return each Distinct Month?

Thanks,
Chad

Try this ---
SELECT Format([tblCalendar].[Day],"yyyymm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

I have a table that has a bunch of dates for about and I would like to write
a query that returns each DISTINCT month found in that table:

Here is the query that I have now, which sorts them alphabetically:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mmmm");

If I try to sort it on the actual data on in the field, like:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY tblCalendar].[Day;

It returns an error saying it conflicts with my DISTINCT statement. Does
anyone know of any ways around this?

Thanks,
Chad

 

[Chad]

Karl,
Thanks again for your quick response. Unfortunately this also returns
duplicate values if I have dates associated with the same month in any other
year. Sorry if I am being a pain. Let me know if you can think of anything
else.

Thanks,
Chad

Try this ---
SELECT Format([tblCalendar].[Day],"mm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mm");

--
KARL DEWEY
Build a little - Test a little

Karl,
Thanks for the quick reply, unfortunately that returns unique values for
for month and year.

For Example:
If I have data spanning more than one yearthen it will return the month
more than once.


Is there a way to only return each Distinct Month?

Thanks,
Chad

Try this ---
SELECT Format([tblCalendar].[Day],"yyyymm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I have a table that has a bunch of dates for about and I would like to write
a query that returns each DISTINCT month found in that table:

Here is the query that I have now, which sorts them alphabetically:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mmmm");

If I try to sort it on the actual data on in the field, like:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY tblCalendar].[Day;

It returns an error saying it conflicts with my DISTINCT statement. Does
anyone know of any ways around this?

Thanks,
Chad

 

[John Spencer]

SELECT TheMonth
FROM
(SELECT DISTINCT
Format([TheDay],"mmmm") AS TheMonth
, Format([TheDay],"mm") AS TheOrder
FROM CalendarTable) as T
ORDER BY TheOrder

Or do it with two queries.
SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
, Format(tblCalendar.Day,"mm") as TheOrder
FROM tblCalendar

Save that as qMonths and build another query on it.
SELECT qMonths.Month
FROM qMonths
ORDER BY theOrder

Of course you could always use
SELECT DISTINCT
Format([TheDay],"mmmm") AS TheMonth
, Format([TheDay],"mm") AS TheOrder
FROM CalendarTable
ORDER BY Format([TheDay],"mm")
And not display the TheOrder field on your report or in the form. If it is
the source for a listbox or a combobox, just HIDE the TheOrder column by
setting its column width to zero.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Karl,
Thanks again for your quick response. Unfortunately this also returns
duplicate values if I have dates associated with the same month in any
other
year. Sorry if I am being a pain. Let me know if you can think of
anything
else.

Thanks,
Chad

Try this ---
SELECT Format([tblCalendar].[Day],"mm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mm");

--
KARL DEWEY
Build a little - Test a little

Karl,
Thanks for the quick reply, unfortunately that returns unique values
for
for month and year.

For Example:
If I have data spanning more than one yearthen it will return the
month
more than once.


Is there a way to only return each Distinct Month?

Thanks,
Chad

:

Try this ---
SELECT Format([tblCalendar].[Day],"yyyymm"),
Format([tblCalendar].[Day],"mmmm") AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I have a table that has a bunch of dates for about and I would like
to write
a query that returns each DISTINCT month found in that table:

Here is the query that I have now, which sorts them alphabetically:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY Format([tblCalendar].[Day],"mmmm");

If I try to sort it on the actual data on in the field, like:

SELECT DISTINCT (Format([tblCalendar].[Day],"mmmm")) AS [Month]
FROM tblCalendar
ORDER BY tblCalendar].[Day;

It returns an error saying it conflicts with my DISTINCT statement.
Does
anyone know of any ways around this?

Thanks,
Chad