Tables with records that have different fields

[AlexMidd]

I am writing an expenses databse which has a table for expense claims with a 1 to many relationship with expense items so that they can be bunched with the appropriate claim. The problem is I want to have different numbers and types of fields for each different claim type, eg mileage and rate for car travel but client's name(s) for entertainment.

Can this be done in MS Access (97)? I have toyed with having another table with definitions of the fields for each claim type but I am not sure how to go about applying this to the claim items table where the actual values are needed.

Grateful for any help.

 

[MichaelRed]

ONe approach would be to set the table up with cols for expense type, expense item and value.

So PART of the table would look like:
ExpenseType[tab]ExpenseItem][tab]ExpenseValue
Transportation[tab]Auto Rental[tab]285.49
Entertainment[tab]Mr. Big[tab]389.90
Transportation[tab]Air Fare[tab]689.37
Transportation[tab]Cab[tab]24.67

The first col would need to be limited to a specific list of categories. The second may also need to be restricted to a list - but this might need to be a list which the User can add to (for specific / proper names).



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Aivars]

Hi!

You can create a new field in the table(s) maybe "ClaimType".
At this rate you can appoint different claim type to different claim groups. In addition it's recommended that you should be created related table for claims (codificator).
Approximative structure:
ClaimID
ClaimType;
ClaimName

Then will be probable to select type of claims with type name (combo box, list box, image list etc.)

Example from one of my databases:
Table XUNT - units data;
KUNT - codificator.

SELECT XUNT.UnitNo, XUNT.UnitID, XUNT.RevisionDate, XUNT.USERID, KUNT.UnitName, FROM XUNT INNER JOIN KUNT ON XUNT.UnitType = KUNT.UnitType WHERE KUNT.UnitType="AA";

Aivars

 

[AlexMidd]

Thanks for the replies. I am not sure if I have explained it properly. Basically each employee will have a list of claims and within each claim several items. Each item will hbelong to a spcific category for which there are different criteria to be recorded, e.g.:-

Item#   Type    Miles  Rate  Client  Description  Cost
1       Mileage    10  0.35                       £3.50
2       Travel                       Trip to USA  £2500.00
3       Accomm                       Hotel        £150.00
4       Mileage    20  0.35                       £7.00
5       Entrtnmt             J Doe                £200.00

i.e. not all the fields are used by all categories. It could be that my whole approach is wrong. I am setting up different tables for each category and linking them via PK to an FK in the main table. The problem then is how to display these on forms and reports. Maybe I am asking too much of the system but I am sur eit must have been done before.

No worries if I am being too hopeful.

Cheers,
Alex Middleton