Max & Min field table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dolphinv4 - 27 May 2008 11:06 GMT

Hi,

now i have the crosstab query as below. I want, in my report, to show the
following below the crosstab query table:

ABC ABC XYZ
Date 12345 55555 9876
1/1/08 100 -200 500
1/2/08 -150 150 450
1/3/08 175 -175 300

***********************************
Max 175 150 500
Min -150 -200 300
No. of days -ve 1 2 0
No. of days +ve 2 1 3
No. of days >200 0 0 3
***********************************

How should I do it?

Thanks,
Dolphin

Reply to this Message

Douglas J. Steele - 27 May 2008 12:13 GMT

You could Union together a number of subqueries, each one of which returns
one row of your desired totals:

SELECT "Min", Min([ABC]), Min([EDF]), Min([XYZ])
FROM MyCrosstabQuery
UNION
SELECT "Max", Max([ABC]), Max([EDF]), Max([XYZ])
FROM MyCrosstabQuery
UNION
SELECT "No. of days -ve", Sum(IIf([ABC] < 0, 1, 0)),
Sum(IIf([EDF] < 0, 1, 0)), Sum(IIf([XYZ] < 0, 1, 0)),
FROM MyCrosstabQuery
UNION
SELECT "No. of days +ve", Sum(IIf([ABC] > 0, 1, 0)),
Sum(IIf([EDF] > 0, 1, 0)), Sum(IIf([XYZ] > 0, 1, 0)),
FROM MyCrosstabQuery
UNION
SELECT "No. of days >200", Sum(IIf([ABC] > 200, 1, 0)),
Sum(IIf([EDF] > 200, 1, 0)), Sum(IIf([XYZ] > 200, 1, 0)),
FROM MyCrosstabQuery

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi,
>
[quoted text clipped - 19 lines]
> Thanks,
> Dolphin

Reply to this Message

Dolphinv4 - 27 May 2008 15:14 GMT

Hi,

thanks, but when i drag & drop this subquery as a subreport in my main
report, it says "Can't use ... as a record source...for subform or
subreport...[must] set the query's ColumnHeadings property"...

How do I do that?

Thanks.

> You could Union together a number of subqueries, each one of which returns
> one row of your desired totals:
[quoted text clipped - 40 lines]
> > Thanks,
> > Dolphin

Reply to this Message

Douglas J. Steele - 27 May 2008 15:34 GMT

I think the issue is that if you run that SQL, you'll get Expr1, Expr2 etc.
as field names, since I didn't bother creating any aliases.

All you need to do is create aliases for the first subselect: Union queries
get field names from the first subselect only.

SELECT "Min" AS Desc, Min([ABC]) AS Field1, Min([EDF]) AS Field2, Min([XYZ])
AS Field3
FROM MyCrosstabQuery
UNION
SELECT "Max", Max([ABC]), Max([EDF]), Max([XYZ])
...

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi,
>
[quoted text clipped - 52 lines]
>> > Thanks,
>> > Dolphin

Reply to this Message

John Spencer - 27 May 2008 16:40 GMT

Actually, I think this means you have to go all the way back to the crosstab
query and specify the column names to be returned using the Pivot clause,

Transform ...
SELECT ...
FROM ...
Group By ...
PIVOT [Somefield] IN ("ABC", "DEF", "XYZ")

In query design view, you would fill in the Column Headings properties.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> I think the issue is that if you run that SQL, you'll get Expr1, Expr2 etc.
> as field names, since I didn't bother creating any aliases.
[quoted text clipped - 8 lines]
> SELECT "Max", Max([ABC]), Max([EDF]), Max([XYZ])
> ...

Reply to this Message

Douglas J. Steele - 27 May 2008 19:23 GMT

Even if not required, that would certainly be a good idea to ensure that the
same field names appear each time the query's run!

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Actually, I think this means you have to go all the way back to the
> crosstab query and specify the column names to be returned using the Pivot
[quoted text clipped - 25 lines]
>> SELECT "Max", Max([ABC]), Max([EDF]), Max([XYZ])
>> ...

Reply to this Message

Dolphinv4 - 28 May 2008 08:45 GMT

Hi,

I tried what you instructed but I still encountered the same error message
in my report. What is wrong? Below is my SQL:

SELECT "Min" AS Expr1, Min([ABC
123]) AS Field1,Min([EFG
456]) AS Field2,Min([XYZ
789]) AS Field3
FROM CrosstabQueryAUD
UNION
SELECT "Max", Max([ABC
123]),Max([EFG
456]),Max([XYZ
789])
FROM CrosstabQueryAUD;

NOTE:
1) I tried to use SELECT "Min" AS Desc but access says it doesn't
recognise. So I changed to Expr1. Why is it so?

2) The company field in the above code looks like this coz my original
crosstab quey is as follows:
TRANSFORM Sum([All].Balance) AS SumOfBalance
SELECT [All].Date
FROM [All]
WHERE ((([All].Currency)="AUD"))
GROUP BY [All].Date, [All].Currency
PIVOT Entity & Chr(13) & Chr(10) & Accounts;

3) John Spencer said to use the following. But with my SQL above, will it
work?
Transform ...
SELECT ...
FROM ...
Group By ...
PIVOT [Somefield] IN ("ABC", "DEF", "XYZ")

Thanks.
Dolphin

> I think the issue is that if you run that SQL, you'll get Expr1, Expr2 etc.
> as field names, since I didn't bother creating any aliases.
[quoted text clipped - 65 lines]
> >> > Thanks,
> >> > Dolphin

Reply to this Message

John Spencer - 28 May 2008 12:24 GMT

It should, but you are going to have to know every Entity and Account
combination.

TRANSFORM Sum([All].Balance) AS SumOfBalance
SELECT [All].Date
FROM [All]
WHERE ((([All].Currency)="AUD"))
GROUP BY [All].Date, [All].Currency
PIVOT Entity & Chr(13) & Chr(10) & Accounts
IN ("ABC" & Chr(13) & Chr(10) & "12345",
"ABC" & Chr(13) & Chr(10) & "16789",
"DEF" & Chr(13) & Chr(10) & "000001",
"XYZ" & Chr(13) & Chr(10) & "29823",
"XXX" & Chr(13) & Chr(10) & "23111")

Your other choice is to use the Crosstab query as the source of a make
table query that creates a temporary table for use in the UNION query.

Again you will have to know all the Entity + Accounts that will be
returned AND you won't be able to embed the new line into the field names.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> Hi,
>
[quoted text clipped - 106 lines]
>>>>> Thanks,
>>>>> Dolphin

Reply to this Message

Dolphinv4 - 28 May 2008 13:35 GMT

Hi,
thanks,
but 2 last qns before i finish this project.

1) I finally managed to get the report out properly using the following.
However, the numbers come out not as the format i wanted. What i wanted is
1,515,333.45 instead of 1515333.45. How/where do I add this in the SQL?

SELECT "Min" AS Expr1, Min([ABC
123]) AS Field1,Min([EFG
456]) AS Field2,Min([XYZ
789]) AS Field3
FROM CrosstabQueryAUD
UNION
SELECT "Max", Max([ABC
123]),Max([EFG
456]),Max([XYZ
789])
FROM CrosstabQueryAUD;

2) You reminded me of something. If i have new companies from my source
table, I'll keep having to amend the SQL right? Is there a better way?

Thanks!
Dolphin

> It should, but you are going to have to know every Entity and Account
> combination.
[quoted text clipped - 134 lines]
> >>>>> Thanks,
> >>>>> Dolphin

Reply to this Message

Douglas J. Steele - 28 May 2008 14:14 GMT

> Hi,
> thanks,
[quoted text clipped - 15 lines]
> 789])
> FROM CrosstabQueryAUD;

SELECT "Min" AS Expr1, Format(Min([ABC 123], "#,###.00") AS
Field1,Format(Min([EFG 456], "#,###.00") AS Field2,Format(Min([XYZ 789],
"#,###.00") AS Field3
FROM CrosstabQueryAUD
UNION
SELECT "Max", Format(Max([ABC 123], "#,###.00"), Format(Max([EFG 456],
"#,###.00"), Format(Max([XYZ 789], "#,###.00")
FROM CrosstabQueryAUD;

(or you can simply set the format for the text box on the report)

> 2) You reminded me of something. If i have new companies from my source
> table, I'll keep having to amend the SQL right? Is there a better way?

Not really. Fortunately, you can always write VBA code to amend the SQL for
you.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message