Sorting Dates

[Melinda]

I have a report in which I have several layers of sorting. My first sort is
by Class No, 2nd sort is total hours of overtime from lowest to highest, and
my 3rd sort is by seniority Date.

My problem is in the 3rd sort. This report is an overtime callout sheet
in which you are called to plow snow. I need it to sort by Class number,
within that class number the lowest balance of overtime hours and if two
employees have the same total in overtime hours I need it to put the first
employee with the most seniority at the top.

How would I go out about sorting the Seniority date properly?

 

[KARL DEWEY]

How would I go out about sorting the Seniority date properly?
I assume Seniority date is the hire date.
You must use the report Grouping and Sorting and select the three fields in
the order you stated. All would be in ascending order.

 

[Melinda]

This is exactly how I have it sorting, but the 3rd sort which is the
Seniority Date/Hire Date seems to be where the trouble is. I have it
sorting on a "Short Date" from oldest to newest. When there are 3 employees
with the exact same total of overtime hours, the seniority dates are sorting
as 10/06/2003, 11/08/1999, and 01/17/1995. Should be the exact opposite.

 

[Duane Hookom]

When you state __sorting on a "Short Date"__ it raises a red flag. Can you
explain this?

Sort on the date field value, not on any formatting. Formats should be
applied only in the control in the report that displays the value. Send pure
data values to your report.

 

[KARL DEWEY]

Try reversing it.

 

[Melinda]

Back in the employee table I have the data type as a Date/Time field.
Possibly is that where my problem is? and then I have it formated in the form
as a "Short Date"
in the form and report.

 

[Melinda]

Try reversing the entire sort?

Try reversing it.

 

[Duane Hookom]

If you look at the Record Source of the report in datasheet view, does your
Seniority Date Hire field appear left or right aligned? If it is
left-aligned, it is text and will not sort properly in the report.

 

[Melinda]

I had it centered.

If you look at the Record Source of the report in datasheet view, does your
Seniority Date Hire field appear left or right aligned? If it is
left-aligned, it is text and will not sort properly in the report.

 

[Duane Hookom]

You centered the values in a datasheet of a report? You are making Access way
too much work ;-)

What is the SQL view of the record source of the report?

 

[Melinda]

SELECT odotqry.Outpost, odotqry.ClassNo, tblEmployees.SeniorityDate,
odotqry.[Sum Of HoursWorked], odotqry.SumOfHoursWorked, odotqry.[Total
Hours], odotqry.EmployeeName FROM odotqry INNER JOIN tblEmployees ON
odotqry.Tmsemployeeno=tblEmployees.Tmsemployeeno WHERE
(((odotqry.ClassNo)<"88888"));

 

[Duane Hookom]

I make sure the sum of hoursworked is what you expect. It looks like you have
three fields that might contain the sum and they might be partial hours which
could account for the issue.

If this doesn't sort it out, I would re-create the report in a very
simplified version with only the sorting and grouping set and those fields
displayed in header and the detail section. Make sure you can view all of the
decimals for all numeric fields.

--
Duane Hookom
Microsoft Access MVP

SELECT odotqry.Outpost, odotqry.ClassNo, tblEmployees.SeniorityDate,
odotqry.[Sum Of HoursWorked], odotqry.SumOfHoursWorked, odotqry.[Total
Hours], odotqry.EmployeeName FROM odotqry INNER JOIN tblEmployees ON
odotqry.Tmsemployeeno=tblEmployees.Tmsemployeeno WHERE
(((odotqry.ClassNo)<"88888"));

You centered the values in a datasheet of a report? You are making Access way
too much work ;-)

What is the SQL view of the record source of the report?

 

[Melinda]

My overtime hours are indeed correct, I have a total for overtime hours
worked, overtime hours refused and those two combined for a year to date
overtime total. I am trying to re-do the report as we speak. The problem
lies in the date. Should this field be a text field as opposed to a
date/time field back in the table? This is the very first database I did and
I think it all goes back to normalizing tables. Thanks for all your help.

I make sure the sum of hoursworked is what you expect. It looks like you have
three fields that might contain the sum and they might be partial hours which
could account for the issue.

If this doesn't sort it out, I would re-create the report in a very
simplified version with only the sorting and grouping set and those fields
displayed in header and the detail section. Make sure you can view all of the
decimals for all numeric fields.

--
Duane Hookom
Microsoft Access MVP

SELECT odotqry.Outpost, odotqry.ClassNo, tblEmployees.SeniorityDate,
odotqry.[Sum Of HoursWorked], odotqry.SumOfHoursWorked, odotqry.[Total
Hours], odotqry.EmployeeName FROM odotqry INNER JOIN tblEmployees ON
odotqry.Tmsemployeeno=tblEmployees.Tmsemployeeno WHERE
(((odotqry.ClassNo)<"88888"));

You centered the values in a datasheet of a report? You are making Access way
too much work ;-)

What is the SQL view of the record source of the report?

--
Duane Hookom
Microsoft Access MVP


:

I had it centered.

:

If you look at the Record Source of the report in datasheet view, does your
Seniority Date Hire field appear left or right aligned? If it is
left-aligned, it is text and will not sort properly in the report.

--
Duane Hookom
Microsoft Access MVP


:

Back in the employee table I have the data type as a Date/Time field.
Possibly is that where my problem is? and then I have it formated in the form
as a "Short Date"
in the form and report.

:

When you state __sorting on a "Short Date"__ it raises a red flag. Can you
explain this?

Sort on the date field value, not on any formatting. Formats should be
applied only in the control in the report that displays the value. Send pure
data values to your report.

--
Duane Hookom
Microsoft Access MVP


:

This is exactly how I have it sorting, but the 3rd sort which is the
Seniority Date/Hire Date seems to be where the trouble is. I have it
sorting on a "Short Date" from oldest to newest. When there are 3 employees
with the exact same total of overtime hours, the seniority dates are sorting
as 10/06/2003, 11/08/1999, and 01/17/1995. Should be the exact opposite.
:

How would I go out about sorting the Seniority date properly?
I assume Seniority date is the hire date.
You must use the report Grouping and Sorting and select the three fields in
the order you stated. All would be in ascending order.
--
KARL DEWEY
Build a little - Test a little


:

I have a report in which I have several layers of sorting. My first sort is
by Class No, 2nd sort is total hours of overtime from lowest to highest, and
my 3rd sort is by seniority Date.

My problem is in the 3rd sort. This report is an overtime callout sheet
in which you are called to plow snow. I need it to sort by Class number,
within that class number the lowest balance of overtime hours and if two
employees have the same total in overtime hours I need it to put the first
employee with the most seniority at the top.

How would I go out about sorting the Seniority date properly?

 

[Duane Hookom]

If you want to sort on the date field, it should be a date data type. The
issue with date sorting is generally caused by developers who change their
date field formats in queries like:

SELECT Format([HireDate], "mm/dd/yyyy") as TheHireDate,....

The above expression might "look" good but it is basically worthless for
sorting or using in calculations. The above would appear in the datasheet
view left aligned since it creates text from a date.

--
Duane Hookom
Microsoft Access MVP

My overtime hours are indeed correct, I have a total for overtime hours
worked, overtime hours refused and those two combined for a year to date
overtime total. I am trying to re-do the report as we speak. The problem
lies in the date. Should this field be a text field as opposed to a
date/time field back in the table? This is the very first database I did and
I think it all goes back to normalizing tables. Thanks for all your help.

I make sure the sum of hoursworked is what you expect. It looks like you have
three fields that might contain the sum and they might be partial hours which
could account for the issue.

If this doesn't sort it out, I would re-create the report in a very
simplified version with only the sorting and grouping set and those fields
displayed in header and the detail section. Make sure you can view all of the
decimals for all numeric fields.

--
Duane Hookom
Microsoft Access MVP

SELECT odotqry.Outpost, odotqry.ClassNo, tblEmployees.SeniorityDate,
odotqry.[Sum Of HoursWorked], odotqry.SumOfHoursWorked, odotqry.[Total
Hours], odotqry.EmployeeName FROM odotqry INNER JOIN tblEmployees ON
odotqry.Tmsemployeeno=tblEmployees.Tmsemployeeno WHERE
(((odotqry.ClassNo)<"88888"));

:

You centered the values in a datasheet of a report? You are making Access way
too much work ;-)

What is the SQL view of the record source of the report?

--
Duane Hookom
Microsoft Access MVP


:

I had it centered.

:

If you look at the Record Source of the report in datasheet view, does your
Seniority Date Hire field appear left or right aligned? If it is
left-aligned, it is text and will not sort properly in the report.

--
Duane Hookom
Microsoft Access MVP


:

Back in the employee table I have the data type as a Date/Time field.
Possibly is that where my problem is? and then I have it formated in the form
as a "Short Date"
in the form and report.

:

When you state __sorting on a "Short Date"__ it raises a red flag. Can you
explain this?

Sort on the date field value, not on any formatting. Formats should be
applied only in the control in the report that displays the value. Send pure
data values to your report.

--
Duane Hookom
Microsoft Access MVP


:

This is exactly how I have it sorting, but the 3rd sort which is the
Seniority Date/Hire Date seems to be where the trouble is. I have it
sorting on a "Short Date" from oldest to newest. When there are 3 employees
with the exact same total of overtime hours, the seniority dates are sorting
as 10/06/2003, 11/08/1999, and 01/17/1995. Should be the exact opposite.
:

How would I go out about sorting the Seniority date properly?
I assume Seniority date is the hire date.
You must use the report Grouping and Sorting and select the three fields in
the order you stated. All would be in ascending order.
--
KARL DEWEY
Build a little - Test a little


:

I have a report in which I have several layers of sorting. My first sort is
by Class No, 2nd sort is total hours of overtime from lowest to highest, and
my 3rd sort is by seniority Date.

My problem is in the 3rd sort. This report is an overtime callout sheet
in which you are called to plow snow. I need it to sort by Class number,
within that class number the lowest balance of overtime hours and if two
employees have the same total in overtime hours I need it to put the first
employee with the most seniority at the top.

How would I go out about sorting the Seniority date properly?

 

[Melinda]

I am going to go back and look at my queries and look at the total overtime
hours, I will let you know how I come along. Thanks again.

If you want to sort on the date field, it should be a date data type. The
issue with date sorting is generally caused by developers who change their
date field formats in queries like:

SELECT Format([HireDate], "mm/dd/yyyy") as TheHireDate,....

The above expression might "look" good but it is basically worthless for
sorting or using in calculations. The above would appear in the datasheet
view left aligned since it creates text from a date.

--
Duane Hookom
Microsoft Access MVP

My overtime hours are indeed correct, I have a total for overtime hours
worked, overtime hours refused and those two combined for a year to date
overtime total. I am trying to re-do the report as we speak. The problem
lies in the date. Should this field be a text field as opposed to a
date/time field back in the table? This is the very first database I did and
I think it all goes back to normalizing tables. Thanks for all your help.

I make sure the sum of hoursworked is what you expect. It looks like you have
three fields that might contain the sum and they might be partial hours which
could account for the issue.

If this doesn't sort it out, I would re-create the report in a very
simplified version with only the sorting and grouping set and those fields
displayed in header and the detail section. Make sure you can view all of the
decimals for all numeric fields.

--
Duane Hookom
Microsoft Access MVP


:

SELECT odotqry.Outpost, odotqry.ClassNo, tblEmployees.SeniorityDate,
odotqry.[Sum Of HoursWorked], odotqry.SumOfHoursWorked, odotqry.[Total
Hours], odotqry.EmployeeName FROM odotqry INNER JOIN tblEmployees ON
odotqry.Tmsemployeeno=tblEmployees.Tmsemployeeno WHERE
(((odotqry.ClassNo)<"88888"));

:

You centered the values in a datasheet of a report? You are making Access way
too much work ;-)

What is the SQL view of the record source of the report?

--
Duane Hookom
Microsoft Access MVP


:

I had it centered.

:

If you look at the Record Source of the report in datasheet view, does your
Seniority Date Hire field appear left or right aligned? If it is
left-aligned, it is text and will not sort properly in the report.

--
Duane Hookom
Microsoft Access MVP


:

Back in the employee table I have the data type as a Date/Time field.
Possibly is that where my problem is? and then I have it formated in the form
as a "Short Date"
in the form and report.

:

When you state __sorting on a "Short Date"__ it raises a red flag. Can you
explain this?

Sort on the date field value, not on any formatting. Formats should be
applied only in the control in the report that displays the value. Send pure
data values to your report.

--
Duane Hookom
Microsoft Access MVP


:

This is exactly how I have it sorting, but the 3rd sort which is the
Seniority Date/Hire Date seems to be where the trouble is. I have it
sorting on a "Short Date" from oldest to newest. When there are 3 employees
with the exact same total of overtime hours, the seniority dates are sorting
as 10/06/2003, 11/08/1999, and 01/17/1995. Should be the exact opposite.
:

How would I go out about sorting the Seniority date properly?
I assume Seniority date is the hire date.
You must use the report Grouping and Sorting and select the three fields in
the order you stated. All would be in ascending order.
--
KARL DEWEY
Build a little - Test a little


:

I have a report in which I have several layers of sorting. My first sort is
by Class No, 2nd sort is total hours of overtime from lowest to highest, and
my 3rd sort is by seniority Date.

My problem is in the 3rd sort. This report is an overtime callout sheet
in which you are called to plow snow. I need it to sort by Class number,
within that class number the lowest balance of overtime hours and if two
employees have the same total in overtime hours I need it to put the first
employee with the most seniority at the top.

How would I go out about sorting the Seniority date properly?