How to connect two unrelated and unconnected tables

[captgnvr]

D/CGM3

Steps understood and will get down to doing it by making a short table with three to four names and three to four items. For table1 crew names I will have staff code as primary key but for the table2 slop chest items there are no primary keys other than the items itself like marlboro cigs or dunhill etc, quantity, rate etc.. Will try what u guided and will get back. Thanks for the help

[captgnvr]

D/RG & CHM3

I went through the 'northwind' example and analyzed the query 'employees sales by country' in which 'employees id' is the field linked in the query to tables 'employees' and 'orders'. But when I go into the tables 'orders' i do not see 'employees id' but I see it in design mode and also checked to see if it is hidden and do not see there also. Can you pls explain why I am not seeing the 'employees id' in 'orders table' database mode.

brgds/captgnvr

[RuralGuy]

One of several reasons NOT to use Lookup Fields.

[captgnvr]

D/RG

Whole day gone trying to figure out how to make the third table, using table1 and table2. I created names table1 with three names, staff code and pasport and table2 with four items, price, quantity. Then failed to figure out how to create third table by using table1 names and table2 items and make a list of items purchased by crew.

I read the link u gave me about evils of look up n thnks.

[RuralGuy]

I don't have ac97 or I'd ask you to post your db and we could help further.

[captgnvr]

D/RG

I am attaching a small trial db but u know what I have been wanting to. The actual db with crew names and details is big and the slop chest items are big. I just made a small sample for my need.

So pls help, how to make a third table or a query or a report or a form where you can enter against each names items purchased and the amount to be deducted. I will keep awake if u can send some response.

[RuralGuy]

I have to leave for the rest of the day but see if this helps at all.

[captgnvr]

D/RG
Thnks for the worked out mdb. Got held up with hectic cargo activities and could not inform earlier. But still need to know how u made the third table. Bcos if I have to enter again all the info or start putting person code and product code in table2, it will take enormous time. Also I found u made three queries out of which two are same as the table1 and table2.
So pls when u find time --some more guidance pls with regard to making of third table combining table1 n table2.
brgds/captgnvr

[RuralGuy]

I run forms from queries rather that directly from tables. They are far more powerful and flexable. The third table I just created manually.

[CGM3]

Think of the third table as the ledger recording who bought how much of what, and when. Unfortunately, the database simply has no way of knowing that -- which person purchased how many of what item on a given date -- unless the data are somehow entered into that third table.

If the existing records of purchases are in an electronic document, like a text file or an Excel spreadsheet, it might be possible to format the information so you can import it into table3. You'd still have to manually update any future purchases, though.

[captgnvr]

D/CGM3 & RG

It is really nice that last two days continuous help for this. Now I feel that it will be lot easier to couple excel and access. So I will use the names from access and link it to excel sheet in colA and the other columns i wl use for items where by i can go there and enter the quantities purchased and to multiply from the price list and have it ready and use this data in a report in access or excel another sheet collating the data.
It gives me nice feeling and I dont feel bad to keep asking till I understand thanks to you both.

[CGM3]

Meant to do this yesterday; another example, much simpler than RuralGuy's, just three tables and a query that shows each purchase (person, item, date, cost, quantity, and calculated total amount found by multiplying cost by quantity), sorted by name and date.

[captgnvr]

D/CGM3

When almost decided to use excel, your suggestion yet again made me try some more.

All fine but why I feel it is difficult to make the table 3 is to remember the product code and person code. So started to find out how to use combo box with names and so when names are clicked it will take the respective person code. Alternatively, what i need is to have all names displayed like a database or excell sheet so that i can enter the quantity against the items and get total at the end. (Names in rows and slop-itemsin columns.)