Combining multiple records per inventory number

[CF]

Hey Everyone,
Here is my situation:

I have an Access 2007 database and here are my tables:

Scope (INVNUM,QTY,LOCATION, etc)
Messages (INVNUM, DICTIONARY_ID, Position)
Dictionary (DICTIONARY_ID, ENGLISH, FRENCH)

For each inventory number on the scope table there will be a number of
Message. (About 20). I have created the messages table so it will look up the
number in Dictionary and then I can produce a report with the English and
French Mesage for each INVNUM.

I'm having trouble doing two things:

1) I can write a query to combine the message, dictionary and scope tables
but I am getting multiple line for each INV Number. I want to see the INV
Number once and then all the message listed by position (1-20)?

2) I need to figure out the best way to enter/ enter each INV Number and its
message? I was thinking if I could build the query above then I could turn it
into a form and data enter that way. I'm not sure is this the best way? I've
tried to create a form to date but I get the same issue as a above, a single
inventory numbers get upto 20 pages (1 per message) and I can't get it to be
on just one page.

Any help you guys could provide would be amazing.

Thanks

 

[Jeanette Cunningham]

Answered on Utter Access.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia