How to connect two unrelated and unconnected tables

[captgnvr]

Dear All

table1 has list of names, date of birth and passport number.

table2 has list of items like beer, whisky and soft drink and price



The scenario is that the names in table1 will purchase few items in table2.

What I need to have is a report or a new table to show who has purchased what and how much to deduct from his salary.

For example: one person by name Charlie from table1 has purchased 3 whisky and 4 soft drinks. How to use enter this consumption against the names in table1. I do not want to add fields like beer, whisky and soft drink in table1.

Pls advice me best way to use these two tables and to make a report of items purchased and amount.

brgds/captgnvr

[RuralGuy]

You need a 3rd table with the keys from the other two.

[captgnvr]

D/Ruralguy
Thank you for the tip. But please give me some more guidance how to make the third table with the keys from the other two tables as nothing is common between the two tables. Table1 has names of people and table2 has list of consumables. By the by, I was counting on you to respond and really thanks for it. Will monitor whole day for the response so that I can make headway with this.

[RuralGuy]

Your 3rd table would have PK from Person table and PK from the Items table along with a price and a date if you like. From that table you can determine what items were used by what people.

[captgnvr]

D/RG
Good day. Kept on looking for ur tip. Have not ustood fully. if u can give me a link where an example I can read and do, will be of great assistance. Pls help me so that I can enter all the data and get going by this evening. What time zone are u in, so that I can follow immy and get it solved before u go to sleep.
brgds/captgnvr

[captgnvr]

D/RG
For this requirement I was going thro solutions.mdb and picked a form titled 'sales totals'. When I was going thro the directions, got into more doubts bcos, in tables 'orders' there is a field by name 'employees id' in design mode. But unable to see in database mode and i checked if the colum is hidden but it is not. Hows this done?? though it is in design but cant be seen in database window and it is not hidden.

Also pls explain what u mean by make 'RK' from first table and make 'RK' from second table?? Is it you just took a field name as 'RK'?? Pls clarify n wl keep watching for ur response.

[RuralGuy]

I'm at GMT -6 for time. You need a Form bound to the Person table with a SubForm bound to the 3rd table and the PersonPK as the LinkField. Use a ComboBox to look up items in your SubForm.

[RuralGuy]

PK = PrimaryKey value.

[RuralGuy]

There are examples of how to do this in the Northwind example that ships with Access. What is the solutions.mdb you mentioned? Where is it located?

[captgnvr]

D/RG
GETTING ROUGH IDEA but not clear to start with. Can you give me any links to see an example, then may be I will get to see what is being done. Must confess novice to Access and keen to know the ways to get over it. So pls give some examples or links.

[captgnvr]

D/RG
THE SOLUTIONS.mdb is also there along with northwind. This solutions.mdb is with lots of examples which has detailed help file also. But even there I got stumbled upon bcos i dont see the employees id tho i can see it in design mode. btw i am using office 97 as my company sticking to it.

[dcrake]

Sorry to intervene but like CAPITALS text talk can be very frustrating, please refrain from using shorthand. This is one of the reasons why I have not contributed to this post.

David

[RuralGuy]

Look at the Categories form in Northwind for the example.

[captgnvr]

D/RG
Thnks and i wl look into the northwind - categories example and get back.

And for mr Dcrake, it was not meant to frustrate anyone. I am used to caps for quick typing but i change immy on seeing. Hvr (however), noted ur sugestions and will do.

bestrgds/captgnvr

[CGM3]

See if this helps:

table1 has list of names, date of birth and passport number; I assume it also has a unique key (such as the person's social security number, or the concatenation of lastname-firstname-MI) to distinguish each record. Regardless of what it is, I'll call it PersonKey.

table2 has list of items like beer, whisky and soft drink and price; again, I assume each record has a unique key (such as the item's product number, or merely its name) to distinguish each record. Let's call this ItemKey.

table3 will therefore have the fields PersonKey, ItemKey, Quantity, and DatePurchased; the primary key for this table will be PersonKey, ItemKey, and DatePurchased.

Let's say Joe Smith buys 6 "units" (bottles/cans/cartons/whatever) of Duf beer on October 9, 2009. This would require a record to be created in table3 with the PersonKey for Joe Smith, the ItemKey of Duf beer, 10/9/2009 for DatePurchased, and 6 in the Quantity field.

If Joe buys another 4 "units" of Duf beer, and a pack of Chokem cigarettes, on October 13, 2009, two more records must be created in table3. Both will have Joe's PersonKey and 10/13/2009 in DatePurchased, but one will have the ItemKey for Duf beer and a Quantity of 4, while the other will have the ItemKey for Chokem cigarettes and a Quantity of 1.

At this point, we have three records in table3:

#1 - JoeSmith, DufBeer, 10/9/2009, 6
#2 - JoeSmith, Chokem, 10/13/2009, 1
#3 - JoeSmith, DufBeer, 10/13/2009, 4

Your queries will link table3 to table1 via PersonKey, and table3 to table2 via ItemKey.