Sorting Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report where I have grouped entries by "Staff Name"
i.e. Work stats summed next to each staff members name.
However I want to sort it by one of the summed fields and not the alphabetic
sort on the name.

format is

Staff Name 1 Sum([field 1]) Sum([field 2]) etc
Staff Name 2 Sum([field 1]) Sum([field 2]) etc
Staff Name 3 Sum([field 1]) Sum([field 2]) etc

I want it to sort on =Sum([field 1]) by descending value (its a numerical
field) and not by the Staff Name.
How can I do this?

Thanks!
 
Create a record source for the report as Group by, and perform the sum in the
SQL and not in the report

SELECT MyTable.[Staff Name], Sum(MyTable.Field1) AS SumField1,
Sum(MyTable.Field2) AS SumField2
FROM MyTable
GROUP BY MyTable.[Staff Name]

In the report, in the grouping and sorting select the sum fields SumField1,
and sort by it.
 
Thanks Ofer

I'll try this on Monday when I get back to work.
I am very new to Access and haven't used much SQL yet but I think I get it...

--
SVL


Ofer said:
Create a record source for the report as Group by, and perform the sum in the
SQL and not in the report

SELECT MyTable.[Staff Name], Sum(MyTable.Field1) AS SumField1,
Sum(MyTable.Field2) AS SumField2
FROM MyTable
GROUP BY MyTable.[Staff Name]

In the report, in the grouping and sorting select the sum fields SumField1,
and sort by it.

SVL said:
I have a report where I have grouped entries by "Staff Name"
i.e. Work stats summed next to each staff members name.
However I want to sort it by one of the summed fields and not the alphabetic
sort on the name.

format is

Staff Name 1 Sum([field 1]) Sum([field 2]) etc
Staff Name 2 Sum([field 1]) Sum([field 2]) etc
Staff Name 3 Sum([field 1]) Sum([field 2]) etc

I want it to sort on =Sum([field 1]) by descending value (its a numerical
field) and not by the Staff Name.
How can I do this?

Thanks!
Click to expand...
Click to expand...
 
If you still need the details (which you would lose with Ofer's suggestion),
you could take his suggested query and add it to your report's record source
with the details records and join the [Staff Name] fields.

--
Duane Hookom
MS Access MVP


SVL said:
Thanks Ofer

I'll try this on Monday when I get back to work.
I am very new to Access and haven't used much SQL yet but I think I get
it...

--
SVL


Ofer said:
Create a record source for the report as Group by, and perform the sum in
the
SQL and not in the report

SELECT MyTable.[Staff Name], Sum(MyTable.Field1) AS SumField1,
Sum(MyTable.Field2) AS SumField2
FROM MyTable
GROUP BY MyTable.[Staff Name]

In the report, in the grouping and sorting select the sum fields
SumField1,
and sort by it.

SVL said:
I have a report where I have grouped entries by "Staff Name"
i.e. Work stats summed next to each staff members name.
However I want to sort it by one of the summed fields and not the
alphabetic
sort on the name.

format is

Staff Name 1 Sum([field 1]) Sum([field 2]) etc
Staff Name 2 Sum([field 1]) Sum([field 2]) etc
Staff Name 3 Sum([field 1]) Sum([field 2]) etc

I want it to sort on =Sum([field 1]) by descending value (its a
numerical
field) and not by the Staff Name.
How can I do this?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
This is the actual query I have thrown together.

As you can see I don't know SQL yet and I am getting a syntax error

Any ideas where I need to correct this?

Hours: SELECT tlkpInvestigator.[strInvName], Sum(tblClaimsInv.intHoursInv)
AS SumintHoursInv FROM tlkpInvestigator GROUP BY tlkpInvestigator.[strInvName]

thanks again
--
SVL


Duane Hookom said:
If you still need the details (which you would lose with Ofer's suggestion),
you could take his suggested query and add it to your report's record source
with the details records and join the [Staff Name] fields.

--
Duane Hookom
MS Access MVP


SVL said:
Thanks Ofer

I'll try this on Monday when I get back to work.
I am very new to Access and haven't used much SQL yet but I think I get
it...

--
SVL


Ofer said:
Create a record source for the report as Group by, and perform the sum in
the
SQL and not in the report

SELECT MyTable.[Staff Name], Sum(MyTable.Field1) AS SumField1,
Sum(MyTable.Field2) AS SumField2
FROM MyTable
GROUP BY MyTable.[Staff Name]

In the report, in the grouping and sorting select the sum fields
SumField1,
and sort by it.

:

I have a report where I have grouped entries by "Staff Name"
i.e. Work stats summed next to each staff members name.
However I want to sort it by one of the summed fields and not the
alphabetic
sort on the name.

format is

Staff Name 1 Sum([field 1]) Sum([field 2]) etc
Staff Name 2 Sum([field 1]) Sum([field 2]) etc
Staff Name 3 Sum([field 1]) Sum([field 2]) etc

I want it to sort on =Sum([field 1]) by descending value (its a
numerical
field) and not by the Staff Name.
How can I do this?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
You can't use a SQL statement as the control source of a control or possibly
where ever you have entered this.

It isn't clear if you want to use a subquery or what. Your SQL doesn't
include tblClaimsInv in the FROM clause.

Maybe you should provide some table structures, sample records, and expected
display in your report.

--
Duane Hookom
MS Access MVP


SVL said:
This is the actual query I have thrown together.

As you can see I don't know SQL yet and I am getting a syntax error

Any ideas where I need to correct this?

Hours: SELECT tlkpInvestigator.[strInvName], Sum(tblClaimsInv.intHoursInv)
AS SumintHoursInv FROM tlkpInvestigator GROUP BY
tlkpInvestigator.[strInvName]

thanks again
--
SVL


Duane Hookom said:
If you still need the details (which you would lose with Ofer's
suggestion),
you could take his suggested query and add it to your report's record
source
with the details records and join the [Staff Name] fields.

--
Duane Hookom
MS Access MVP


SVL said:
Thanks Ofer

I'll try this on Monday when I get back to work.
I am very new to Access and haven't used much SQL yet but I think I get
it...

--
SVL


:

Create a record source for the report as Group by, and perform the sum
in
the
SQL and not in the report

SELECT MyTable.[Staff Name], Sum(MyTable.Field1) AS SumField1,
Sum(MyTable.Field2) AS SumField2
FROM MyTable
GROUP BY MyTable.[Staff Name]

In the report, in the grouping and sorting select the sum fields
SumField1,
and sort by it.

:

I have a report where I have grouped entries by "Staff Name"
i.e. Work stats summed next to each staff members name.
However I want to sort it by one of the summed fields and not the
alphabetic
sort on the name.

format is

Staff Name 1 Sum([field 1]) Sum([field 2]) etc
Staff Name 2 Sum([field 1]) Sum([field 2]) etc
Staff Name 3 Sum([field 1]) Sum([field 2]) etc

I want it to sort on =Sum([field 1]) by descending value (its a
numerical
field) and not by the Staff Name.
How can I do this?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top