• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

query a table to display data jan thru dec

Hello,
have attached sample db with tbl1
Is there a way I can create query on tbl1 and display solution like this:

Item #    Item Desc    Total    January   February   March    April    May   June  etc..
01           Misc            2879     880        888           780       331
03           PC               2983     672        488          1188      635
05          etc....
thank you
EXA1.accdb
0
davetough
Asked:
davetough
2 Solutions
 
Jeffrey CoachmanMIS LiasonCommented:
That is either a Pivot Table or a Crosstab query.

There is a crosstab query wizard in Access to help with the crosstab

You can make a Pivot table as well by changing the "View" of the table to "Pivot Table"

Then dragging and dropping the fields wherever you want them to be.

You will have to play around with both methods to see which is best for you...

But also wait for another expert to come along to give more explicit instructions.
0
 
Rey Obrero (Capricorn1)Commented:
copy and paste this sql statement in the SQL view of a query


TRANSFORM Sum(tbl1.[Total #]) AS [SumOfTotal #]
SELECT tbl1.[Item #], tbl1.[Item Desc], Sum(tbl1.[Total #]) AS [Total Of Total #]
FROM tbl1
GROUP BY tbl1.[Item #], tbl1.[Item Desc]
PIVOT tbl1.[Month] In("January","February","March","April","May","June","July","August","September","October","November","December")


run CrosstabQuery
EXA1.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
I think the Cross-tab looks better...
crosstabEXA1.accdb
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
chrisezardCommented:
You need a Crosstab Query, which will give you a spreadsheet-like layout

1.Click on Create/Query Design
2.Close the Show Table window
3.Right click on Query1 and select SQL view
4.Copy this code

TRANSFORM Sum(tbl1.[Total #]) AS [SumOfTotal #]
SELECT tbl1.[Item #], tbl1.[Item Desc], Sum(tbl1.[Total #]) AS YTD
FROM tbl1
GROUP BY tbl1.[Item #], tbl1.[Item Desc]
PIVOT tbl1.Month;

5.Right click on Query1 and select Design
6.Save the query as qtabMonthlyTotals, or whatever suits you
7.Run the query

8.The columns will not display in calendar order, but you can move them when the results are displayed and they will stay in the order you set.
9.You may want to format the values in design view
0
 
Jeffrey CoachmanMIS LiasonCommented:
Please evaluate capricorn1's post first.
He is much better at Crosstabs/SQL than I am...
0
 
Jeffrey CoachmanMIS LiasonCommented:
...and his post will sort the Months properly...
(My post will have the months sorted by the name alphabetically...)
:-(
0
 
davetoughAuthor Commented:
thanks cap and boag for explanation- sorry to take time getting back
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now