This is done is Access Database. I wonder if there is a simply query statment for what i want to do? I have a list of client ID and list of items they have bought. I would like to write a query to ask few questions.
ClientTable ItemTable
1000 1
1001 2
1002 3
1004 4
1005
1006
…
…
I have 2 tables, ClientTable and ItemTable. Those numbers are primary key for each corrsponding table. I create a many-to-many table called Client-ItemTable to keep which client has bough which item. In Client-ItemTable, both ClientID and ItemID are primary keys.
Example of Client-ItemTable:
ClientID ItemID
1000 1
1000 2
1000 3
1001 1
1002 1
1002 2
1. I would like to write a query to tell me which clients have bough ONLY item 1, in this case, it would be Client 1001.
2. I would like to write a query to tell me which clients have bought BOTH items 1 and 2 ONLY, in this case, it would be Client 1002.
3. I would like to write a query to tell me which clients have bought Items 1,2,and3, in this case, Client 1000
Overall desgin:
ClientTable Client-ItemTable ItemTable
ClientID ClientID ItemID
ItemID
Many thanks!!!