sorting query by calculated field

[lay]

I have a query that pulls up data from tables (i.e. missing orders, aged po's
etc), and I added 3 fields into the query to calculated points for each
area...so missing orders will have a missing order points field, so on so
forth.

Then i added a field to calculate the total points for each plant...and
that's just "points for missing order + points for aged po's"

I want to sort it according to the total points, but when i do that, and
click on datasheet view, it'll pop up: Enter Total points, then "Enter points
for missing order", etc...why is that? can't i use the sort function on a
calculated field?

 

[Jerry Whittle]

Please provide the sql for that query.

 

[lay]

i *think* you're talking about this (SQL = formulas??)

nway, here's the formulas i used:

AgedPOPoints: Int((1-[Aged PO (%)])*10)

TotOrdNotInSys: [% SuspdOrd]+[% Ords Missing]

TotOrdPoints:
IIf(Int(10-([TotOrdNotInSys]*100))<0,0,Int(10-([TotOrdNotInSys]*100)))

DataIntPoints: [AgedPOPoints]+[TotOrdPoints]

DataIntPoints is what i want to sort it by....

 

[John Vinson]

i *think* you're talking about this (SQL = formulas??)

No. Open the Query in design view. Use the leftmost tool in the query
design toolbar as a dropdown to select SQL view, or (equivalently) use
the View menu option to view the query in its *real* form - the SQL
text. Copy and paste that cryptic (at this point, it'll make sense
when you study it!) text to a message here.

John W. Vinson[MVP]

 

[lay]

whopps!! *blush* can you tell i'm new at this??!!

nway, here it is!

SELECT [Mstr Raw Mat Stat Report].Business, [Mstr Raw Mat Stat
Report].PlantName, Plants.PlantCode, [Mstr MissedOrd&Susp].[Total # of
Orders], [Mstr MissedOrd&Susp].[# of Missing Orders], [# of Missing
Orders]/[Total # of Orders] AS [% Ords Missing], [Mstr MissedOrd&Susp].[# of
Suspended Orders >3 Days], [# of Suspended Orders >3 Days]/[Total # of
Orders] AS [% SuspdOrd], [Mstr Raw Mat Stat Report].[Tot#OfPO's], [Mstr Raw
Mat Stat Report].[#OfPO'sDue(Prom>20Days)],
[#OfPO'sDue(Prom>20Days)]/[Tot#OfPO's] AS [Aged PO (%)], Int((1-[Aged PO
(%)])*10) AS AgedPOPoints, [% SuspdOrd]+[% Ords Missing] AS TotOrdNotInSys,
IIf(Int(10-([TotOrdNotInSys]*100))<0,0,Int(10-([TotOrdNotInSys]*100))) AS
TotOrdPoints, [AgedPOPoints]+[TotOrdPoints] AS DataIntPoints, [Mstr CondFrmt
Qry].[AvgOf% Ords Missing], [Mstr CondFrmt Qry].[AvgOf% SuspdOrd], [Mstr
CondFrmt Qry].[AvgOfAged PO (%)], [Mstr MissedOrd&Susp].FW, [Mstr CondFrmt
Qry].AvgOfDataIntPoints
FROM FW INNER JOIN (([Mstr MissedOrd&Susp] INNER JOIN ([Mstr Raw Mat Stat
Report] INNER JOIN Plants ON [Mstr Raw Mat Stat Report].PlantName =
Plants.PlantName) ON [Mstr MissedOrd&Susp].[Plant Name] = [Mstr Raw Mat Stat
Report].PlantName) INNER JOIN [Mstr CondFrmt Qry] ON Plants.PlantCode = [Mstr
CondFrmt Qry].PlantCode) ON (FW.FW = [Mstr Raw Mat Stat Report].FW) AND
(FW.FW = [Mstr MissedOrd&Susp].FW)
WHERE ((([Mstr MissedOrd&Susp].FW)=[Forms]![MainPg].[MainPgFW]));

 

[Jerry Whittle]

We were all new once .

I can't find the following, or anything like it, in your SQL statement.
points for missing order + points for aged po's

To sort a query add an ORDER BY clause. In the Access QBE grid, it's the
Sort line. It looks something like below. Make sure that you move the
semi-colon ( ; ) to the end.

WHERE ((([Mstr MissedOrd&Susp].FW)=[Forms]![MainPg].[MainPgFW]))
ORDER BY [AgedPOPoints]+[TotOrdPoints] ;

If you want the order flipped it would look like:

WHERE ((([Mstr MissedOrd&Susp].FW)=[Forms]![MainPg].[MainPgFW]))
ORDER BY [AgedPOPoints]+[TotOrdPoints] DESC ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

whopps!! *blush* can you tell i'm new at this??!!

nway, here it is!

SELECT [Mstr Raw Mat Stat Report].Business, [Mstr Raw Mat Stat
Report].PlantName, Plants.PlantCode, [Mstr MissedOrd&Susp].[Total # of
Orders], [Mstr MissedOrd&Susp].[# of Missing Orders], [# of Missing
Orders]/[Total # of Orders] AS [% Ords Missing], [Mstr MissedOrd&Susp].[# of
Suspended Orders >3 Days], [# of Suspended Orders >3 Days]/[Total # of
Orders] AS [% SuspdOrd], [Mstr Raw Mat Stat Report].[Tot#OfPO's], [Mstr Raw
Mat Stat Report].[#OfPO'sDue(Prom>20Days)],
[#OfPO'sDue(Prom>20Days)]/[Tot#OfPO's] AS [Aged PO (%)], Int((1-[Aged PO
(%)])*10) AS AgedPOPoints, [% SuspdOrd]+[% Ords Missing] AS TotOrdNotInSys,
IIf(Int(10-([TotOrdNotInSys]*100))<0,0,Int(10-([TotOrdNotInSys]*100))) AS
TotOrdPoints, [AgedPOPoints]+[TotOrdPoints] AS DataIntPoints, [Mstr CondFrmt
Qry].[AvgOf% Ords Missing], [Mstr CondFrmt Qry].[AvgOf% SuspdOrd], [Mstr
CondFrmt Qry].[AvgOfAged PO (%)], [Mstr MissedOrd&Susp].FW, [Mstr CondFrmt
Qry].AvgOfDataIntPoints
FROM FW INNER JOIN (([Mstr MissedOrd&Susp] INNER JOIN ([Mstr Raw Mat Stat
Report] INNER JOIN Plants ON [Mstr Raw Mat Stat Report].PlantName =
Plants.PlantName) ON [Mstr MissedOrd&Susp].[Plant Name] = [Mstr Raw Mat Stat
Report].PlantName) INNER JOIN [Mstr CondFrmt Qry] ON Plants.PlantCode = [Mstr
CondFrmt Qry].PlantCode) ON (FW.FW = [Mstr Raw Mat Stat Report].FW) AND
(FW.FW = [Mstr MissedOrd&Susp].FW)
WHERE ((([Mstr MissedOrd&Susp].FW)=[Forms]![MainPg].[MainPgFW]));

No. Open the Query in design view. Use the leftmost tool in the query
design toolbar as a dropdown to select SQL view, or (equivalently) use
the View menu option to view the query in its *real* form - the SQL
text. Copy and paste that cryptic (at this point, it'll make sense
when you study it!) text to a message here.

John W. Vinson[MVP]

 

[lay]

[AgedPOPoints]+[TotOrdPoints] AS DataIntPoints is what you were looking for?

TotordPoints include missing orders and suspended orders

Nway, i tried entering your "code" (is that what it's called?) into the end
of the SQL, and made sure the semi colon was at the end.....then i tried to
switch it back to database view, and it prompts:

AgedPOPoints


where i'm expected to enter something in, and so on so forth, which leads me
back to my first post!

Has it anything to do with the fact that this is a calculated field?

We were all new once .

I can't find the following, or anything like it, in your SQL statement.
points for missing order + points for aged po's

To sort a query add an ORDER BY clause. In the Access QBE grid, it's the
Sort line. It looks something like below. Make sure that you move the
semi-colon ( ; ) to the end.

WHERE ((([Mstr MissedOrd&Susp].FW)=[Forms]![MainPg].[MainPgFW]))
ORDER BY [AgedPOPoints]+[TotOrdPoints] ;

If you want the order flipped it would look like:

WHERE ((([Mstr MissedOrd&Susp].FW)=[Forms]![MainPg].[MainPgFW]))
ORDER BY [AgedPOPoints]+[TotOrdPoints] DESC ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

whopps!! *blush* can you tell i'm new at this??!!

nway, here it is!

SELECT [Mstr Raw Mat Stat Report].Business, [Mstr Raw Mat Stat
Report].PlantName, Plants.PlantCode, [Mstr MissedOrd&Susp].[Total # of
Orders], [Mstr MissedOrd&Susp].[# of Missing Orders], [# of Missing
Orders]/[Total # of Orders] AS [% Ords Missing], [Mstr MissedOrd&Susp].[# of
Suspended Orders >3 Days], [# of Suspended Orders >3 Days]/[Total # of
Orders] AS [% SuspdOrd], [Mstr Raw Mat Stat Report].[Tot#OfPO's], [Mstr Raw
Mat Stat Report].[#OfPO'sDue(Prom>20Days)],
[#OfPO'sDue(Prom>20Days)]/[Tot#OfPO's] AS [Aged PO (%)], Int((1-[Aged PO
(%)])*10) AS AgedPOPoints, [% SuspdOrd]+[% Ords Missing] AS TotOrdNotInSys,
IIf(Int(10-([TotOrdNotInSys]*100))<0,0,Int(10-([TotOrdNotInSys]*100))) AS
TotOrdPoints, [AgedPOPoints]+[TotOrdPoints] AS DataIntPoints, [Mstr CondFrmt
Qry].[AvgOf% Ords Missing], [Mstr CondFrmt Qry].[AvgOf% SuspdOrd], [Mstr
CondFrmt Qry].[AvgOfAged PO (%)], [Mstr MissedOrd&Susp].FW, [Mstr CondFrmt
Qry].AvgOfDataIntPoints
FROM FW INNER JOIN (([Mstr MissedOrd&Susp] INNER JOIN ([Mstr Raw Mat Stat
Report] INNER JOIN Plants ON [Mstr Raw Mat Stat Report].PlantName =
Plants.PlantName) ON [Mstr MissedOrd&Susp].[Plant Name] = [Mstr Raw Mat Stat
Report].PlantName) INNER JOIN [Mstr CondFrmt Qry] ON Plants.PlantCode = [Mstr
CondFrmt Qry].PlantCode) ON (FW.FW = [Mstr Raw Mat Stat Report].FW) AND
(FW.FW = [Mstr MissedOrd&Susp].FW)
WHERE ((([Mstr MissedOrd&Susp].FW)=[Forms]![MainPg].[MainPgFW]));

i *think* you're talking about this (SQL = formulas??)


No. Open the Query in design view. Use the leftmost tool in the query
design toolbar as a dropdown to select SQL view, or (equivalently) use
the View menu option to view the query in its *real* form - the SQL
text. Copy and paste that cryptic (at this point, it'll make sense
when you study it!) text to a message here.

John W. Vinson[MVP]

 

[John Vinson]

Has it anything to do with the fact that this is a calculated field?

Yes. You can't sort by a calculated field. Recapitulate the
calculation in the Order By expression.

John W. Vinson[MVP]

 

[lay]

and how do i do that? or, where do i do that? in the sort line, all i see is
"Ascending", "descending",and "not sorted".....

 

[lay]

never mind...figured it out...i go to form, hit "properties" and in the "all"
tab, go to "order by" , then i entered [dataintpoints] which is the field i
wanted it sorted by. Thanks!!