Crosstab query (I think)

D

DKS

I need help on structuring a query.

I have an existing query that gives me as as output the following information:
Region-Id (coded number with duplicates)
Month (alphanumeric field)
Reading (numeric field)

the existing query gives for each "region-id" the "reading" for each
"month". Thus there are duplicates for "region-id" and duplicates for
"month".

The new query that I need:
For 2 given months, and I am ready to hard-code those values in the new
query, I need a query that gives as output the following columns/values:

Column 1 = Region-Id (one single line per region-id, thus using the GROUPED
BY functionality)
Column 2 = "reading" value for Month-1 that I can give as hard-coded value
Column 3 = "reading" value for Month-2 that I can give as hard-coded value
Column 4= difference between column 3 and column 2.

It is possible that in certain cases there is no data for a region for one
or both months. Just FYI.

Thanks a lot in anticipation.
 
J

John Spencer

SELECT [Region-ID]
, Sum(IIF([month] = [Enter Month 1],Reading)) as MonthOneSum
, Sum(IIF([month] = [Enter Month 2],Reading)) as MonthTwoSum
, Sum(IIF([month] = [Enter Month 2],Reading))
- Sum(IIF([month] = [Enter Month 1],Reading)) as Difference
FROM [SomeTableOrQuery]
WHERE [Month] = [Enter Month 1]
OR [Month] = [Enter Month 2]
GROUP BY [Region-ID]

If you need help building that in query design view instead of in SQL view
then post back.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
D

DKS

Hi John,

I can manage the SQL view. I will try and it let you know of the outcome
(even though I am quite sure that it will work).

Thanks for your help.

John Spencer said:
SELECT [Region-ID]
, Sum(IIF([month] = [Enter Month 1],Reading)) as MonthOneSum
, Sum(IIF([month] = [Enter Month 2],Reading)) as MonthTwoSum
, Sum(IIF([month] = [Enter Month 2],Reading))
- Sum(IIF([month] = [Enter Month 1],Reading)) as Difference
FROM [SomeTableOrQuery]
WHERE [Month] = [Enter Month 1]
OR [Month] = [Enter Month 2]
GROUP BY [Region-ID]

If you need help building that in query design view instead of in SQL view
then post back.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need help on structuring a query.

I have an existing query that gives me as as output the following information:
Region-Id (coded number with duplicates)
Month (alphanumeric field)
Reading (numeric field)

the existing query gives for each "region-id" the "reading" for each
"month". Thus there are duplicates for "region-id" and duplicates for
"month".

The new query that I need:
For 2 given months, and I am ready to hard-code those values in the new
query, I need a query that gives as output the following columns/values:

Column 1 = Region-Id (one single line per region-id, thus using the GROUPED
BY functionality)
Column 2 = "reading" value for Month-1 that I can give as hard-coded value
Column 3 = "reading" value for Month-2 that I can give as hard-coded value
Column 4= difference between column 3 and column 2.

It is possible that in certain cases there is no data for a region for one
or both months. Just FYI.

Thanks a lot in anticipation.
Click to expand...
.
Click to expand...
 
D

DKS

Hi John,

Yes, your proposed solution worked well. Thanks for the same.

Just for my knowledge: is there some good place on the internet where I can
learn what the IIF function that you used can do (and more importantly cannot
do)?

Thanks.


John Spencer said:
SELECT [Region-ID]
, Sum(IIF([month] = [Enter Month 1],Reading)) as MonthOneSum
, Sum(IIF([month] = [Enter Month 2],Reading)) as MonthTwoSum
, Sum(IIF([month] = [Enter Month 2],Reading))
- Sum(IIF([month] = [Enter Month 1],Reading)) as Difference
FROM [SomeTableOrQuery]
WHERE [Month] = [Enter Month 1]
OR [Month] = [Enter Month 2]
GROUP BY [Region-ID]

If you need help building that in query design view instead of in SQL view
then post back.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need help on structuring a query.

I have an existing query that gives me as as output the following information:
Region-Id (coded number with duplicates)
Month (alphanumeric field)
Reading (numeric field)

the existing query gives for each "region-id" the "reading" for each
"month". Thus there are duplicates for "region-id" and duplicates for
"month".

The new query that I need:
For 2 given months, and I am ready to hard-code those values in the new
query, I need a query that gives as output the following columns/values:

Column 1 = Region-Id (one single line per region-id, thus using the GROUPED
BY functionality)
Column 2 = "reading" value for Month-1 that I can give as hard-coded value
Column 3 = "reading" value for Month-2 that I can give as hard-coded value
Column 4= difference between column 3 and column 2.

It is possible that in certain cases there is no data for a region for one
or both months. Just FYI.

Thanks a lot in anticipation.
Click to expand...
.
Click to expand...
 
J

John Spencer

Try Access HELP. You may need to check it out in Access VBA help, but in an
SQL statement it works pretty much the same. Slight differences in the
behavior when used in a query.

The basic structure of an IIF is
Comparison, Result if True, Result If false

IN a query you can skip the result if false. To totally be correct, I should
have supplied the third argument also.
Sum(IIF([month] = [Enter Month 1],Reading,Null))

You can nest IIF statements (up to a limit)
IIF(1<>2,3,IIF(1<2,1,2))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi John,

Yes, your proposed solution worked well. Thanks for the same.

Just for my knowledge: is there some good place on the internet where I can
learn what the IIF function that you used can do (and more importantly cannot
do)?

Thanks.


John Spencer said:
SELECT [Region-ID]
, Sum(IIF([month] = [Enter Month 1],Reading)) as MonthOneSum
, Sum(IIF([month] = [Enter Month 2],Reading)) as MonthTwoSum
, Sum(IIF([month] = [Enter Month 2],Reading))
- Sum(IIF([month] = [Enter Month 1],Reading)) as Difference
FROM [SomeTableOrQuery]
WHERE [Month] = [Enter Month 1]
OR [Month] = [Enter Month 2]
GROUP BY [Region-ID]

If you need help building that in query design view instead of in SQL view
then post back.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need help on structuring a query.

I have an existing query that gives me as as output the following information:
Region-Id (coded number with duplicates)
Month (alphanumeric field)
Reading (numeric field)

the existing query gives for each "region-id" the "reading" for each
"month". Thus there are duplicates for "region-id" and duplicates for
"month".

The new query that I need:
For 2 given months, and I am ready to hard-code those values in the new
query, I need a query that gives as output the following columns/values:

Column 1 = Region-Id (one single line per region-id, thus using the GROUPED
BY functionality)
Column 2 = "reading" value for Month-1 that I can give as hard-coded value
Column 3 = "reading" value for Month-2 that I can give as hard-coded value
Column 4= difference between column 3 and column 2.

It is possible that in certain cases there is no data for a region for one
or both months. Just FYI.

Thanks a lot in anticipation.
Click to expand...
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autonumber field in Excel that behaves the same as Access 0
Count of Unique Values in Access Field 9
Max plus additional field 1
Query the same table three times 0
Sort query by date 3
Multiple Unrelated Tables w/Similar Field in Single Query 6
How do I add a column that calculates the product of two fields? 2
Crosstab Query Column headings 4

Top