Sort a calculated field

[Opal]

I am running access 2003 and have a report
I want to sort based on the results of a calculated
field.

My SQL behind the report is:

SELECT DISTINCT ScrapData.PartNo, ScrapData.Description,
ScrapData.CostCtr, ScrapData.Quantity, ScrapData.ScrapDate
FROM ScrapData
GROUP BY ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr,
ScrapData.Quantity, ScrapData.ScrapDate
HAVING (((ScrapData.ScrapDate) Between [Forms]![frmSelectCC]!
[FromDate] And [Forms]![frmSelectCC]![ToDate]));

I then have my report grouped by the PartNo field as I
have a calculated field =Sum([Quantity]) for each part.

Now, my Manager wants to sort the report by largest
to smallest based on this =Sum([Quantity]) field.
Its not part of the query, so how do I sort my report
based on this field?

 

[Duane Hookom]

You must get the Sum(Quantity) into your report's record source query.

Create a new query [qtotPartNoSumQty]
SELECT ScrapData.PartNo, Sum(ScrapData.Quantity) As SumQty
FROM ScrapData
WHERE (((ScrapData.ScrapDate)
Between [Forms]![frmSelectCC]![FromDate] And
[Forms]![frmSelectCC]![ToDate]))
GROUP BY ScrapData.PartNo;

Then add this query to your report's record source and join the PartNo
fields. Add SumQty to the output fields so that you can sort by it.

 

[John Spencer]

Normally you need to calculate the value in the query. One method of doing so
would look like the following.

SELECT DISTINCT ScrapData.PartNo
, ScrapData.Description
, ScrapData.CostCtr
, ScrapData.Quantity
, ScrapData.ScrapDate
, (SELECT SUM(Quantity)
FROM ScrapData as Temp
WHERE Temp.ScrapDate Between
[Forms]![frmSelectCC]![FromDate] And [Forms]![frmSelectCC]![ToDate]
AND Temp.PartNo = ScrapData.PartNo) as TotalQuantity
FROM ScrapData
WHERE(((ScrapData.ScrapDate)
Between [Forms]![frmSelectCC]![FromDate] And [Forms]![frmSelectCC]![ToDate]));



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Opal]

John,

Oops...forgot to mention, the report opens
on a filter by Costctr and this query adds
all of the records by parts, not by filtered
cc.... My Private sub to open the report
is:

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim stDocName As String

If Len([cboCC] & "") = 0 Then
Select Case MsgBox("You did not enter a cost center, is that
correct?", vbYesNo, "Confirm Action")
Case vbNo
'Do Nothing.Default Behaviour
Case vbYes
stDocName = "rptScrap"
DoCmd.OpenReport stDocName, acPreview
End Select
End If

If Len([cboCC] & "") <> 0 Then

DoCmd.OpenReport "rptScrap", acPreview, , "CostCtr =" & cboCC

End If

Me.Visible = False

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox Err.Description
Resume Exit_cmdOK_Click

End Sub

So the user can see all records or filter by
cost center, but the query doesn't account for this...

 

[Duane Hookom]

You must get the CostCtr filter into the subquery or totals query that I
suggested.

SELECT ScrapData.PartNo, Sum(ScrapData.Quantity) As SumQty
FROM ScrapData
WHERE ScrapData.ScrapDate
Between [Forms]![frmSelectCC]![FromDate] And
[Forms]![frmSelectCC]![ToDate] AND
(CostCtr = [Forms]![frmSelectCC]![cboCC] OR [Forms]![frmSelectCC]![cboCC]
Is Null)
GROUP BY ScrapData.PartNo;

 

[Opal]

Hmm...Duane...

Getting there....but

why does my query show 6 results
and my report, based on this query
with the same parameter values only
show 3 results? For some reason
serveral part numbers are dropped
off of the report.... :-S

My report should show one part with
16 scrap items, one with 2 and 4 with 1.
It only shows the top 3....dropping off the remaining
3 parts with only a quantity of 1.

I can't see a rhyme or reason for this...

 

[Duane Hookom]

You report should display what your Record Source provides. I'm having
trouble seeing your record source or its datasheet. You need to be our eyes.
Please describe what you see.

 

[Opal]

Okay:

One example for one cost center is as follows:

Query results:

PartNo Description SumQty
6701 RF S/A 16
6712 FR Panel 2
6714 Back inner 1
6703 R Rr outer 1
6702 L Rr outer 1
5301 LF S/A 1


Report Results:

PartNo Description SumQty
6701 RF S/A 16
6712 FR Panel 2
5301 LF S/A 1

 

[Duane Hookom]

Are your report controls in the detail section?
It looks like you might have joined the quantity fields in the report's
record source query rather than just "join the PartNo fields" as I suggested.

What is the SQL view of your report's record source?

 

[Opal]

D'oh!! (head smack)

I still have the report controls
in a group header.....ugh...

That's what I get for working on
3 separate DBs at one time with
3 separate issues.....

Thanks for opening me eyes

 

[Opal]

Ugh, still not sorting properly.

SQL is:

SELECT ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr, Sum
(ScrapData.Quantity) AS SumQty
FROM ScrapData
WHERE (((ScrapData.CostCtr)=[Forms]![frmSelectCC]![cboCC]) AND
((ScrapData.ScrapDate) Between [Forms]![frmSelectCC]![FromDate] And
[Forms]![frmSelectCC]![ToDate])) OR (((ScrapData.ScrapDate) Between
[Forms]![frmSelectCC]![FromDate] And [Forms]![frmSelectCC]![ToDate])
AND (([Forms]![frmSelectCC]![cboCC]) Is Null))
GROUP BY ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr
ORDER BY Sum(ScrapData.Quantity) DESC;

 

[Duane Hookom]

I expect this isn't the record source of your report. My thought was you
would create the GROUP BY query and add it to the record source of your
report and join the appropriate fields.

What is your report's record source?
What are the sorting and grouping levels in your report?

 

[Opal]

Hi Duane,

Took some time off, sorry for the late response.
The SQL, above, is the source for my report.

There are currently no grouping or sorting levels
in the report.

 

[Opal]

More head smacks.....

Okay, the record source for the report
is now:

SELECT DISTINCT ScrapData.PartNo, ScrapData.Description,
ScrapData.ScrapDate, qrySumSort.SumQty, ScrapData.CostCtr
FROM ScrapData INNER JOIN qrySumSort
ON ScrapData.PartNo = qrySumSort.PartNo
GROUP BY ScrapData.PartNo, ScrapData.Description,
ScrapData.ScrapDate, qrySumSort.SumQty, ScrapData.CostCtr
HAVING (((ScrapData.ScrapDate)
Between [Forms]![frmSelectCC]![FromDate] And [Forms]![frmSelectCC]!
[ToDate]))
ORDER BY qrySumSort.SumQty;


but the query results are not what I want... and I am getting
duplicate
results... :-S

 

[Duane Hookom]

qrySumSort must be grouped by PartNo only. There must be no duplicate PartNo
values. If there are duplicate PartNo in qrySumSort, then you will get
duplicates in your report's record source.

If you can't figure this out, come back with the SQL view of qrySumSort. If
you are grouping by more than PartNo, tell us why.

 

[Opal]

After much fiddling and playing around, I
got it where I wanted it.

Thanks Duane!