Solved

Crosstab query constraint of only 1 Value

Posted on 2013-01-07
47
382 Views
Last Modified: 2013-01-08
Hi, I would like to create a crosstab query showing a month-by-month count of claims and also a month-by-month interest value paid on the claims.

Example:

                              2012-01        2012-02
            Count        20,125          15,892
            Value         100,000.00   95,000.00

I haven't successfully thought of a way to overcome the crosstab query's 1 value constraint with a Group by required for at least one Row Heading. If I Group By on the field called claim_no, it will obviously be a very long result set.

Does anyone know if it is possible to do something like this (in multiple steps perhaps)??
0
Comment
Question by:IEHP1
  • 28
  • 13
  • 6
47 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38752282
my IT guys have some filter which is blocking my SQL statement.  Take a look at the attached, it involves creating a Union query to create the Count and and Value records, then using that as a subquery on a Crosstab.Crosstab of Union query
0
 

Author Comment

by:IEHP1
ID: 38752393
Thank you, I will try that when I get a chance.

I believe that there will be a problem, nonetheless, since I don't think I can group it on any of the fields without causing the result set to become longer than 2 rows (it should be 1 row for the counts and 1 row for the columns). I mean, can't group it on claim_no (the primary key for that table). I will have to figure out that aspect of it.

Thank you for your great technique fyed!!!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38752561
The group by statemens in the two segments of the Union query performs the initial Group and Count/Sum functions and will give you two records for each day.  One of those records will be the count of records associated with that day, the other will be the sum of whatever field you need to do your summation on for the [Value] row.

Then, the cross-tab will transpose those results so that you only have two rows, one for Count, the other for Value, and as many columns as you have Year/Month combinations in your data.
0
 

Author Comment

by:IEHP1
ID: 38752635
ok will try it when after the projects currently working on and will let you know how it goes as soon as i do. I am thinking I will probably need a little help with this when I get around to doing it.

Thank you
0
 

Author Comment

by:IEHP1
ID: 38752855
is there a trick going on in the SQL statement because I don't understand "Count" AS Source (3rd line down) since it is doing a Count already (next field over from that)??
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38752876
Not for points: "Count" is a flag so you get 2 records in your pivot as you asked for. "Monthly Count" would work as well. Just a label, as it were, and not part of any aggregate function.
0
 

Author Comment

by:IEHP1
ID: 38752904
whoooaaa!! I am getting the same numbers for Count and Value (I had to make the far right "AS MyValue" say the same name or else it will say "Microsoft Access doesn't recognize ~~~~~~(the field name not the same as the first line's (Temp.MyValue).

In other words, once I change one of the MyValue in the inner Select statements, it will  give that error, but if they are the same, they give the same results??

And the first column is titled like this:  <>   ????

Does any of this make sense to you fyed???
0
 

Author Comment

by:IEHP1
ID: 38752952
Do you want me to just paste the SQL??
0
 

Author Comment

by:IEHP1
ID: 38752972
I am not an expert at this stuff, so can you please help me at my level?

I don't know why I am getting astronomically high results? (but at least I am getting results when using fyed's technique right?)

Seriously help me out with this.......
0
 

Author Comment

by:IEHP1
ID: 38753026
Oh, hey, I got the results from each of the separate queries on my own (just had to research a little about UNION queries, read a Microsoft article, and took out the ALL as in UNION ALL and then I guess it didn't duplicate the records before performing the crosstab?

Laugh Out Loud, it worked!!!! I am so happy!!!!

Only problem now is the first column is "<>" (without the quotes, of course) and the values in both the Count and Value rows are some value I don't know what??? At first I thought it might be the sum of all months but I summed them up in Excel and way off????

Can you just help me out with this minor detail please??
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753055
I created a sample table with ClaimID (autonumber), ClaimMonth (text) and ClaimInterest (Currency), and adjusted for fyed's field names. Got same results with his sql as I did with my clumsy efforts (4 queries instead of 1).

fyed's sql works. Can you post your sql, a sample db?

Assuming:
No additional fields
MS Access
local tables
0
 

Author Comment

by:IEHP1
ID: 38753094
TRANSFORM Sum(['Claims Counts & Values by Month'].MyValue) AS SumOfMyValue
SELECT ['Claims Counts & Values by Month'].Source
FROM (SELECT date_paid, "Count" AS Source, Count(rvs_claim_master.claim_no) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Count"
UNION
SELECT date_paid, "Value" AS Source, Sum(rvs_claim_master.interest) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Value")  AS ['Claims Counts & Values by Month']
GROUP BY ['Claims Counts & Values by Month'].Source
PIVOT Format([rvs_claim_master].[date_paid],"yyyy" & "-" & "mm");


I just used my field names instead of his sample field names in this way and it worked but do you know why I get a <> with a value in the first column for both rows?? I don't know where it is getting its value from???
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753123
Maybe the ' in the field names?
0
 

Author Comment

by:IEHP1
ID: 38753129
I actually went to record 132052 in the table and found out that there were a bunch of null date_paid fields??

I put the WHERE clause in the crosstab section and it took out the null values (where date_paid is not null)

How about you, did you get yours working, too??
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753135
No, that is not it.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753146
Yes, a null date_field caused the <>. Something to remember.
0
 

Author Comment

by:IEHP1
ID: 38753173
What are you having problems with? What are your expected results? My SQL statement was just like fyed's except for my field name changes? What do you mean "No, that is not it"?

What were you looking for?
0
 

Author Closing Comment

by:IEHP1
ID: 38753175
this was genius. Thank you fyed!!!!! He didn't give me the answer, I had to earn it!!!!
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753179
IEHP1-We were crossing comments. yes, you did work it out, with fyed's original answer.
0
 

Author Comment

by:IEHP1
ID: 38753186
FYED, I am just curious at this point. Can you show me how I would make the Value row as Currency values (2 decimal places would be good)? While still preserving the Counts row as numbers (as a matter of fact, I would like them to be Standard Number format (the numbers with commas and no decimal points needed in this situation)?

My SQL is posted above (I can repost it again with that WHERE clause I was sharing with jerryb30).

It seems like every time I try inserting the Formatting functions into that SQL, though, it messes something up so I don't save it or return it back the way it was.

Can you help a little with that?
0
 

Author Comment

by:IEHP1
ID: 38753188
ok jerryb30 good to know (hope you get yours perfect, too).
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38753205
TRANSFORM iif(source = "count", cstr(Sum(['Claims Counts & Values by Month'].MyValue)), format(sum(['Claims Counts & Values by Month'].MyValue), "$0.00")) AS SumOfMyValue
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38754456
OK, back at it.  It looks like you got it working, but now want the Count row formatted as an integer and the Value row formatted as Currency.  The down side of doing this is that when you use the Format() function, you are converting to text, so all of your numbers will be left aligned.  If you are displaying this data in a report, you could right align the textboxes to get the numeric effect.  This should work:

TRANSFORM Format(Sum(['Claims Counts & Values by Month'].MyValue), iif([Source] = "Count", "#0", "$.00#)) AS SumOfMyValue
SELECT ['Claims Counts & Values by Month'].Source
FROM (SELECT date_paid, "Count" AS Source, Count(rvs_claim_master.claim_no) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Count"
UNION
SELECT date_paid, "Value" AS Source, Sum(rvs_claim_master.interest) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Value")  AS ['Claims Counts & Values by Month']
GROUP BY ['Claims Counts & Values by Month'].Source
PIVOT Format([rvs_claim_master].[date_paid],"yyyy" & "-" & "mm");
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:IEHP1
ID: 38755372
So when I updated the SQL statement for the formatting, it popped up a message saying "Syntax error in SQL statement" or something like that and then had the whole SQL statement in the message box.

Here is the updated SQL that gave that error:

TRANSFORM Format(Sum(['Claims Counts & Values by Month'].MyValue) iif([Source] = "Count", "#0", "$.00#)) AS SumOfMyValue
SELECT ['Claims Counts & Values by Month'].Source
FROM (SELECT date_paid, "Count" AS Source, Count(rvs_claim_master.claim_no) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Count"
UNION
SELECT date_paid, "Value" AS Source, Sum(rvs_claim_master.interest) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Value")  AS ['Claims Counts & Values by Month']
WHERE ((([rvs_claim_master].[date_paid]) Is Not Null))
GROUP BY ['Claims Counts & Values by Month'].Source
PIVOT Format([rvs_claim_master].[date_paid],"yyyy" & "-" & "mm");

If you notice, I had to insert the WHERE clause (3rd line from bottom) to remove the <> field I was getting in the result set (caused by the date_paid field being null).
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38755395
You can't throw an iif into the middle of the statement. Did you see my post?
0
 

Author Comment

by:IEHP1
ID: 38755457
Oh yeah Jerry, I inserted yours and worked right away Thank you so much Jerry!!!

Can I award you points too?

So like fyed was saying, now they are left aligned (and I believe both the Counts and the Values should be right-aligned normally right?), but I could create a report and right align them to take care of that detail.

Here is the SQL as it works now:

TRANSFORM iif(source = "count", cstr(Sum(['Claims Counts & Values by Month'].MyValue)), format(sum(['Claims Counts & Values by Month'].MyValue), "$0.00")) AS MyValue
SELECT ['Claims Counts & Values by Month'].Source
FROM (SELECT date_paid, "Count" AS Source, Count(rvs_claim_master.claim_no) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Count"
UNION
SELECT date_paid, "Value" AS Source, Sum(rvs_claim_master.interest) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Value")  AS ['Claims Counts & Values by Month']
WHERE ((([rvs_claim_master].[date_paid]) Is Not Null))
GROUP BY ['Claims Counts & Values by Month'].Source
PIVOT Format([rvs_claim_master].[date_paid],"yyyy" & "-" & "mm");
0
 

Author Comment

by:IEHP1
ID: 38755468
cstr means convert string?

just out of curiosity, can I insert any alignment function into the statement (to have it align correctly in the query results)?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38755477
No points. I had that worked out, but fyed posted a better sql statement otherwise. All good.
0
 

Author Comment

by:IEHP1
ID: 38755487
Just thought of it now as well, can we get the Count row to be Standard (as in 11,185 (example only) instead of 11185) (comma there instead of no comma)?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38755496
Try $0,000.00 in the format string.
0
 

Author Comment

by:IEHP1
ID: 38755525
fits like a glove Jerry--Thank you once again!! I didn't even think of the Value row needing that formatting.

Can we do the same for the Count row? Not sure if I am seeing where to insert that function for the Count?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38755528
And yes, cstr converts  to string. Look up cstr, clng, ccur, cdate, cdbl for other conversions.
0
 

Author Comment

by:IEHP1
ID: 38755548
yes, but can we have it formatted with a comma (11185-no, 11,185-yes)?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38755566
Play around with it, or post another question.
For whatever reason, using a number conversion requires both COUNT and VALUE to have the same format.
Using FORMAT lets each field display its own unique format (withing a text string, with its own alignment issues.)
0
 

Author Comment

by:IEHP1
ID: 38755568
Jerry, I got it.

Here is what I used:

TRANSFORM iif(source = "count", format(Sum(['Claims Counts & Values by Month'].MyValue), "#0,000"), format(sum(['Claims Counts & Values by Month'].MyValue), "$0,000.00")) AS MyValue
SELECT ['Claims Counts & Values by Month'].Source
FROM (SELECT date_paid, "Count" AS Source, Count(rvs_claim_master.claim_no) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Count"
UNION
SELECT date_paid, "Value" AS Source, Sum(rvs_claim_master.interest) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Value")  AS ['Claims Counts & Values by Month']
WHERE ((([rvs_claim_master].[date_paid]) Is Not Null))
GROUP BY ['Claims Counts & Values by Month'].Source
PIVOT Format([rvs_claim_master].[date_paid],"yyyy" & "-" & "mm");
0
 

Author Comment

by:IEHP1
ID: 38755581
so you can see I replaced the cstr with format and inserted "#0,000" at the end.

The very last thing would be to see if we can get the Count row values to align to the right in the query results (now they are aligning left)?
0
 

Author Comment

by:IEHP1
ID: 38755596
actually, both the Value and the Count rows are aligning left (can we insert something like cnum before the format function for both of them?)

Sorry if I am wrong about cnum?
0
 

Author Comment

by:IEHP1
ID: 38755696
I've got the Counts and Values rows to align to the right in the query results now.

But the Currency Values row is a little strange (because of the "0,000.00 it shows $0,000.00 where I would like it to say $0.00 another one says $0,081.85 where I would like it to say $81.85, etc., etc. without losing the correct ones such as $11,365.98)??

Here is the SQL code now as it works:

 TRANSFORM iif(source = "count", format(Sum(['Claims Counts & Values by Month'].MyValue), "#0,000"), format(sum(['Claims Counts & Values by Month'].MyValue), "$0,000.00")) AS MyValue
SELECT ['Claims Counts & Values by Month'].Source
FROM (SELECT date_paid, "Count" AS Source, Count(rvs_claim_master.claim_no) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Count"
UNION
SELECT date_paid, "Value" AS Source, Sum(rvs_claim_master.interest) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Value")  AS ['Claims Counts & Values by Month']
WHERE ((([rvs_claim_master].[date_paid]) Is Not Null))
GROUP BY ['Claims Counts & Values by Month'].Source
PIVOT Format([rvs_claim_master].[date_paid],"yyyy" & "-" & "mm");
0
 

Author Comment

by:IEHP1
ID: 38755737
oh I guess not, when I tried to run it again, it won't align right anymore (weird?)

let me know if there is a way to make it align correctly in the query results if you can (not a pressing issue, you guys are probably "putting out fires" elsewhere)

Thank you
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38755843
Once you use the Format() function, it becomes text, which automatically aligns it to the left.  However, if you are using a non-proportional font to display your query results, you could try:

TRANSFORM Right(String(10, " ") & format(Sum(['Claims Counts & Values by Month'].MyValue), iif(source = "count", "#0,000", "$0,000.00")), 10) AS MyValue
SELECT ['Claims Counts & Values by Month'].Source
FROM (SELECT date_paid, "Count" AS Source, Count(rvs_claim_master.claim_no) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Count"
UNION
SELECT date_paid, "Value" AS Source, Sum(rvs_claim_master.interest) AS MyValue
FROM rvs_claim_master
GROUP BY date_paid, "Value")  AS ['Claims Counts & Values by Month']
WHERE ((([rvs_claim_master].[date_paid]) Is Not Null))
GROUP BY ['Claims Counts & Values by Month'].Source
PIVOT Format([rvs_claim_master].[date_paid],"yyyy" & "-" & "mm");

This basically appends 10 spaces to the front of the Count and Value columns, then extracts the right most 10 characters.  I usually use the Courier or Courier New font for non-proportional spacing.  If you click on the box in the Upper left corner of the query results, it will highlight all of the result cells.  Then click on Home on the main menu and change the font to one of those fonts.right aligned
0
 

Author Comment

by:IEHP1
ID: 38756199
So the only change was in the TRANSFORM line of the SQL so I copied yours into mine and these are the results:

Sorry I am not as slick as you for pasting in the screen shot (how do you do that anyway, I tried MSPaint and the Windows 7 Snipping Tool)?

You will notice that the Count row values are slightly to the right but then they kind of just sit just left of center but not left-aligned all the way?

I'm not sure what font I am using (don't know which ones are non-proportional)?
Query-Results.PNG
0
 

Author Comment

by:IEHP1
ID: 38756234
oh wait sorry didn't read the last part of your post, got them going to look right-indented now and please see attachment (looks a little weird in that font--Courier New, i will try Courier, do you know any other "non-proportional fonts" I could use--if not, its ok, I will Google it).

The only thing left I guess is the formatting of the Value row (if you'll notice the $0,000.00 instead of how it should be like $0.00. I played a little with the SQL but can't seem to get the right balance?
New-query-results.PNG
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38756267
To show the screenshot, after attaching file, select EMBED.
0
 

Author Comment

by:IEHP1
ID: 38756283
oh ok thx do you see what i mean about the format of the value row, though?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38756340
to show the screen shot, instead of attaching a file, select the "Insert Image", last icon on the right of the message tool bar.

in your SQL, replace:

iif(source = "count", "#0,000", "$0,000.00")

With:

iif(source = "count", "#,###", "$#,###.00")
0
 

Author Comment

by:IEHP1
ID: 38756403
YES!!! Got it guys!! Thank you to both of you guys!

I was going through the fonts and found that the following will bring it all the way to the right (just an FYI)

Courier
Courier New
Lucida Console
Miriam Fixed
Rod
Simplified Arabic Fixed

Thank you so much!!!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38756427
Glad to could help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now