Personnel Budgeting Design Questions

[Megblue]

I have several questions as I convert a very wide excel database to access. Its is used for annual personnel budgting for wages and benefits.




I need to be able to budget by deptment by part time wages, full time wages and overtime wages. I can do a column with the options of PT, FT or OT but will that allow me to show all 3 caltegories of wages later on when I need to report for each department?


Contributions for retirment are a calculated percentage of wages. That I can do. However, some PT employees are eligible but others are not so I cant use that as a filter. I thought of using a check box (Retirement Eligible) or something like that but am not sure that is the best way to do that.


Last, I am unable to sort the table and save it that way. The "Last applied sort order" selection is Yes, but everytime I sort, then save and close, it is back to the original order when I open. ( employees were previously ordered by department, then by grade, I need it to be by department then last name so I dont have to keep sorting every single query and report I do.)


Thanks for the assistance!! It is appreciated very much.

[Robeen]

I don't believe MS Access Tables are designed to 'hold' your sorting.
If you want to see the records sorted a particular way each time you view the data, you should try creating a Query on the Table and sort the query the way you want.

[Megblue]

According to the microsoft 2007 access help page,"when you sort the data in a table, form, or report, you can save the sort order with the object."

I take that to mean that you can save a particular sort order in a table, but I could be wrong. Worst case, I cut and paste to a new table in the order I want.

[Robeen]

I just tried sorting one of my test tables, closing it and then opening it again.
It looks like the sorting I specified 'held'. Sorry.
I seldom do any sorting in my tables so I spoke without checking.

[Robeen]

Using a Yes/No . . . True/False value for specifying 'Retirement Eligible' [check box on Form] sounds like a good idea.

[Robeen]

I need to be able to budget by deptment by part time wages, full time wages and overtime wages. I can do a column with the options of PT, FT or OT but will that allow me to show all 3 caltegories of wages later on when I need to report for each department?

I think you should have one column in your table for storing what kind of WageType is being reported [PT, FT, OT].

When you design your report, you can create a query that retrieves all your data - and sorts it by Department and then by WageType.
Then - when you create your report, you can group it by Department and then by WageType.

If you want to show all three Wage Types for each Department on the same line of your report, you could:
1. Create a query for each Wage Type [using Department, WageType, Amount - and then filter for WageType].
2. Group the Query by Department and WageType fields and then Sum the Amount field.
3. Then create a 4th query that joins all three previous queries on Department & pulls in the WageType and Amount from each of the previous queries.

I've done this in some complicated reports, and while it does mean a lot of queries - it certainly works well.

I hope this helps! Let me know if you have questions