Crosstab query constraint of only 1 Value

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

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.

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

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

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.

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

Do you want me to just paste the SQL??

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

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

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

Maybe the ' in the field names?

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

No, that is not it.

Yes, a null date_field caused the <>. Something to remember.

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?

this was genius. Thank you fyed!!!!! He didn't give me the answer, I had to earn it!!!!

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?

ok jerryb30 good to know (hope you get yours perfect, too).

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

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).

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

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

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

Try $0,000.00 in the format string.

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.

yes, but can we have it formatted with a comma (11185-no, 11,185-yes)?

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.)

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

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

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?

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

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

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

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

To show the screenshot, after attaching file, select EMBED.

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

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

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

Glad to could help.