Sorting Multiple Columns Independently

M

marycanyon

I have a workers' compensation report which shows location names and incident
rates for the past 5 years. I want to be able to sort each years' column
from high to low ... can that be done?

2004 2005
Maple Heights, OH 31.53 Maple Heights, OH 27.60
St. Louis, MO 20.49 Riverside, MO 25.23
Indianapolis, IN 19.77 Cleveland, OH 22.74
Cleveland, OH 17.09 St. Louis, MO 17.25
Las Vegas, NV 13.12 Indianapolis, IN 14.18
Riverside, MO 13.08 Las Vegas, NV 12.03

Thank you!
 
M

Marshall Barton

marycanyon said:
I have a workers' compensation report which shows location names and incident
rates for the past 5 years. I want to be able to sort each years' column
from high to low ... can that be done?

2004 2005
Maple Heights, OH 31.53 Maple Heights, OH 27.60
St. Louis, MO 20.49 Riverside, MO 25.23
Indianapolis, IN 19.77 Cleveland, OH 22.74
Cleveland, OH 17.09 St. Louis, MO 17.25
Las Vegas, NV 13.12 Indianapolis, IN 14.18
Riverside, MO 13.08 Las Vegas, NV 12.03
Click to expand...


Use Sorting and Grouping (View menu) and enter two rows:
yearfield
amountfield

If you specify a group header for the year group, then put
the yearfield textbox in the group header and set the
headersection's NewRowOrCol property to Yes.

Set the report's column snaking to Down then Across
 
M

marycanyon

This didn't work. I don't have a group header so I didn't do that step. I
don't know how relevant the column snaking is because I only have one column
according to the page setup feature and the down then across is "greyed" out.
 
M

Marshall Barton

Then I have to ask how you are getting two "columns".

If the data for two years is all in each record source
record, then the answer is No, you can not sort the
"columns" differently. I won;t be able to make a meaningful
suggestion until I see the report's record source list of
fields and the arrangement of controls in the report.
 
M

marycanyon

How can I get you the "the report's record source list of > fields and the
arrangement of controls in the report"?

Marshall Barton said:
Then I have to ask how you are getting two "columns".

If the data for two years is all in each record source
record, then the answer is No, you can not sort the
"columns" differently. I won;t be able to make a meaningful
suggestion until I see the report's record source list of
fields and the arrangement of controls in the report.
--
Marsh
MVP [MS Access]

This didn't work. I don't have a group header so I didn't do that step. I
don't know how relevant the column snaking is because I only have one column
according to the page setup feature and the down then across is "greyed" out.
Click to expand...
Click to expand...
 
M

Marshall Barton

If the report's record source is a query, then Copy/Paste
its SQL view. If it's a table, then just type the relevant
fields as a list. I suspect that you have an unnormalized
table with fields like
address04 Text
amount04 Currency
address05 Text
amount05 Currency

Then describe the layout of the sections and their controls.
E.g. I think your detail section contains four bound text
boxes side by side something like:
address04 amount04 address05 amount05

If my guess as to your table is reasonably close, then you
will not be able to get the report the way you want using
that record source. The table structure would need to be
normalized so there are no repeating fields (or faked by
using a Union query) so the report's record source looks
more like:
year
address
amount
 
M

marycanyon

The record source is a query and the SQL is shown below:
SELECT [2005 Incident Rates].[Location Name], [2005 Incident
Rates].[Incident Rate], [2006 Incident Rates].[Incident Rate], [2007 Incident
Rates].[Incident Rate], [Current Quarter Incident Rates].[Incident Rate],
[2005 Incurred].Incurred, [2006 Incurred].Incurred, [2007 Incurred].Incurred,
[Current Quarter Incurred].Incurred
FROM (((([2005 Incident Rates] INNER JOIN [2006 Incident Rates] ON [2005
Incident Rates].LocNo=[2006 Incident Rates].LocNo) INNER JOIN [2007 Incident
Rates] ON [2006 Incident Rates].LocNo=[2007 Incident Rates].LocNo) INNER JOIN
[Current Quarter Incident Rates] ON [2007 Incident Rates].LocNo=[Current
Quarter Incident Rates].LocNo) INNER JOIN (([2005 Incurred] INNER JOIN [2006
Incurred] ON [2005 Incurred].LocNo=[2006 Incurred].LocNo) INNER JOIN [2007
Incurred] ON [2006 Incurred].LocNo=[2007 Incurred].LocNo) ON [Current Quarter
Incident Rates].LocNo=[2005 Incurred].LocNo) INNER JOIN [Current Quarter
Incurred] ON [2007 Incurred].LocNo=[Current Quarter Incurred].LocNo
ORDER BY [Current Quarter Incurred].Incurred;

The Detail section of my report shows:
Location Name & 2005 Incident Rate
Location Name & 2005 Incurred
Location Name & 2006 Incident Rate
Location Name & 2006 Incurred
Location Name & 2007 Incident Rate
Location Name & 2007 Incurred
Location Name & Current QTD Incident Rate
Location Name & Current QTD Incurred

The table that the query runs from has the following 8 fields:
LocNO (text)
Location Name (text)
Year (text)
Quarter (text)
Incident Rate (number)
Incurred (currency)
Payroll (currency)
Loss Rate (number)

Does this answer all of your questions?

Marshall Barton said:
If the report's record source is a query, then Copy/Paste
its SQL view. If it's a table, then just type the relevant
fields as a list. I suspect that you have an unnormalized
table with fields like
address04 Text
amount04 Currency
address05 Text
amount05 Currency

Then describe the layout of the sections and their controls.
E.g. I think your detail section contains four bound text
boxes side by side something like:
address04 amount04 address05 amount05

If my guess as to your table is reasonably close, then you
will not be able to get the report the way you want using
that record source. The table structure would need to be
normalized so there are no repeating fields (or faked by
using a Union query) so the report's record source looks
more like:
year
address
amount
--
Marsh
MVP [MS Access]

How can I get you the "the report's record source list of > fields and the
arrangement of controls in the report"?
Click to expand...
Click to expand...
 
M

Marshall Barton

marycanyon said:
The record source is a query and the SQL is shown below:
SELECT [2005 Incident Rates].[Location Name], [2005 Incident
Rates].[Incident Rate], [2006 Incident Rates].[Incident Rate], [2007 Incident
Rates].[Incident Rate], [Current Quarter Incident Rates].[Incident Rate],
[2005 Incurred].Incurred, [2006 Incurred].Incurred, [2007 Incurred].Incurred,
[Current Quarter Incurred].Incurred
FROM (((([2005 Incident Rates] INNER JOIN [2006 Incident Rates] ON [2005
Incident Rates].LocNo=[2006 Incident Rates].LocNo) INNER JOIN [2007 Incident
Rates] ON [2006 Incident Rates].LocNo=[2007 Incident Rates].LocNo) INNER JOIN
[Current Quarter Incident Rates] ON [2007 Incident Rates].LocNo=[Current
Quarter Incident Rates].LocNo) INNER JOIN (([2005 Incurred] INNER JOIN [2006
Incurred] ON [2005 Incurred].LocNo=[2006 Incurred].LocNo) INNER JOIN [2007
Incurred] ON [2006 Incurred].LocNo=[2007 Incurred].LocNo) ON [Current Quarter
Incident Rates].LocNo=[2005 Incurred].LocNo) INNER JOIN [Current Quarter
Incurred] ON [2007 Incurred].LocNo=[Current Quarter Incurred].LocNo
ORDER BY [Current Quarter Incurred].Incurred;

The Detail section of my report shows:
Location Name & 2005 Incident Rate
Location Name & 2005 Incurred
Location Name & 2006 Incident Rate
Location Name & 2006 Incurred
Location Name & 2007 Incident Rate
Location Name & 2007 Incurred
Location Name & Current QTD Incident Rate
Location Name & Current QTD Incurred

The table that the query runs from has the following 8 fields:
LocNO (text)
Location Name (text)
Year (text)
Quarter (text)
Incident Rate (number)
Incurred (currency)
Payroll (currency)
Loss Rate (number)

Does this answer all of your questions?
Click to expand...


Almost, but not quite. You described a noramlized table,
but the query uses 4 table/query nanes. If those are the
names of queries that extract records for the different
years, then the issue is the denormalization of the query.
In this situation you can use the table as the report's
record source, group on the year field and use a 2 (or
more?) columns to get the desired result.

OTOH, if there really are 4 (6?) separate tables (a;; with
the same field structure) for the different time periods,
then the tables are not normalized. They should be combined
into a single table with an additional field to identify the
time beriod (maybe the Year field serves this purpose?). If
this is what you have, then you can fake the normalized
table by using a Union query and use that as the record
source.
 
M

marycanyon

I only have one table but I did 15 queries of that table and called them:
2004 Incident Rates
2005 Incident Rates
2006 Incident Rates
2007 Incident Rates
Current Quarter Incident Rates
2004 Incurred
2005 Incurred
2006 Incurred
2007 Incurred
Current Quarter Incurred
2004 Loss Rates
2005 Loss Rates
2006 Loss Rates
2007 Loss Rates
Current Quarter Loss Rates

Then I created the query shown below from 8 of the queries shown above.

Marshall Barton said:
marycanyon said:
The record source is a query and the SQL is shown below:
SELECT [2005 Incident Rates].[Location Name], [2005 Incident
Rates].[Incident Rate], [2006 Incident Rates].[Incident Rate], [2007 Incident
Rates].[Incident Rate], [Current Quarter Incident Rates].[Incident Rate],
[2005 Incurred].Incurred, [2006 Incurred].Incurred, [2007 Incurred].Incurred,
[Current Quarter Incurred].Incurred
FROM (((([2005 Incident Rates] INNER JOIN [2006 Incident Rates] ON [2005
Incident Rates].LocNo=[2006 Incident Rates].LocNo) INNER JOIN [2007 Incident
Rates] ON [2006 Incident Rates].LocNo=[2007 Incident Rates].LocNo) INNER JOIN
[Current Quarter Incident Rates] ON [2007 Incident Rates].LocNo=[Current
Quarter Incident Rates].LocNo) INNER JOIN (([2005 Incurred] INNER JOIN [2006
Incurred] ON [2005 Incurred].LocNo=[2006 Incurred].LocNo) INNER JOIN [2007
Incurred] ON [2006 Incurred].LocNo=[2007 Incurred].LocNo) ON [Current Quarter
Incident Rates].LocNo=[2005 Incurred].LocNo) INNER JOIN [Current Quarter
Incurred] ON [2007 Incurred].LocNo=[Current Quarter Incurred].LocNo
ORDER BY [Current Quarter Incurred].Incurred;

The Detail section of my report shows:
Location Name & 2005 Incident Rate
Location Name & 2005 Incurred
Location Name & 2006 Incident Rate
Location Name & 2006 Incurred
Location Name & 2007 Incident Rate
Location Name & 2007 Incurred
Location Name & Current QTD Incident Rate
Location Name & Current QTD Incurred

The table that the query runs from has the following 8 fields:
LocNO (text)
Location Name (text)
Year (text)
Quarter (text)
Incident Rate (number)
Incurred (currency)
Payroll (currency)
Loss Rate (number)

Does this answer all of your questions?
Click to expand...


Almost, but not quite. You described a normalized table,
but the query uses 4 table/query names. If those are the
names of queries that extract records for the different
years, then the issue is the denormalization of the query.
In this situation you can use the table as the report's
record source, group on the year field and use a 2 (or
more?) columns to get the desired result.

OTOH, if there really are 4 (6?) separate tables (a;; with
the same field structure) for the different time periods,
then the tables are not normalized. They should be combined
into a single table with an additional field to identify the
time beriod (maybe the Year field serves this purpose?). If
this is what you have, then you can fake the normalized
table by using a Union query and use that as the record
source.
Click to expand...
 
M

Marshall Barton

Then the comglomeration of queries is the problem.

Start over with a simple query that just selects the desired
records from the table and use it as the record source.

SELECT thetable.Location, thetable.Year,
Sum(thetable.incedent) As Total
FROM thetable
WHERE thetable.Year IN(2004,2005)
GROUP BY thetable.Location, thetable.Year

Then use the report's Sorting and Grouping (View menu) to
group by the year and sort by location. Use Page Setup
(File menu) to arrange the groups in multiple columns.
 
Top