sort in report

I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name – text field
Security Type – text (can be equal to ‘S’, ‘P’, or ‘C’)
Option Description – this field contains 3 types of information – a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First – by security
Second – by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth – by option month, year and price

I'm still in the design process so changes can be made. Thanks.

You can base the report on a query that is sorted as you describe or you can
set up GROUPS in the report. First group being Security Name, second group
Security Type, etc.

Bonnie
http://www.dataplus-svc.com

lake2212 wrote:
I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name – text field
Security Type – text (can be equal to ‘S’, ‘P’, or ‘C’)
Option Description – this field contains 3 types of information – a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First – by security
Second – by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth – by option month, year and price

I'm still in the design process so changes can be made. Thanks.

--
Message posted via http://www.accessmonster.com

I understand that. But I don't know how to make it sort this particular way.

How can I sort based on what the value is in the field? The first question
is how to make the "Type" field sort with a value of "S' coming first, a
value of "C" coming second and a value of "P" coming third. Thanks.
Julie

"bhicks11 via AccessMonster.com" wrote:

You can base the report on a query that is sorted as you describe or you can
set up GROUPS in the report. First group being Security Name, second group
Security Type, etc.

Bonnie
http://www.dataplus-svc.com

lake2212 wrote:
I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name – text field
Security Type – text (can be equal to ‘S’, ‘P’, or ‘C’)
Option Description – this field contains 3 types of information – a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First – by security
Second – by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth – by option month, year and price

I'm still in the design process so changes can be made. Thanks.

--
Message posted via http://www.accessmonster.com

Sorry Julie - I missed that.

A quick and easy would be to add a field - put an "A" in for every type = "S",
"B" for "C" and "C" for "P" and group or sort on it.

Bonnie
http://www.dataplus-svc.com

lake2212 wrote:
I understand that. But I don't know how to make it sort this particular way.

How can I sort based on what the value is in the field? The first question
is how to make the "Type" field sort with a value of "S' coming first, a
value of "C" coming second and a value of "P" coming third. Thanks.
Julie

You can base the report on a query that is sorted as you describe or you can
set up GROUPS in the report. First group being Security Name, second group
[quoted text clipped - 18 lines]

I'm still in the design process so changes can be made. Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1

On Tue, 15 Jul 2008 04:55:00 -0700, lake2212 wrote:

I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name ¡V text field
Security Type ¡V text (can be equal to ¡¥S¡¦, ¡¥P¡¦, or ¡¥C¡¦)
Option Description ¡V this field contains 3 types of information ¡V a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First ¡V by security
Second ¡V by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth ¡V by option month, year and price

I'm still in the design process so changes can be made. Thanks.

1) Add a new column to your query.
SortThis:IIf([SecurityType] = "S",1,IIf([SecurityType] = "C",2,3)

2) Tho proper place to sort your report is in the report's Sorting and
Grouping dialog (not in the query).
In Report Design View, click on View + Sorting and Grouping

In the Field/Expression column and Sort columns, enter:
[SecurityName] Ascending
[SortThis] Ascending

The above will sort the report by SecurityName then SecurityType in
"S", "C", "P" order.

3) As far as then sorting according to the value in your
OptionDescription field, NO you won't be able to.
OptionDescription is a Text (or Memo) datatype field.
You will not be able to sort Jan before Apr because they are not seen
by Access as dates, so Apr would sort before Jan. Also, in a text
field, 12.50 would sort before 4.50.

Your database is not normalized. You should have 2 more fields. One
field as Date datatype, and another as Number datatype. You enter the
date in one field (a valid date, containing month, day, and year) and
the other field enter the price.
Then it would be a simple matter to just add those two fields to the
report's Sorting and Grouping dialog so that they are included in the
sorting.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Great, I got the first 2 things done. On #3 below - I only want the user to
enter a month and year, so how would I go about that but still be able to
sort on the date? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 04:55:00 -0700, lake2212 wrote:

I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name – text field
Security Type – text (can be equal to ‘S’, ‘P’, or ‘C’)
Option Description – this field contains 3 types of information – a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First – by security
Second – by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth – by option month, year and price

I'm still in the design process so changes can be made. Thanks.

1) Add a new column to your query.
SortThis:IIf([SecurityType] = "S",1,IIf([SecurityType] = "C",2,3)

2) Tho proper place to sort your report is in the report's Sorting and
Grouping dialog (not in the query).
In Report Design View, click on View + Sorting and Grouping

In the Field/Expression column and Sort columns, enter:
[SecurityName] Ascending
[SortThis] Ascending

The above will sort the report by SecurityName then SecurityType in
"S", "C", "P" order.

3) As far as then sorting according to the value in your
OptionDescription field, NO you won't be able to.
OptionDescription is a Text (or Memo) datatype field.
You will not be able to sort Jan before Apr because they are not seen
by Access as dates, so Apr would sort before Jan. Also, in a text
field, 12.50 would sort before 4.50.

Your database is not normalized. You should have 2 more fields. One
field as Date datatype, and another as Number datatype. You enter the
date in one field (a valid date, containing month, day, and year) and
the other field enter the price.
Then it would be a simple matter to just add those two fields to the
report's Sorting and Grouping dialog so that they are included in the
sorting.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

Great, I got the first 2 things done. On #3 below - I only want the user to
enter a month and year, so how would I go about that but still be able to
sort on the date? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 04:55:00 -0700, lake2212 wrote:

I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name ¡V text field
Security Type ¡V text (can be equal to ¡¥S¡¦, ¡¥P¡¦, or ¡¥C¡¦)
Option Description ¡V this field contains 3 types of information ¡V a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First ¡V by security
Second ¡V by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth ¡V by option month, year and price

I'm still in the design process so changes can be made. Thanks.

1) Add a new column to your query.
SortThis:IIf([SecurityType] = "S",1,IIf([SecurityType] = "C",2,3)

2) Tho proper place to sort your report is in the report's Sorting and
Grouping dialog (not in the query).
In Report Design View, click on View + Sorting and Grouping

In the Field/Expression column and Sort columns, enter:
[SecurityName] Ascending
[SortThis] Ascending

The above will sort the report by SecurityName then SecurityType in
"S", "C", "P" order.

3) As far as then sorting according to the value in your
OptionDescription field, NO you won't be able to.
OptionDescription is a Text (or Memo) datatype field.
You will not be able to sort Jan before Apr because they are not seen
by Access as dates, so Apr would sort before Jan. Also, in a text
field, 12.50 would sort before 4.50.

Your database is not normalized. You should have 2 more fields. One
field as Date datatype, and another as Number datatype. You enter the
date in one field (a valid date, containing month, day, and year) and
the other field enter the price.
Then it would be a simple matter to just add those two fields to the
report's Sorting and Grouping dialog so that they are included in the
sorting.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I added the date field and it is working, but I cannot get it to sort by the
date on my report. I defined the date field "Option Date" as data type
"Date/Time". Then I added "Option Date" to my Sorting and Grouping in the
report. What could I be doing wrong? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

Great, I got the first 2 things done. On #3 below - I only want the user to
enter a month and year, so how would I go about that but still be able to
sort on the date? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 04:55:00 -0700, lake2212 wrote:

I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name – text field
Security Type – text (can be equal to ‘S’, ‘P’, or ‘C’)
Option Description – this field contains 3 types of information – a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First – by security
Second – by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth – by option month, year and price

I'm still in the design process so changes can be made. Thanks.

1) Add a new column to your query.
SortThis:IIf([SecurityType] = "S",1,IIf([SecurityType] = "C",2,3)

2) Tho proper place to sort your report is in the report's Sorting and
Grouping dialog (not in the query).
In Report Design View, click on View + Sorting and Grouping

In the Field/Expression column and Sort columns, enter:
[SecurityName] Ascending
[SortThis] Ascending

The above will sort the report by SecurityName then SecurityType in
"S", "C", "P" order.

3) As far as then sorting according to the value in your
OptionDescription field, NO you won't be able to.
OptionDescription is a Text (or Memo) datatype field.
You will not be able to sort Jan before Apr because they are not seen
by Access as dates, so Apr would sort before Jan. Also, in a text
field, 12.50 would sort before 4.50.

Your database is not normalized. You should have 2 more fields. One
field as Date datatype, and another as Number datatype. You enter the
date in one field (a valid date, containing month, day, and year) and
the other field enter the price.
Then it would be a simple matter to just add those two fields to the
report's Sorting and Grouping dialog so that they are included in the
sorting.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Tue, 15 Jul 2008 14:06:00 -0700, lake2212 wrote:

I added the date field and it is working, but I cannot get it to sort by the
date on my report. I defined the date field "Option Date" as data type
"Date/Time". Then I added "Option Date" to my Sorting and Grouping in the
report. What could I be doing wrong? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 09:54:05 -0700, lake2212 wrote:

Great, I got the first 2 things done. On #3 below - I only want the user to
enter a month and year, so how would I go about that but still be able to
sort on the date? Thanks.
Julie

"fredg" wrote:

On Tue, 15 Jul 2008 04:55:00 -0700, lake2212 wrote:

I am setting up a new database. I will have a table with the following
fields I would like to sort on in a report:

Security Name ¡V text field
Security Type ¡V text (can be equal to ¡¥S¡¦, ¡¥P¡¦, or ¡¥C¡¦)
Option Description ¡V this field contains 3 types of information ¡V a month, a
year, a price (examples: Jan-08-25, Feb-07-3.50) (I'd like not to print
decimals unless they are entered by the user)

Is there a way to sort on all these fields in a report?
First ¡V by security
Second ¡V by type (but I want the records to print in this order 'S', 'C', 'P')
Third, forth and fifth ¡V by option month, year and price

I'm still in the design process so changes can be made. Thanks.

1) Add a new column to your query.
SortThis:IIf([SecurityType] = "S",1,IIf([SecurityType] = "C",2,3)

2) Tho proper place to sort your report is in the report's Sorting and
Grouping dialog (not in the query).
In Report Design View, click on View + Sorting and Grouping

In the Field/Expression column and Sort columns, enter:
[SecurityName] Ascending
[SortThis] Ascending

The above will sort the report by SecurityName then SecurityType in
"S", "C", "P" order.

3) As far as then sorting according to the value in your
OptionDescription field, NO you won't be able to.
OptionDescription is a Text (or Memo) datatype field.
You will not be able to sort Jan before Apr because they are not seen
by Access as dates, so Apr would sort before Jan. Also, in a text
field, 12.50 would sort before 4.50.

Your database is not normalized. You should have 2 more fields. One
field as Date datatype, and another as Number datatype. You enter the
date in one field (a valid date, containing month, day, and year) and
the other field enter the price.
Then it would be a simple matter to just add those two fields to the
report's Sorting and Grouping dialog so that they are included in the
sorting.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Create a new field Date datatype.
Do NOT name the field "Date", as "Date" is an Access reserved word.
You can use "OrderDate", "SalesDate", "dteDate", etc.

A valid date (month, day, year) must be stored.
If the user enters just a month/year, Access will assume the first day
of the month, so enter 7/08 and Access will store it as 7/1/2008
(note: It's actually stored as a number... 7/1/2008 is stored as 39630
.... so the format is irrelevant).
However... 7/08 is ambiguous.
Does the user mean July 2008 or July 8th. If Access assumes July 8th
it will default to 7/8 2008, the current year.
Therefore you should always use a 4 digit year and avoid confusing
Access. Enter 7/2008 and Access will know you mean July 2008.

As you can readily see from the above, as Access stores 39630 for
7/1/2008, sorting becomes very easy, as it's just a numerical sort and
the actual month name is not important.

Good Luck.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Did you include the [Option Date] field in the record source of the
report (the query)?


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

"bhicks11 via AccessMonster.com" u44327@uwe wrote in message
news:87303282b5516@uwe...
Sorry Julie - I missed that.

A quick and easy would be to add a field - put an "A" in for every type =
"S",
"B" for "C" and "C" for "P" and group or sort on it.

Well... I wouldn't really recommend that. This new field would be redundant,
and could very easily get out of synch.

Instead base the report on a query with a calculated field:

SortGroup: Switch([Type] = "S", 1, [Type] = "C", 2, [Type] = "P", 3, True,
100)

to create a calculated field which can then be used for sorting. Use 0
instead of 100 if you want unspecified Type values to sort first.