Employee Expenses table design

[RCurtin]

Hi,
I'm having problems getting my head around the best way to design a table for an employees expenses. This is my current M_Expenses table:

ExpenseID (PK)
PayrateID
ExpenseType
Amount

PayrateID is the foreign key to a table that holds information about their rates, evertime etc.

My idea was that the user would only choose an expense if the employee were entitled to that expense - so if someone was not entitled to flight expenses the record would not be created. However my boss says that if he were to look at the report produced from this he wouldn't know whether the person putting in the data just didn't have the information at the time or it didn't apply to that employee. So I will need to let the users know that they must create a record for each.

Also the report must list all of the expenses:
Per diem expense
Accommodation
Car hire
Flight
Other expenses
They need to be listed in this order and must appear whether that particular employee is entitled to them or not. I don't see how I can do this as it is because my subreport that lists the expenses is a continuous form and they are not listed in alphabetical order. Plus only expenses that the user has chosen will appear.

The only other way I can see to do it is redesign my table as follows:
ExpenseID
PerDiem
Accommodation
CarHire
Flights
OtherExpenseType
OtherExpenseAmount

But that doesn't seem like good design?

 

[RCurtin]

Think I've solved it - by using a crosstab query - that way I have all the expense headings as though they were separate fields. It will list all of the fields also because in the crosstab query I have it showing all the expenseTypes form the expense type lookup table and the matching records from the expenses table.