Top AND Bottom Values

G

Guest

I am trying to obtain the top AND bottom 25 values in the same query for a
report.

The only difference is the sort order on the pertinent field but I get an
error telling me I have different fields.

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt] DESC;

Union All

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt]

Can anyone tell me how to resolve the issue? Thanks in advance.
 
A

Allen Browne

You may have to help JET understand the data types here.

1. Declare the parameter:
PARAMETERS [Forms]![frm_Reports]![RptDate] DateTime;
SELECT DISTINCT >...

2. Typecast the calculated field:
CCur(Nz([Indamt]-[amt],0)) AS Difference
That needs to happen in the SELECT and ORDER BY clauses.
(The ORDER BY in the first SELECT is actually ignored, because sorting is
done after the UNION.)

Since the values come form another query, you may need to adjust it also.
More info in:
Calculated fields misinterpreted'
at:
http://allenbrowne.com/ser-45.html
 
J

John Spencer

First problem is that you only get to sort a UNION query once. So your sort
in the first section is probably going to be ignored.

You could try making the two queries as separate queries. And then joining
the two separate queries in a third query
SELECT q_Top25.*
FROM q_Top25
UNION ALL
SELECT q_Bottom25.*
FROM qBottom25

Alternative would be to use subqueries. The following might work

SELECT qry_PACONT09.Dept
, qry_PACONT09.PTNumber
, qry_PACONT09.Name
, qry_PACONT09.PROC
, qry_PACONT09.Description
, qry_PACONT09.IndAmt
, PAPME42C_5_5.Amt
, [Indamt]-[amt] AS Difference
, qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
And (InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] DESC)
OR
InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] ASC) )
 
G

Guest

Allan:

PARAMETERS [Forms]![frm_Reports]![RptDate] DateTime;SELECT TOP 25
qry_PACONT09.PTNumber, qry_PACONT09.Name, qry_PACONT09.PROC,
qry_PACONT09.Description, qry_PACONT09.IndAmt, PAPME42C_5_5.Amt,
CCur(Nz([Indamt]-[amt],0)) AS Difference, qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt]Desc

Union All

PARAMETERS [Forms]![frm_Reports]![RptDate] DateTime;SELECT TOP 25
qry_PACONT09.PTNumber, qry_PACONT09.Name, qry_PACONT09.PROC,
qry_PACONT09.Description, qry_PACONT09.IndAmt, PAPME42C_5_5.Amt,
CCur(Nz([Indamt]-[amt],0)) AS Difference, qry_PACONT09.ChargeDate FROM
qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))ORDER BY
[Indamt]-[amt]

I keep getting the error: Characters founds after end of SQL Statement. I
am still fighting with it though . . thank you for the hints
--
JCoulson
Live Well .. Be Happy In All You Do


Allen Browne said:
You may have to help JET understand the data types here.

1. Declare the parameter:
PARAMETERS [Forms]![frm_Reports]![RptDate] DateTime;
SELECT DISTINCT >...

2. Typecast the calculated field:
CCur(Nz([Indamt]-[amt],0)) AS Difference
That needs to happen in the SELECT and ORDER BY clauses.
(The ORDER BY in the first SELECT is actually ignored, because sorting is
done after the UNION.)

Since the values come form another query, you may need to adjust it also.
More info in:
Calculated fields misinterpreted'
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jeff C said:
I am trying to obtain the top AND bottom 25 values in the same query for a
report.

The only difference is the sort order on the pertinent field but I get an
error telling me I have different fields.

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt] DESC;

Union All

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt]

Can anyone tell me how to resolve the issue? Thanks in advance.
Click to expand...
Click to expand...
 
G

Guest

John: Am trying your methods too, the subqueries crashes the program, and
They are on the far edge of my understanding, still am working with the
examples. Thanks
--
JCoulson
Live Well .. Be Happy In All You Do


John Spencer said:
First problem is that you only get to sort a UNION query once. So your sort
in the first section is probably going to be ignored.

You could try making the two queries as separate queries. And then joining
the two separate queries in a third query
SELECT q_Top25.*
FROM q_Top25
UNION ALL
SELECT q_Bottom25.*
FROM qBottom25

Alternative would be to use subqueries. The following might work

SELECT qry_PACONT09.Dept
, qry_PACONT09.PTNumber
, qry_PACONT09.Name
, qry_PACONT09.PROC
, qry_PACONT09.Description
, qry_PACONT09.IndAmt
, PAPME42C_5_5.Amt
, [Indamt]-[amt] AS Difference
, qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
And (InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] DESC)
OR
InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] ASC) )

Jeff C said:
I am trying to obtain the top AND bottom 25 values in the same query for a
report.

The only difference is the sort order on the pertinent field but I get an
error telling me I have different fields.

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt] DESC;

Union All

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt]

Can anyone tell me how to resolve the issue? Thanks in advance.
Click to expand...
Click to expand...
 
G

Guest

I added Allen's declarations and also made two separate queries, then joined
them per John:

SELECT top.*
FROM [top]

Union All

SELECT bottom.*
FROM [Bottom]

SUCCESS!!!

Thanks Guys.
--
JCoulson
Live Well .. Be Happy In All You Do


John Spencer said:
First problem is that you only get to sort a UNION query once. So your sort
in the first section is probably going to be ignored.

You could try making the two queries as separate queries. And then joining
the two separate queries in a third query
SELECT q_Top25.*
FROM q_Top25
UNION ALL
SELECT q_Bottom25.*
FROM qBottom25

Alternative would be to use subqueries. The following might work

SELECT qry_PACONT09.Dept
, qry_PACONT09.PTNumber
, qry_PACONT09.Name
, qry_PACONT09.PROC
, qry_PACONT09.Description
, qry_PACONT09.IndAmt
, PAPME42C_5_5.Amt
, [Indamt]-[amt] AS Difference
, qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
And (InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] DESC)
OR
InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] ASC) )

Jeff C said:
I am trying to obtain the top AND bottom 25 values in the same query for a
report.

The only difference is the sort order on the pertinent field but I get an
error telling me I have different fields.

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt] DESC;

Union All

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt]

Can anyone tell me how to resolve the issue? Thanks in advance.
Click to expand...
Click to expand...
 
G

Guest

I added Allen's declarations and created two queries joining them per John:

SELECT top.*
FROM [top]

Union All

SELECT bottom.*
FROM [Bottom]

SUCCESS!!

Thanks Guys.
--
JCoulson
Live Well .. Be Happy In All You Do


John Spencer said:
First problem is that you only get to sort a UNION query once. So your sort
in the first section is probably going to be ignored.

You could try making the two queries as separate queries. And then joining
the two separate queries in a third query
SELECT q_Top25.*
FROM q_Top25
UNION ALL
SELECT q_Bottom25.*
FROM qBottom25

Alternative would be to use subqueries. The following might work

SELECT qry_PACONT09.Dept
, qry_PACONT09.PTNumber
, qry_PACONT09.Name
, qry_PACONT09.PROC
, qry_PACONT09.Description
, qry_PACONT09.IndAmt
, PAPME42C_5_5.Amt
, [Indamt]-[amt] AS Difference
, qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
And (InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] DESC)
OR
InDamt-Amt IN
(SELECT Top 25 Indamt-Amt
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5
ON qry_PACONT09.PROC = PAPME42C_5_5.PROC
WHERE qry_PACONT09.Dept<>736 AND
qry_PACONT09.ChargeDate=[Forms]![frm_Reports]![RptDate]
ORDER BY [Indamt]-[amt] ASC) )

Jeff C said:
I am trying to obtain the top AND bottom 25 values in the same query for a
report.

The only difference is the sort order on the pertinent field but I get an
error telling me I have different fields.

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt] DESC;

Union All

SELECT DISTINCT TOP 25 qry_PACONT09.Dept, qry_PACONT09.PTNumber,
qry_PACONT09.Name, qry_PACONT09.PROC, qry_PACONT09.Description,
qry_PACONT09.IndAmt, PAPME42C_5_5.Amt, [Indamt]-[amt] AS Difference,
qry_PACONT09.ChargeDate
FROM qry_PACONT09 LEFT JOIN PAPME42C_5_5 ON qry_PACONT09.PROC =
PAPME42C_5_5.PROC
WHERE (((qry_PACONT09.Dept)<>736) AND
((qry_PACONT09.ChargeDate)=[Forms]![frm_Reports]![RptDate]))
ORDER BY [Indamt]-[amt]

Can anyone tell me how to resolve the issue? Thanks in advance.
Click to expand...
Click to expand...
 

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

Top 20 and bottom 20 values 9
Grouping Top Values 1
top 3 values Subquery problems 2
Problem with query: SELECT TOP 1 6
Help using Top Values or DMAX for a report 3
Multipage - navigation 2
Please see statement encloded about postal and lottery laws 3
Outlook connector crashes outlook 0

Top