Tables with records that have different fields
Tables with records that have different fields
(OP)
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.
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.
Talk To Other Members
RE: Tables with records that have different fields
So PART of the table would look like:
ExpenseType ExpenseItem] ExpenseValue
Transportation Auto Rental 285.49
Entertainment Mr. Big 389.90
Transportation Air Fare 689.37
Transportation Cab 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
RE: Tables with records that have different fields
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
RE: Tables with records that have different fields
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