[AccessD] Report Ordering

Hi, Reuben

I don't know if I can explain this clearly enough to be helpful to you,
but I had a similar requirement and took the following approach.

In my situation, I wanted just to group according to user preferences,
but I think something like this could work for sorting as well.  Here's
what I did:

1.  Built a query using generic aliases for the variable
grouping/sorting fields (EmployeeName as GroupSortVal1, EmployeeNumber
as GroupSortVal2, etc.)

2.  Designed a report based on the query, using the generic field names
as the group/sort values.

3.  Designed a report spec form with combo boxe(s) offering a selection
of grouping options.

4.  User selects the desired group field(s), and I use the choice(s) to
build the SQL for a new query, assigning the chosen grouping fields to
the appropriate aliases.

5.  Delete the existing query for the report and add the new one using
the same name.

6.  Open the report.

Since the grouping/sorting is being done in the report on generic
aliases, the report doesn't care which fields/values from the query the
aliases represent.

HTH

Don






-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Reuben
Cummings
Sent: Thursday, January 13, 2005 12:08 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Report Ordering


No.  This is two separate fields.  One is Employee number.  The other is
Employee Name.  Some clients will want it sorted by name and others by
number.

Currently, the sort and grouping looks like

department, 	ascending
employee#, 	ascending
order,		ascending (this is a special field for sorting because
some clients
actually have people with the same name)
calcrate	ascending (simply puts regular time ahead of overtime or
double
time)

To change this to numerical I need something like this
department, 	ascending
name, 		ascending
employee#	ascending
order,		ascending
calcrate	ascending

I have to have these groups so that the payroll shows correctly.  I
could
change the employee# in the first option above to name and simply leave
out
the employee# field from the sort.

Like this
department, 	ascending
name, 		ascending
order,		ascending
calcrate	ascending

I think I may be onto my answer, but I still have to look into
manipulating
the sort groups.

Reuben Cummings
GFC, LLC
phone: 812.523.1017
email: reuben at gfconsultants.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Andy Lacey
Sent: Thursday, January 13, 2005 2:47 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Report Ordering


Reuben
If I've understood right, what you need to do is define both groups but
then
base them on calculated fields in your Query. The calculated fields
would
return identical values for the unselected criterion. Something like:

SELECT Iif(frm!txtSort="Alpha",0,[fldNumeric]) As NumericKey,
Iif(frm!txtSort="Alpha",[fldAlpha],"") As AlphaKey

-- Andy Lacey
http://www.minstersystems.co.uk

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Reuben Cummings
> Sent: 13 January 2005 19:09
> To: AccessD
> Subject: [AccessD] Report Ordering
>
>
> I have a report that currently has 4 entries in Sorting and Grouping.
>
> I now have a couple clients that need this report to be
> alphabetical before numerical.  Currently alphabetical is not
> even an option.  Alpha would need to be the 2nd entry and the
> other 3 should move down to 3rd, 4th, and 5th.
>
> How do I work with and edit the sorting and grouping based on
> user criteria. I figured I would place a check box on the
> print form so the user could select numberical or alpha.
>
> Thanks.
>
> Reuben Cummings
> GFC, LLC
> phone: 812.523.1017
> email: reuben at gfconsultants.com
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com