Avatar of IEHP1
IEHP1
Flag for United States of America asked on

Crosstab query constraint of only 1 Value

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)??
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
IEHP1

ASKER
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!!!
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.

Thank you
Your help has saved me hundreds of hours of internet surfing.
fblack61
IEHP1

ASKER
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??

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

Does any of this make sense to you fyed???
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
IEHP1

ASKER
Do you want me to just paste the SQL??
IEHP1

ASKER
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??
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jerryb30

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???
jerryb30

Maybe the ' in the field names?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
IEHP1

ASKER
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??
jerryb30

No, that is not it.
jerryb30

Yes, a null date_field caused the <>. Something to remember.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
IEHP1

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
IEHP1

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Dale Fye

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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
IEHP1

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
IEHP1

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jerryb30

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.)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
IEHP1

ASKER
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");
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?)

Sorry if I am wrong about cnum?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
IEHP1

ASKER
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.right aligned
Your help has saved me hundreds of hours of internet surfing.
fblack61
IEHP1

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
IEHP1

ASKER
oh ok thx do you see what i mean about the format of the value row, though?
Dale Fye

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")
IEHP1

ASKER
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!!!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Dale Fye

Glad to could help.