I believe I have the viewing and sorting correct, but I thought I would post
my SQL and maybe you would see something wrong in that.
SELECT [Total Query].Outpost, [Total Query].ClassNo, [Total
Query].EmployeeName, tblEmployees.SeniorityDate, tblEmployees.HomePhone,
tblEmployees.CellPhone, tblEmployees.Pagers, odotqry.[Total Hours]
FROM ([Total Query] INNER JOIN tblEmployees ON [Total Query].Tmsemployeeno =
tblEmployees.Tmsemployeeno) INNER JOIN odotqry ON tblEmployees.Tmsemployeeno
= odotqry.Tmsemployeeno
ORDER BY tblEmployees.SeniorityDate;
:
Open your report in design view, click on menu VIEW - Sorting And Grouping.
Select the overtime hours totals field and Descinding order That is if you
want the one with the most overtime). Then below that select the 'seniority
date' field.
--
KARL DEWEY
Build a little - Test a little
:
It is in the query, I misspoke. Just can't figure how to make it sort with
the most senior (seniority date) first when they have the same total year to
date hours.
Duane Hookom" wrote:
Regarding the seniority date, you stated "because it is not in the query".
Your report can't sort on a field that isn't displayed in the report's field
list.
--
Duane Hookom
Microsoft Access MVP
:
OK, I went back into the tblEmployees field and typed in 4/21 and I get an
error, it does not change the field to the date of 4/21/2008. I took out
the input mask and now I can type 4-21 and it populates the rest of the
field. I went back and looked at my report and it still has the 3 employees
who have a total of 16.4 ytd overtime hours sorted incorrectly. First
seniority date 05-30-2000, second seniority date 06-18-1990 and third
seniority date of 06-23-1986. It should be reporting the dates the oposite.
Should I possibly put in the "Orderby" of the report,
(Format[Senioritydate[, "yyyy") Would something like that work?
:
A fast simple test of your date field is to type 4/21 and press ENTER.
If the Access field is DateTime datatype it will change the 4/21 to
4/21/2008. If it is a text field it will remain 4/21.
--
KARL DEWEY
Build a little - Test a little
:
Sorry, but I am a bit confused. Back in the table I have the Seniority
Date as a Date/Time field, and the format at the bottom is Short Date. When
I wrote the report I have the seniority date coming from tblEmployees, is
that possibly where the problem is because it is not in the query? I have
checked and rechecked everything you guys have said, but if I am looking at
it correct it appears that I have everything ok. Anything else I can check?
:
Fredg is correct as usual. Your statement "I also have it set up as a short
date" raises a red flag. You don't normally provide a format to a numeric or
date value you want sort on. Leave it alone.
Also, are you sure the Total Hours doesn't include more decimal places that
aren't displayed?
--
Duane Hookom
Microsoft Access MVP
:
I do have it as a "Date Datatye". I am sorting in the report, I am first
sorting by a ClassNo, 2nd, Total Hours, and 3rd SeniorityDate. I have the
SeniorityDate set up as Date field in the table and that is where I am
bringing into the report from. I didn't know if I could go into the record
souce from the actual report and type descending or something of that sort.
I also have it set up as a short date.
:
On Fri, 18 Apr 2008 10:25:00 -0700, Melinda wrote:
I have looked through all the postings, but don't see one that quite fits
what I am doing, and have failed at all the attempts I have made. I am
sorting Seniority Dates in a total overtime report. When 3 employees have
the same year to date total hours of overtime, I need it to then sort by
seniority, with the most senior person being called in to work first. I
have three employees with 16.4 hours of overtime, and their seniority dates
are 05-13-2000, 06-14-1990, and 06-28-1986. I see that it is sorting on the
month and then the date, but I need the most senior person being listed
first. How do I change my sort?
What is the actual datatype of the Seniority Date field?
Is it Text datatype or is it Date datatype.?
Where are you doing the sorting? In the query or in the Report?
1) The Seniority Date field should be Date datatype (I suspect yours
is Text).
2) Any sorting you do in the query is irrelevant to the sorting in the
report.
To sort the report, use the Report's Sorting and Grouping dialog...
Click on View + Sorting and Grouping.
If the [SeniorityDate] is actually a Date datatype .....
In the Field/Expression column write:
[SeniorityDate]
on the top line.
In the Sort column write
Ascending
If the [SeniorityDate] is actually a Text datatype .....
In the Field/Expression column write:
=CDate([SeniorityDate])
on the top line
In the Sort Column, write
Ascending
The report should now sort according to Seniority.
