Can I create a sort order for a field/table? (1 Viewer)

[vangogh228]

I have been given a table with the fields Salary AutoNum, SSN, Month, Year, and Salary. The table tracks salaries by the month and is reported to my client in a spreadsheet. The Year is 4 digits (e.g. "2002") and and the Month is 3 characters (e.g. "Jan"). My client wants to keep this formatting.

The only trouble is that the table is the source for a subform in datasheet view for personal records with SSN as, of course, the linkng field. When a person's record is shown, the subform shows the salary history for that person. The records need to be sorted in chronological order, with Year being ascending and the Month being from "Jan", "Feb" ... ... ... to "Nov", "Dec".

I do not know how to sort this in the appropriate manner and could not find a previous thread dealing with this issue.

THANKS for any help!!

Tom

 

[glynch]

I have an idea but it calls for some table reworking.

Create a new lookup table with just the twelve months in it containing two fields, MonthID and Month (Text, Jan, Feb, etc.)Use the new table to update all of the month values in your current table to the MonthID, change it's data type to Long Integer. Whenver you need to display a month value, just use a query that links on the MonthID as a foreign key.

Then you can use the numeric MonthID field to sort your months in the right order. From a user's point of view all of the changes will be transparent.

HTH

 

[vangogh228]

Wow... I'm not sure I understand. I think I know WHAT you mean, since I have used tables for sources of lookup functions... but I don't know HOW to do it in this situation to get the desired result.

I'm not sure I understand the term 'foreign key.'

Thanks for the ideas so far.

Tom

 

[glynch]

Try something like this:

tblEmpSalary (this is your existing table), add MonthID field:

EmpSalaryID
MonthID (Long Integer)
SSN
Year
Month
Salary

tblMonth (new table):

MonthID (autonumber, Primary Key)
MonthText (Text, 3)

Check first that you have valid values in the Month field in tblEmpSalary, use this query:

SELECT DISTINCT tblEmpSalary.Month
FROM tblEmpSalary;

If you have any typos or other irregularities in that data fix them now.

To update the new MonthID field in tblEmpSalary:

UPDATE tblEmpSalary INNER JOIN tblMonth ON tblEmpSalary.Month = tblMonth.MonthText SET tblEmpSalary.MonthID = [tblMonth]![MonthID];

Then this query should be close to what you need to display your employee records in the right order, you will need to decide what fields will be displayed on your subform. Here it is:

SELECT tblEmpSalary.EmpSalaryID, tblEmpSalary.Year, tblMonth.MonthID, tblMonth.MonthText, tblEmpSalary.EmployeeName, tblEmpSalary.SSN, tblEmpSalary.Salary
FROM tblEmpSalary INNER JOIN tblMonth ON tblEmpSalary.MonthID = tblMonth.MonthID
ORDER BY tblEmpSalary.Year, tblMonth.MonthID;

HTH

 

[vangogh228]

glynch:

I was just ready to give up and reset the months back to numbers, but I tried what you posted here and, with little difficulty, got what I was looking for. I cannot tell you how much I appreciate your help... especially seeing how it is 1:02am here and I am meeting them at 10:00.

THANKS!!!

Tom