Virtual List

[Awes]

Hi All

I am working on a database where one of the operations to be recorded is Work Done.

This falls into two categories:

1. Predefined tasks
2. Ad Hoc Tasks

Both task types have the same characteristics e.g. Id Value, Description of task, Category and Type.

The Predefined tasks (which is a fixed list of over 200 tasks) are stored in one table and the Ad Hoc
Tasks (which is a list that can be added to, via a form, by the User) are to be stored in another table.

The requirement is that when the User comes to select a task, from a form, they must be able to select
from one combo box list a task which could be from either list.

I need to store these two lists separately but be able to produce a virtual list of all tasks from
which the User can select a task.

I'm not sure of the best way to approach this so I'm throwing over to you guys to see what you suggest.

The alternative of course is to convince the User that it should all be stored in one table.

I look forward to your thoughts.

Cheers

AWES.

 

[Len Boorman]

Try using a UNION query as the row source for the combo box. You will then need to figure out how you are going to handle the combo box selection to get the record you actually want from the right table.

Len B

 

[Pat Hartman]

The BEST way to solve the problem is to store the two lists in the same table with a field that indicates the task type. It is trivial to select the set of data needed for each purpose. Whose decision was it to make separate tables and where did they go to data base design school?

The union query is the easy part. With separate tables how are you going to tell which table to join to in your other queries? How are you going to store the foreign key? Will you use separate fields or a flag that tells which table the key came from?

If the two task types are truely separate (thereby justifying a separate table), then everything should be separate including having two combos and separate many-side tables for storing the tasks as they relate to something else.