Trying to figure out how to create a crosstab query using Excel file

Please see the attached Excel file.  I'm trying to figure out how to create a crosstab query that will give me the data for a report that looks like this:


Report ExamplePricing-Example-File.xlsx
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
This seems to be an Excel question, so not sure why you selected the Access topic?  I won't go into too much detail just in case I am misunderstanding.

Looking at the Excel sheet you provided, Excel has the capability called Pivot Tables to do what you want.  But to do that you need to get your data in an easier format for the tool to work with.  So I first created a new sheet, "flattening" your sample data into a more database design, looking like:
sshot-345.pngThen, I selected that and inserted a new pivot table on the next sheet, selecting the row, column and data fields:
sshot-344.pngAttaching my test sample, let me know if this is what you want to do, or if I missed the question.

EE29066341.xlsx


»bp
0
SteveL13Author Commented:
This is an Access question.  I cannot alter the Excel file which really is an Access table.  I only put it together in Excel to show what the table looks like.
0
PatHartmanCommented:
Did you try the crosstab wizard?  Since you are having a problem with Access, it would be better to post pictures of the "Access" problem.

If your question is - how do I get the 12 month names in month order?  Then the answer is, use the Column headings property.   But a word of warning.  When you use column headings you never get any data that doesn't match one of the headings.  This isn't a problem with months because there's only 12 of them and they don't change but if you wanted department headings for example and you wanted them in some order other than alphabetical so you decided to use the column headings property,  if the data contained a department that wasn't defined in the column headings list, the data for that department would not be included in the report.

One more caveat.  Make sure your select query includes a selection by year otherwise you mush data for all years into each month's column.
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.

SteveL13Author Commented:
Attached:  Sample Access file/table.
PricingExample.accdb
0
PatHartmanCommented:
You didn't even try.
0
SteveL13Author Commented:
Not true.  I did and what I tried did not work.  I don't understand cross-tab queries.
0
PatHartmanCommented:
What happened when you ran the wizard?
0
SteveL13Author Commented:
See attached.  Not even close.
PricingExample2.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
Then this goes back to the old concept of "Normalization"
Your raw data is not in the correct format to give you what you are looking for (easily).

If your data were in the more appropriate format, like this:

ID      MONTH_DATE      PRICE      PriceType
1      1/1/2017      $52.50      ABC
2      2/1/2017      $53.47      ABC
3      3/1/2017      $49.33      ABC
4      4/1/2017      $51.06      ABC
5      5/1/2017      $48.48      ABC
6      6/1/2017      $45.18      ABC
7      7/1/2017      $46.63      ABC
8      8/1/2017      $48.04      ABC
9      9/1/2017      $49.82      ABC
10      1/1/2017      $3.30      XYZ
11      2/1/2017      $2.85      XYZ
12      3/1/2017      $2.88      XYZ
13      4/1/2017      $3.10      XYZ
14      5/1/2017      $3.15      XYZ
15      6/1/2017      $2.98      XYZ
16      7/1/2017      $2.98      XYZ
17      8/1/2017      $2.09      XYZ
18      9/1/2017      $2.98      XYZ

...Then your crosstab would simply be this:

TRANSFORM First(AC_PRICE2.PRICE) AS FirstOfPRICE
SELECT AC_PRICE2.PriceType, First(AC_PRICE2.PRICE) AS [TotalOf PRICE]
FROM AC_PRICE2
GROUP BY AC_PRICE2.PriceType
PIVOT Format([MONTH_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Open in new window


...and it would look like this:
Normalized Crosstab...easy peasy lemon squeezy
;-)

As with all un-normalized data, ...if left in the incorrect format, ...you will always have to jump though endless hoops to get the data to do things that are ridiculously simple, ...if the data were in the correct format.

For example, if the data is left un-normailzed you would have to make a report in design view:
...and create all the textboxes and lables that your layout example requires.
...then set all the Monthly textboxes to have a controlsource of something like this:
(for example: ABC_Price for January 1st 2017):
=DLookUp("ABC_PRICE","AC_Price","MONTH_DATE= #1/1/2017#")
(again, ...changing the Field and the Date arguments for *every single textbox*)
Then you get something like what you are after:
    "that will give me the data for a REPORT"
Un-normailzed Report
;-)

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
...actually, ...the crosstab would be more correct if we got rid of the confusing "Total" column.

TRANSFORM First(AC_PRICE2.PRICE) AS FirstOfPRICE
SELECT AC_PRICE2.PriceType
FROM AC_PRICE2
GROUP BY AC_PRICE2.PriceType
PIVOT Format([MONTH_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Fixed Normalized crosstab
;-)

Jeff
0
PatHartmanCommented:
Didn't I post a database that fixed this problem for you?  Perhaps it was in a different thread.
0
Bill PrewCommented:
Here is a possible approach.  First we create a query called qryData that takes your existing table and flattens it into a format that can easily drive the pivot activity.  Then we create the actual pivot (TRANSFORM) as a second query qryPivot.  See if this makes sense and might be helpful.

There's no easy way to make the qryData somewhat generic, it needs to be fashioned off of your real data, but once you have it done you can use it against that data going forward.

EE29066341.accdb


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Steve,
As you can see, (from all of our posts), the lynch-pin here is that your data is not normalized.

If the other tables in this system were designed by the same DBA, ...you will have to always be going through crazy machinations to do things that are normally simple, if the data was properly structured.

JeffCoachman
0
PatHartmanCommented:
I'll post it again.  It took a union query to solve the problem because the data is not normalized.
PricingExample2.accdb
0
Bill PrewCommented:
My comment here produced exactly what you were looking for, how was that not considered?

sshot-349.png

»bp
0
SteveL13Author Commented:
Pat had posted the solution 3 days ago.  I had missed it.
0
Bill PrewCommented:
Pat had posted the solution 3 days ago.  I had missed it.

What comment is that in this thread, I don't see it?


»bp
0
SteveL13Author Commented:
I am terribly mixed up and confused,  I have asked the moderator to re-open the topic so I can re-assign points.
0
PatHartmanCommented:
There may be more than one thread on this topic.
0
SteveL13Author Commented:
You all either posted a solution or offered very good insight.  Thank you.
0
Jeffrey CoachmanMIS LiasonCommented:
OK
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.