Query Help Needed

[ipower2 via AccessMonster.com]

I have a database that tracks statistics for my department. I need to be
able to compare the current years and previous years totals. I also need to
be able to track them by month.

I believe that a cross tab query would do the job, however I am not having
any luck in making it work.

my table is like this

monthdate field(formatted as MMMM YYYY)
field1,number
field2,number
field3,number
field4,number
field5,number
field6,number
field7,number

The numbers are obtained from our database, however they are raw numbers, we
have to format them into reports. I have created the database to use the
numbers and hopefully make charts and reports using these numbers.

Do I just need to use a separate query that uses the previous months totals
and combine it with a current query for the current's months totals? Any
help will be appreciated thanks.

 

[KARL DEWEY]

Try this substituting yourtable and field names ---
TRANSFORM Sum([Change Requests].YourField) AS SumOfYourField
SELECT Format([Date open],"mmmm") AS [Month]
FROM [Change Requests]
GROUP BY Format([Date open],"mm"), Format([Date open],"mmmm")
PIVOT Format([Date open],"yyyy");

 

[John W. Vinson]

I have a database that tracks statistics for my department. I need to be
able to compare the current years and previous years totals. I also need to
be able to track them by month.

I believe that a cross tab query would do the job, however I am not having
any luck in making it work.

my table is like this

monthdate field(formatted as MMMM YYYY)

Is this a Date/Time field or a text field? Either can be used but it makes a
difference!

field1,number
field2,number
field3,number
field4,number
field5,number
field6,number
field7,number

The numbers are obtained from our database, however they are raw numbers, we
have to format them into reports. I have created the database to use the
numbers and hopefully make charts and reports using these numbers.

Do I just need to use a separate query that uses the previous months totals
and combine it with a current query for the current's months totals? Any
help will be appreciated thanks.

You can use a single query with a "self join", adding the table to the query
grid twice and using the JOIN clause in SQL view to join this month's results
to last month's results. Just how you do so depends on the nature of the
monthdate field.

John W. Vinson [MVP]

 

[ipower2 via AccessMonster.com]

the monthdate field is a Date/Time field, but it is formatted as MMMM YYYY.

I have a database that tracks statistics for my department. I need to be
able to compare the current years and previous years totals. I also need to

[quoted text clipped - 6 lines]

monthdate field(formatted as MMMM YYYY)

Is this a Date/Time field or a text field? Either can be used but it makes a
difference!

field1,number
field2,number

[quoted text clipped - 11 lines]

and combine it with a current query for the current's months totals? Any
help will be appreciated thanks.

You can use a single query with a "self join", adding the table to the query
grid twice and using the JOIN clause in SQL view to join this month's results
to last month's results. Just how you do so depends on the nature of the
monthdate field.

John W. Vinson [MVP]

 

[ipower2 via AccessMonster.com]

Thanks, I will try this and let you know.

Try this substituting yourtable and field names ---
TRANSFORM Sum([Change Requests].YourField) AS SumOfYourField
SELECT Format([Date open],"mmmm") AS [Month]
FROM [Change Requests]
GROUP BY Format([Date open],"mm"), Format([Date open],"mmmm")
PIVOT Format([Date open],"yyyy");

I have a database that tracks statistics for my department. I need to be
able to compare the current years and previous years totals. I also need to

[quoted text clipped - 21 lines]

and combine it with a current query for the current's months totals? Any
help will be appreciated thanks.

 

[John W. Vinson]

the monthdate field is a Date/Time field, but it is formatted as MMMM YYYY.

The format is irrelevant - it does not affect what's stored, only what you see
on the screen or page. The actual date/time field contains a floating point
count of days and fractions of a day (times) since midnight, December 30,
1899.

Assuming that each monthdate value contains the first day of the month, you
could get a comparison of a selected month's data to the previous year's data
for that month using a self join query like:

SELECT Thisyear.monthdate AS ThisIsNow, Thisyear.field1 AS Current1,
Thisyear.field2 AS current2, ...,
Lastyear.monthdate AS ThisWasThen, Lastyear.field1 AS Prior1, Lastyear.field2
AS Prior2, ...
FROM yourtable As Thisyear
INNER JOIN yourtable AS Lastyear
ON lastyear.monthdate = DateAdd("yyyy", -1, thisyear.monthdate)
WHERE thisyear.monthdate = DateSerial([Enter current year:], [Enter current
month number:], 1)


John W. Vinson [MVP]

 

[ipower2 via AccessMonster.com]

Okay, I tried it, but it is not exactly what I am looking for...

When I did this, it creates a query and uses the month as rows and the years
as column headings, which is not what I was looking for.

I need the years to be the column headings and the fields to be row headings
but the same fields that are the row headings will also be the values
respectively.

I beginnning to think that it is not possible...I would like a query that
does something like this...
MonthYear1 MonthYear2
field1 some value some value
field2 another value another value again
field3 etc.... etc....
field4
field5
field6
fieled7

Any alternatives would be helpful, if this cant be done.

Try this substituting yourtable and field names ---
TRANSFORM Sum([Change Requests].YourField) AS SumOfYourField
SELECT Format([Date open],"mmmm") AS [Month]
FROM [Change Requests]
GROUP BY Format([Date open],"mm"), Format([Date open],"mmmm")
PIVOT Format([Date open],"yyyy");

I have a database that tracks statistics for my department. I need to be
able to compare the current years and previous years totals. I also need to

[quoted text clipped - 21 lines]

and combine it with a current query for the current's months totals? Any
help will be appreciated thanks.

 

[ipower2 via AccessMonster.com]

Thank you John,

This worked great. I substituted my field names and it works fine. It gives
me the number totals which I am looking for, however this does not make
columns/rows like I would have liked but this does work for th dates and now
I can use the query as the source for a report which I can format the way
that I want.


Thanks a bunch. You have made my work easier.

Ivan

the monthdate field is a Date/Time field, but it is formatted as MMMM YYYY.

The format is irrelevant - it does not affect what's stored, only what you see
on the screen or page. The actual date/time field contains a floating point
count of days and fractions of a day (times) since midnight, December 30,
1899.

Assuming that each monthdate value contains the first day of the month, you
could get a comparison of a selected month's data to the previous year's data
for that month using a self join query like:

SELECT Thisyear.monthdate AS ThisIsNow, Thisyear.field1 AS Current1,
Thisyear.field2 AS current2, ...,
Lastyear.monthdate AS ThisWasThen, Lastyear.field1 AS Prior1, Lastyear.field2
AS Prior2, ...
FROM yourtable As Thisyear
INNER JOIN yourtable AS Lastyear
ON lastyear.monthdate = DateAdd("yyyy", -1, thisyear.monthdate)
WHERE thisyear.monthdate = DateSerial([Enter current year:], [Enter current
month number:], 1)

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you John,

This worked great. I substituted my field names and it works fine. It gives
me the number totals which I am looking for, however this does not make
columns/rows like I would have liked but this does work for th dates and now
I can use the query as the source for a report which I can format the way
that I want.

Exactly. Queries provide the raw information; to lay it out neatly on a page
use a Report.

Thanks a bunch. You have made my work easier.

Glad to have been of assistance!

John W. Vinson [MVP]

 

[KARL DEWEY]

Post some sample data with table and field names. Also show how that same
data would be displayed in your desired results.
--
KARL DEWEY
Build a little - Test a little

Okay, I tried it, but it is not exactly what I am looking for...

When I did this, it creates a query and uses the month as rows and the years
as column headings, which is not what I was looking for.

I need the years to be the column headings and the fields to be row headings
but the same fields that are the row headings will also be the values
respectively.

I beginnning to think that it is not possible...I would like a query that
does something like this...
MonthYear1 MonthYear2
field1 some value some value
field2 another value another value again
field3 etc.... etc....
field4
field5
field6
fieled7

Any alternatives would be helpful, if this cant be done.

Try this substituting yourtable and field names ---
TRANSFORM Sum([Change Requests].YourField) AS SumOfYourField
SELECT Format([Date open],"mmmm") AS [Month]
FROM [Change Requests]
GROUP BY Format([Date open],"mm"), Format([Date open],"mmmm")
PIVOT Format([Date open],"yyyy");

I have a database that tracks statistics for my department. I need to be
able to compare the current years and previous years totals. I also need to

[quoted text clipped - 21 lines]

and combine it with a current query for the current's months totals? Any
help will be appreciated thanks.

 

[ipower2 via AccessMonster.com]

Okay...I thought I could be a big shot and now Im stuck again...

Using the same sql command how would I modify it so that it does not break
the time frames by month but rather uses timeframes like from the 1/1/2007 to
present or alternatively a specific month?

Thank you John,

[quoted text clipped - 3 lines]

I can use the query as the source for a report which I can format the way
that I want.

Exactly. Queries provide the raw information; to lay it out neatly on a page
use a Report.

Thanks a bunch. You have made my work easier.

Glad to have been of assistance!

John W. Vinson [MVP]

 

[John W. Vinson]

Okay...I thought I could be a big shot and now Im stuck again...

Using the same sql command how would I modify it so that it does not break
the time frames by month but rather uses timeframes like from the 1/1/2007 to
present or alternatively a specific month?

You could use a criterion

WHERE thisyear.monthdate >= [Enter start date:] AND thisyear.monthdate <=
[Enter end date:]

John W. Vinson [MVP]