Max & Min field table

D

Dolphinv4

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
 
D

Douglas J. Steele

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
 
D

Dolphinv4

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.

Douglas J. Steele said:
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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dolphinv4 said:
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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dolphinv4 said:
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.

Douglas J. Steele said:
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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dolphinv4 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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
 
D

Douglas J. Steele

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!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John Spencer said:
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.

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])
...
Click to expand...
Click to expand...
 
D

Dolphinv4

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

Douglas J. Steele said:
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])
....

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dolphinv4 said:
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.

Douglas J. Steele said:
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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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,

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

Douglas J. Steele said:
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])
....

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dolphinv4 said:
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:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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
Click to expand...
Click to expand...
Click to expand...
 
D

Dolphinv4

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


John Spencer said:
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,

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

Douglas J. Steele said:
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])
....

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Dolphinv4 said:
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;
Click to expand...

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?
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot table in Report 3
Cell 1 = row 1 balance minus row 2 balance 3
Access Running Balance in Access 1
Excel Complex Summing 3
simple formula I'm sure using offset? or index? 2
interpolation within subranges 4
Subtotals in a Pivot Table 1
Combine Two Queries in Different Layouts 2

Top