davetough
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jeffrey Coachman
I think the Cross-tab looks better...
EXA1.accdb
EXA1.accdb
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
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
Please evaluate capricorn1's post first.
He is much better at Crosstabs/SQL than I am...
He is much better at Crosstabs/SQL than I am...
...and his post will sort the Months properly...
(My post will have the months sorted by the name alphabetically...)
:-(
(My post will have the months sorted by the name alphabetically...)
:-(
ASKER
thanks cap and boag for explanation- sorry to take time getting back