getting information in table 2 to fill fields in table 1 if a field has matching data

[turtlesrun]

hey guys,

i have just started using access again for the first time in 10 years and i am reteaching myself.

i have this problem i know it is possible but i am unsure on how to achieve it, i believe it would be done with a query.

i have two tables:


batch (table 1)



which contains the following

ID | Date | Prefix | First Number | Last Number | Location | Store / Scan|


Prefix (table 2)

which contains the following

ID | Prefix | Description | Location | Store / Scan |

basicly what i require is when a prefix is entered in table 1 and the prefix matches one in table 2 it collects information regarding Location and store / scan from table 2

as a resault of this when a new batch is created and a prefix assigned the Location and store scan fields are automatically populated with information from the second table.

any help regarding this issue would be greatly appreciated.

Thanks

Turtlesrun

[lfpm062010]

There are many way to achieve this.

A join query, Dlookup, vba, ... etc.

The only cosideration is are the relationship one to one match (best), or one to many match (little more tricky), or many to many match (re-design table) between the two table.

[turtlesrun]

anyone know of any good tutorials or can give an explanations that may help me in solving this i know once iv done it once ill be able to apply it to other areas of my database but i am currently struggling to do it.

[lfpm062010]

I don't know how good this site is. Hope it will help you to get you back to speed.

http://www.gcflearnfree.org/access2010