×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tables with records that have different fields

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.

RE: Tables with records that have different fields

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    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

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

 

RE: Tables with records that have different fields

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close