Help with Sorting Queries/Reports

[vpboy_22]

Hello I'm at a loss with sorting some data. I have a database program in 2000 file format but I'm using 2003 access. We use this program for staffing. I have a table named time sheet and I created a query to calculate some time diffrences and I also use the query to enter parameters for pull up data for certin dates. The problem is that I need to sort my nurses name in acending order. I have selected acending order from the drop down menu in sort in the query it still does not work. I also created a report from the query and it does not sort either and i have selected acending order in the report design view and it does not work. I have sorted all of my other queries and reports with out any problems. Can someone help me please. Thanks in advance!!

 

[Pat Hartman]

In a report, the report's sorting and grouping options override any sort criteria in the underlying query.

Are you certain that no other column has a sort sequence assigned?

 

[vpboy_22]

RE Sorting

No other colum in my query or my report is sorted exept for nurses name and its sorted in acending order. But it does not sort. Everyother query I have sorts in acending order by Nurses name which is tied into the table new nurses. When I run a report from the querys that are sorted the report is sorted the way the query is sorted with out any sorting options selected in the report. But the only one is the time sheet table query and report it will not sort no matter what. I tried deleteing the table and the query and the report and i have done it over and over again. Please help. I can even send a copy of the database for you to see how this is not working!

 

[Pat Hartman]

Just post the query.

 

[vpboy_22]

Re Post Query

How do you do that?

 

[vpboy_22]

Here is the SQL Code if that is what you were referring to

SELECT [Time Sheet].[Nurses Name], [Time Sheet].Title, [Time Sheet].Facility, [Time Sheet].[SP/NON], [Time Sheet].[Week Beginning], [Time Sheet].[Week Ending], [Time Sheet].[Date and time in], [Time Sheet].[Date and time out], [Date and time out]-[Date and time in] AS Subtotal, [Time Sheet].Lunch, [Subtotal]-[Lunch] AS Total, [Time Sheet].REG, [Time Sheet].OT, [Time Sheet].[Pay Rate], [Time Sheet].[Date Paid], [Time Sheet].[Weekly/Daily], [Time Sheet].[Special Rate/Holiday/Notes]
FROM [Time Sheet]
WHERE ((([Time Sheet].[Week Beginning])=[Enter date of week beginning]) AND (([Time Sheet].[Week Ending])=[Enter date of week ending]) AND (([Time Sheet].[Weekly/Daily])=[View Weekly/Daily]))
ORDER BY [Time Sheet].[Nurses Name];

 

[Pat Hartman]

Why are you using only a single field for a name? At a minimum you should have FirstName and LastName.

There is nothing wrong with the order by clause. Is there something wrong with your data?

 

[vpboy_22]

I only use one feild

I only use one feild for Nurses name I want there name sorted acending by first name that is just our procedure for the company. But it still does not work even though you said the sort clause works. there is nothing wrong with my data because it works just fine in all the other querys and tables. Thanks!

 

[Pat Hartman]

Does the data in the name column have leading spaces in some cases? If it does, you can get rid of them by running an update query with the following in the UpdateTo cell:

Trim([Time Sheet].[Nurses Name])

Do I need to remind you to make a back up PRIOR to running a bulk update?

 

[vpboy_22]

Thanks I will try

I will try that but what will it do to fix my problem. Oh and by the way thanks for all the help so far!

 

[Pat Hartman]

If some records have leading spaces and others don't, the records will appear to be unsorted.

You could post a small spreadsheet with your "unsorted" data so we can see what you're seeing.