Best Practice

Redo the Table err!!!

ClientTable~~~~Client-ItemTable~~~~~ItemTable
ClientID~~~~~~~ClientID~~~~~~~~~~~~~ItemID
~~~~~~~~~~~~~~~ITemID~~~~~~~~~~~~~~~~~~~~~

*/I'm going to generate some tables and data to populate them with.*/

CREATE TABLE ClientTable (
ClientID int PRIMARY KEY NOT NULL,
ClientName varchar (25)
)
GO

Insert INTO ClientTable (ClientID,ClientName)
VALUES ('1','Bob Smith')
Insert INTO ClientTable (ClientID,ClientName)
VALUES ('2','Susan Lumpkin')
Insert INTO ClientTable (ClientID,ClientName)
VALUES ('3','Jay Jones')
Insert INTO ClientTable (ClientID,ClientName)
VALUES ('4','Forrest Gump')
Insert INTO ClientTable (ClientID,ClientName)
VALUES ('5','Rush Limbaugh')
GO

CREATE TABLE dbo.ItemTable (
ItemID int PRIMARY KEY NOT NULL,
ItemDesc varchar (25)
)
GO

Insert INTO ItemTable (ItemID,ItemDesc)
VALUES ('1','Old Plate')
Insert INTO ItemTable (ItemID,ItemDesc)
VALUES ('2','New VCR')
Insert INTO ItemTable (ItemID,ItemDesc)
VALUES ('3','Used Car')
Insert INTO ItemTable (ItemID,ItemDesc)
VALUES ('4','New Auto')
Insert INTO ItemTable (ItemID,ItemDesc)
VALUES ('5','Old Stove')

GO

CREATE TABLE ClientItemTable (
ClientID int NOT NULL,
ItemID int NOT NULL
)
GO

Insert INTO ClientItemTable (ClientID,ItemID)
VALUES ('1','2')
Insert INTO ClientItemTable (ClientID,ItemID)
VALUES ('3','2')
Insert INTO ClientItemTable (ClientID,ItemID)
VALUES ('3','4')
Insert INTO ClientItemTable (ClientID,ItemID)
VALUES ('4','1')
Insert INTO ClientItemTable (ClientID,ItemID)
VALUES ('5','5')
Insert INTO ClientItemTable (ClientID,ItemID)
VALUES ('5','1')
Insert INTO ClientItemTable (ClientID,ItemID)
VALUES ('3','1')

SELECT ClientTable.ClientName as Client, ItemTable.ItemDesc

FROM ClientItemTable JOIN ClientTable
ON (ClientItemTable.ClientID = ClientTable.ClientID)
JOIN ItemTable
ON (ClientItemTable.ItemID = ItemTable.ItemID)

WHERE ClientItemTable.ItemID = '1' AND ClientItemTable.ItemID='2'
Group By ClientTable.ClientName, ItemTable.ItemDesc WITH Rollup

Hope this helps... I might be wayyy off though. :-)

Make sure you've set the relationships between the client-item table and your client and item tables, so Access knows which fields correspond.

Presuming there are no items with item number 0, all you want to do is query who hasn't got orders against their name with a number > 1. In design view, select all 3 tables (this is where your relationships need to be in place). Take the Client from the Client table (NOT the Client-Item table or you'll get multiple entries) and the Item from the Item table (same reason), then for criteria, enter NOT > 1 under the item. This rules out any clients who have ordered anything with a higher item number. If you've got clients on the system who haven't ordered, you'll need to drag a second item field onto the grid, and ON THE SAME ROW, enter 1 under criteria.

For the second query, change the > 1 to > 2, and the 1 to "1" OR "2". Note with two criteria plus the operator (OR) you'll need to put the inverted commas in or the query will look for entries that contain the whole string, and pull up nothing. Also, if Access drops the 2 onto the second row, make sure your other criteria (NOT >2) is copied down to that row as well.

Third query, you're not looking for them to only have bought certain items, so you can rule out the >2. Just have the item criteria to "1" and "2" and "3".