automatic lookup

[munday63]

hi i have two tables.

One with Item Data - ITem ID, Item Name and Item Value

On another table, i have transaction data - Transaction ID, Item 1, Item 1 Value, Item 2, Item 2 Value etc.

What i am looking for is a way of when i lookup item 1 and enter it, item 1 value will appear automatically.

Any ideas

 

[Mile-O]

You don't need the value in the second table. Delete it as it violates third normal form.

The thing is you have a repeating group in this table Item1, Item2, Item3 which means your database, at the moment, does not even meet First Normal Form.

If you go this way then your whole development process will prove problematic and more complicated than anything should ever be. It will also require more maintenance to the database on a continuous basis.

Look up normalisation. You probably have a many-to-many relationship here and are not simulating it properly.