Tables

[Joe Augustine]

I have a table with 7 project numbers (all unique) I have
another form with expense types, one project may have
several expense types while another project may only have
one or two, how do I make this work?

 

[Allen Browne]

You have a many-to-many relationship between projects and expense types,
i.e. one project can have many expense types, and one expense type can turn
up in many projects. You need a junction table between the two to resolve
the many-to-many into a pair of one-to-many.

Create a 3rd table to record the project expenses. It will have fields such
as these:
- ProjectID Number (Long) foreign key to Project.ProjectID
- ExpenseTypeID " foreign key to ExpenseType.ExpenseTypeID
- ExpenseDate Date/Time when this expense occurred.
- Amount Currency how much this expense was.