Sorting

[TVC@BNB]

I have created a report using an Excel linked table. The Excel file and
concurrent report contains a "lead status" column and there are about 5
different status. When entering the data on the excel file, it will be in a
scattered order in terms of the status. However, I would like to set a
specific order for the lead status to display on the report. Is this
possible? I should mention that I am a beginner Access user and created the
report through Report Wizard.

 

[pietlinden]

I have created a report using an Excel linked table. The Excel file and
concurrent report contains a "lead status" column and there are about 5
different status. When entering the data on the excel file, it will be in a
scattered order in terms of the status. However, I would like to set a
specific order for the lead status to display on the report. Is this
possible? I should mention that I am a beginner Access user and created the
report through Report Wizard.

In design view, go into sorting and grouping and modify the sort order
on your report.

 

[John W. Vinson]

I have created a report using an Excel linked table. The Excel file and
concurrent report contains a "lead status" column and there are about 5
different status. When entering the data on the excel file, it will be in a
scattered order in terms of the status. However, I would like to set a
specific order for the lead status to display on the report. Is this
possible? I should mention that I am a beginner Access user and created the
report through Report Wizard.

Open the Report in design view. Right mouseclick the little square at the
upper left and select "Sorting and Grouping" from the options.

This will give you a box in which you can specify the sort order on one or
more fields; you can optionally define group headers or footers (e.g. if you
want to display the count of records for each status in the group footer).

John W. Vinson [MVP]

 

[TVC@BNB]

Are ascending and descending the only sort options? I have a specific sort
order that I would like to assign and it's not necessarily in alphabetical
order.

 

[Douglas J. Steele]

Yes, ascending and descending are the only options.

What you can do is create another table that indicates the sort order you
want, join the two tables, and sort on the indicator from the 2nd table.

For example, if you wanted Red to be sorted before Yellow to be sorted
before Green, you'd create a table ColourSort:

Colour SortOrder
Red 1
Yellow 2
Green 3

You'd then join that table to your existing table:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

 

[TVC@BNB]

I understand creating the sort table, but am unsure where the following
action is taken:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

Where is this done?

Thanks again for your help.

 

[Douglas J. Steele]

That's the SQL for the query you'd need to use.

To build that same query using the graphical query builder, you'd create a
new query and add the MyTable and ColourSort tables to the query.

If it doesn't already exist, you'd create a relationship between MyTable and
ColourSort by dragging the Colour field from the MyTable table onto the
Colour field in the ColourSort table.

You'd then drag the fields from the MyTable table into the grid, as well as
dragging the SortOrder table from the ColourSort table.

Select Ascending from the Sort row under the SortOrder field.

 

[TVC@BNB]

I am getting a syntax error with the following sql statement:

SELECT [Sales Funnel v1.Lead Status], [Lead Status Sort Order.Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ORDER BY [Lead Status Sort Order.Sort Order]

Any suggestion?

 

[Douglas J. Steele]

Try

SELECT [Sales Funnel v1].[Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ON [Sales Funnel v1].[Lead Status] = [Lead Status Sort Order].[Lead Status]
ORDER BY [Lead Status Sort Order].[Sort Order]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am getting a syntax error with the following sql statement:

SELECT [Sales Funnel v1.Lead Status], [Lead Status Sort Order.Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ORDER BY [Lead Status Sort Order.Sort Order]

Any suggestion?

That's the SQL for the query you'd need to use.

To build that same query using the graphical query builder, you'd create
a
new query and add the MyTable and ColourSort tables to the query.

If it doesn't already exist, you'd create a relationship between MyTable
and
ColourSort by dragging the Colour field from the MyTable table onto the
Colour field in the ColourSort table.

You'd then drag the fields from the MyTable table into the grid, as well
as
dragging the SortOrder table from the ColourSort table.

Select Ascending from the Sort row under the SortOrder field.

 

[TVC@BNB]

That works and I can see that the sort is working correctly in my lead status
table, however, it's not linking to the report. In fact, now when I preview
the report, I am asked to enter a parameter value. I was expecting to be
able to run the report and have it sorted in the order that I entered in to
the sort query.

Thanks for all of your assistance.

Try

SELECT [Sales Funnel v1].[Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ON [Sales Funnel v1].[Lead Status] = [Lead Status Sort Order].[Lead Status]
ORDER BY [Lead Status Sort Order].[Sort Order]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am getting a syntax error with the following sql statement:

SELECT [Sales Funnel v1.Lead Status], [Lead Status Sort Order.Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ORDER BY [Lead Status Sort Order.Sort Order]

Any suggestion?

That's the SQL for the query you'd need to use.

To build that same query using the graphical query builder, you'd create
a
new query and add the MyTable and ColourSort tables to the query.

If it doesn't already exist, you'd create a relationship between MyTable
and
ColourSort by dragging the Colour field from the MyTable table onto the
Colour field in the ColourSort table.

You'd then drag the fields from the MyTable table into the grid, as well
as
dragging the SortOrder table from the ColourSort table.

Select Ascending from the Sort row under the SortOrder field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I understand creating the sort table, but am unsure where the following
action is taken:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

Where is this done?

Thanks again for your help.

:

Yes, ascending and descending are the only options.

What you can do is create another table that indicates the sort order
you
want, join the two tables, and sort on the indicator from the 2nd
table.

For example, if you wanted Red to be sorted before Yellow to be sorted
before Green, you'd create a table ColourSort:

Colour SortOrder
Red 1
Yellow 2
Green 3

You'd then join that table to your existing table:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Are ascending and descending the only sort options? I have a
specific
sort
order that I would like to assign and it's not necessarily in
alphabetical
order.

:

I have created a report using an Excel linked table. The Excel
file
and
concurrent report contains a "lead status" column and there are
about
5
different status. When entering the data on the excel file, it
will
be
in a
scattered order in terms of the status. However, I would like to
set
a
specific order for the lead status to display on the report. Is
this
possible? I should mention that I am a beginner Access user and
created
the
report through Report Wizard.

 

[TVC@BNB]

Any suggestions on this are greatly appreciated.

That works and I can see that the sort is working correctly in my lead status
table, however, it's not linking to the report. In fact, now when I preview
the report, I am asked to enter a parameter value. I was expecting to be
able to run the report and have it sorted in the order that I entered in to
the sort query.

Thanks for all of your assistance.

Try

SELECT [Sales Funnel v1].[Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ON [Sales Funnel v1].[Lead Status] = [Lead Status Sort Order].[Lead Status]
ORDER BY [Lead Status Sort Order].[Sort Order]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am getting a syntax error with the following sql statement:

SELECT [Sales Funnel v1.Lead Status], [Lead Status Sort Order.Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ORDER BY [Lead Status Sort Order.Sort Order]

Any suggestion?

:

That's the SQL for the query you'd need to use.

To build that same query using the graphical query builder, you'd create
a
new query and add the MyTable and ColourSort tables to the query.

If it doesn't already exist, you'd create a relationship between MyTable
and
ColourSort by dragging the Colour field from the MyTable table onto the
Colour field in the ColourSort table.

You'd then drag the fields from the MyTable table into the grid, as well
as
dragging the SortOrder table from the ColourSort table.

Select Ascending from the Sort row under the SortOrder field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I understand creating the sort table, but am unsure where the following
action is taken:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

Where is this done?

Thanks again for your help.

:

Yes, ascending and descending are the only options.

What you can do is create another table that indicates the sort order
you
want, join the two tables, and sort on the indicator from the 2nd
table.

For example, if you wanted Red to be sorted before Yellow to be sorted
before Green, you'd create a table ColourSort:

Colour SortOrder
Red 1
Yellow 2
Green 3

You'd then join that table to your existing table:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Are ascending and descending the only sort options? I have a
specific
sort
order that I would like to assign and it's not necessarily in
alphabetical
order.

:

I have created a report using an Excel linked table. The Excel
file
and
concurrent report contains a "lead status" column and there are
about
5
different status. When entering the data on the excel file, it
will
be
in a
scattered order in terms of the status. However, I would like to
set
a
specific order for the lead status to display on the report. Is
this
possible? I should mention that I am a beginner Access user and
created
the
report through Report Wizard.

 

[Douglas J. Steele]

If you're being prompted for a parameter, you've likely included a field
that isn't in the report's underlying recordsource.

And remember that reports do not respect the order of records from the
underlying recordsource: you must use the report's Sorting and Grouping
dialog to set the sort order correctly in the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Any suggestions on this are greatly appreciated.

That works and I can see that the sort is working correctly in my lead
status
table, however, it's not linking to the report. In fact, now when I
preview
the report, I am asked to enter a parameter value. I was expecting to be
able to run the report and have it sorted in the order that I entered in
to
the sort query.

Thanks for all of your assistance.

Try

SELECT [Sales Funnel v1].[Lead Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ON [Sales Funnel v1].[Lead Status] = [Lead Status Sort Order].[Lead
Status]
ORDER BY [Lead Status Sort Order].[Sort Order]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am getting a syntax error with the following sql statement:

SELECT [Sales Funnel v1.Lead Status], [Lead Status Sort Order.Lead
Status]
FROM [Sales Funnel v1s] INNER JOIN [Lead Status Sort Orders]
ORDER BY [Lead Status Sort Order.Sort Order]

Any suggestion?

:

That's the SQL for the query you'd need to use.

To build that same query using the graphical query builder, you'd
create
a
new query and add the MyTable and ColourSort tables to the query.

If it doesn't already exist, you'd create a relationship between
MyTable
and
ColourSort by dragging the Colour field from the MyTable table onto
the
Colour field in the ColourSort table.

You'd then drag the fields from the MyTable table into the grid, as
well
as
dragging the SortOrder table from the ColourSort table.

Select Ascending from the Sort row under the SortOrder field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I understand creating the sort table, but am unsure where the
following
action is taken:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

Where is this done?

Thanks again for your help.

:

Yes, ascending and descending are the only options.

What you can do is create another table that indicates the sort
order
you
want, join the two tables, and sort on the indicator from the 2nd
table.

For example, if you wanted Red to be sorted before Yellow to be
sorted
before Green, you'd create a table ColourSort:

Colour SortOrder
Red 1
Yellow 2
Green 3

You'd then join that table to your existing table:

SELECT MyTable.Field1, MyTable.Field2, MyTable.Colour
FROM MyTable INNER JOIN ColourSort
ON MyTable.Colour = ColourSort.Colour
ORDER BY ColourSort.SortOrder

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Are ascending and descending the only sort options? I have a
specific
sort
order that I would like to assign and it's not necessarily in
alphabetical
order.

:

I have created a report using an Excel linked table. The
Excel
file
and
concurrent report contains a "lead status" column and there
are
about
5
different status. When entering the data on the excel file,
it
will
be
in a
scattered order in terms of the status. However, I would like
to
set
a
specific order for the lead status to display on the report.
Is
this
possible? I should mention that I am a beginner Access user
and
created
the
report through Report Wizard.