Finding records in Access Database

  • Thread starter Thread starter Jhoon
  • Start date Start date
J

Jhoon

I'm using Access Database to keep track of Customers and
Items purchased. I have 3 tables, which one is a many-
many relationship table.

ClientTable Client-ItemTabe ItemTable
ClientID(primary) ClientID(primary) ItemID(primary)
ItemID(primary)

ClientTable Client-ItemTable ItemTable
1000 1000~~~~1 1
1001 1000~~~~2 2
1002 1000~~~~3 3
1003 1001~~~~1 4
1004 1002~~~~1
1005 1002~~~~2

Few things i want to find.
1. Find list of client who has ONLY bought Item1, in this
case, it would be 1001.
2. Find list of client who has bough both Item 1 and 2,
this case, its is 1002.
3. Find list of clients who has bough Items 1, 2, and 3,
such as 1000.

Would be nice to have an SQL statemet that would satisfy
those 3 question... Much thanks guys =)
 
First, let me say that it is best to use the query
builder. But since you asked for the SQL, please see
below:
ClientTable Client-ItemTable ItemTable
ClientID(primary) ClientID(primary) ItemID(primary)
ItemID(primary)
1. Find list of client who has ONLY bought Item1, in this
case, it would be 1001.
Click to expand...

SELECT ClientTable.ClientID, Client-ItemTable.ItemID
FROM ClientTable, Client-ItemTable
WHERE ClientTable.ClientID = Client-ItemTable.ClientID
AND Client-ItemTable.ItemID = 1001
2. Find list of client who has bough both Item 1 and 2,
this case, its is 1002.
Click to expand...

SELECT ClientTable.ClientID, Client-ItemTable.ItemID
FROM ClientTable, Client-ItemTable
WHERE ClientTable.ClientID = Client-ItemTable.ClientID
AND Client-ItemTable.ItemID In(1001,1002)

[for this one, you could also use: ... AND (Client-
ItemTable.ItemID = 1001 OR Client-ItemTable.ItemID =
1002)]
3. Find list of clients who has bough Items 1, 2, and 3,
such as 1000.
Click to expand...
SELECT ClientTable.ClientID, Client-ItemTable.ItemID
FROM ClientTable, Client-ItemTable
WHERE ClientTable.ClientID = Client-ItemTable.ClientID
AND Client-ItemTable.ItemID In(1001,1002,1003)

[for this one, you could also use: ... AND (Client-
ItemTable.ItemID = 1001 OR Client-ItemTable.ItemID = 1002
OR Client-ItemTable.ItemID = 1003)]
 
If it can be done using query builder, please that maybe a
easier methods. Please show me to the right direction....


-----Original Message-----
First, let me say that it is best to use the query
builder. But since you asked for the SQL, please see
below:
ClientTable Client-ItemTable ItemTable
ClientID(primary) ClientID(primary) ItemID (primary)
ItemID(primary)
1. Find list of client who has ONLY bought Item1, in this
case, it would be 1001.
Click to expand...

SELECT ClientTable.ClientID, Client-ItemTable.ItemID
FROM ClientTable, Client-ItemTable
WHERE ClientTable.ClientID = Client-ItemTable.ClientID
AND Client-ItemTable.ItemID = 1001
2. Find list of client who has bough both Item 1 and 2,
this case, its is 1002.
Click to expand...

SELECT ClientTable.ClientID, Client-ItemTable.ItemID
FROM ClientTable, Client-ItemTable
WHERE ClientTable.ClientID = Client-ItemTable.ClientID
AND Client-ItemTable.ItemID In(1001,1002)

[for this one, you could also use: ... AND (Client-
ItemTable.ItemID = 1001 OR Client-ItemTable.ItemID =
1002)]
3. Find list of clients who has bough Items 1, 2, and 3,
such as 1000.
Click to expand...
SELECT ClientTable.ClientID, Client-ItemTable.ItemID
FROM ClientTable, Client-ItemTable
WHERE ClientTable.ClientID = Client-ItemTable.ClientID
AND Client-ItemTable.ItemID In(1001,1002,1003)

[for this one, you could also use: ... AND (Client-
ItemTable.ItemID = 1001 OR Client-ItemTable.ItemID = 1002
OR Client-ItemTable.ItemID = 1003)]
.
Click to expand...
 
Try using an exists clause in your query. The following UNTESTED SQL may get
you started.

SELECT C.*
FROM ClientTable as C INNER JOIN ClientItemTable as CI
ON C.ClientId = CI.ClientID
WHERE CI.ItemId In (1) AND
EXISTS (SELECT CIT.*
FROM ClientItemTable as CIT
WHERE CIT.ClientId = C.ClientID AND
CIT.ItemID NOT In (1)) = False

SELECT C.*
FROM ClientTable as C INNER JOIN ClientItemTable as CI
ON C.ClientId = CI.ClientID
WHERE CI.ItemId In (1,2) AND
EXISTS (SELECT CIT.*
FROM ClientItemTable as CIT
WHERE CIT.ClientId = C.ClientID AND
CIT.ItemID NOT In (1,2,3)) = False

SELECT C.*
FROM ClientTable as C INNER JOIN ClientItemTable as CI
ON C.ClientId = CI.ClientID
WHERE CI.ItemId In (1,2) AND
EXISTS (SELECT CIT.*
FROM ClientItemTable as CIT
WHERE CIT.ClientId = C.ClientID AND
CIT.ItemID NOT In (1,2,3)) = False
 
Back
Top