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)??
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!!!
Dale Fye
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.
IEHP1
ASKER
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.
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)??
jerryb30
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.
IEHP1
ASKER
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??
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.......
IEHP1
ASKER
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??
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
IEHP1
ASKER
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???
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?
IEHP1
ASKER
this was genius. Thank you fyed!!!!! He didn't give me the answer, I had to earn it!!!!
jerryb30
IEHP1-We were crossing comments. yes, you did work it out, with fyed's original answer.
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?
IEHP1
ASKER
ok jerryb30 good to know (hope you get yours perfect, too).
jerryb30
TRANSFORM iif(source = "count", cstr(Sum(['Claims Counts & Values by Month'].MyValue)), format(sum(['Claims Counts & Values by Month'].MyValue), "$0.00")) AS SumOfMyValue
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");
IEHP1
ASKER
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).
jerryb30
You can't throw an iif into the middle of the statement. Did you see my post?
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");
IEHP1
ASKER
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)?
jerryb30
No points. I had that worked out, but fyed posted a better sql statement otherwise. All good.
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)?
jerryb30
Try $0,000.00 in the format string.
IEHP1
ASKER
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?
And yes, cstr converts to string. Look up cstr, clng, ccur, cdate, cdbl for other conversions.
IEHP1
ASKER
yes, but can we have it formatted with a comma (11185-no, 11,185-yes)?
jerryb30
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.)
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");
IEHP1
ASKER
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)?
IEHP1
ASKER
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?)
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");
IEHP1
ASKER
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
Dale Fye
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.
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
IEHP1
ASKER
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
jerryb30
To show the screenshot, after attaching file, select EMBED.
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!!!