I am working on a sales database, which has two seperate sales tables (one for subscriptions and one for events), and I really need to keep these separate.

The problem is I need these to both be connected to a single invoice table. The invoice table has several fields which are filled by means of a lookup but it is not possible to have them looking up the same fields in two separate tables.

Is there a way around this.

Many thanks for reading

I am working on a sales database, which has two seperate sales tables (one for subscriptions and one for events), and I really need to keep these separate.

I think that you don't need separate sales tables... all you need is a new "type" field with two chooses.

I tend to agree with Daniele; however, an auto-enter calculation and the under-appreciated Lookup function might accomplish your stated goal.

I would prefer to keep the tables separate as different people in the office do different tasks and I just think it would be confusing to have everything in the same table. Also the subscriptions need to be regularly auto-updated.

If someone could give me a little more detail re the second suggestion ie the auto-enter calculation and lookup function that would be great.

Defining automatic data entry

The auto-enter calculation might look something like:

Lookup( subscriptions::item ; events::item )