Query question. How do I write a query to do this?

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Tbl_customers
aut_id Client
1 Dan
2 Steve
3 Mike


tbl_shirts
Shirt_Info int_clientID
Red 3
Blue 3
Red 1
Green 3


tbl_pants
pant_info int_clientID
Jeans 3
Short 2
Long 3
Short 1
Jeans 2

tbl_socks
socks_info int_clientID
white 3
dress 3
dress 2
white 1
Stripes 1

Is there a way to build a query in access that gives me this?
txt_Name int_numberOfItems
Mike 7
Steve 3
Dan 4


Thanks in advance
Mark
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First thing, normalize your data: put all the clients' items into one
table instead of having a separate table for each item type.

CREATE TABLE ClientItems (
ClientID INTEGER NOT NULL REFERENCES Clients ,
ItemID INTEGER NOT NULL REFERENCES Items ,
CONSTRAINT PK_ClientItems PRIMARY KEY (ClientID, ItemID)
)

This table will only have ONE item per client (customer). The
REFERENCES are to other tables. IOW, there must be a ClientID & an
ItemID in the other tables before they can be added to table
ClientItems.

Then a query like this will work:

SELECT C.PersonName, Count(I.*)
FROM Clients As C INNER JOIN (ClientItems AS I INNER JOIN Items ON
I.ItemID = Items.ItemID) ON C.ClientID = I.ClientID
GROUP BY C.PersonName

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRqJjIechKqOuFEgEQJdNgCgsyOTPwimSHOLv4zdZjig4Jv+9RIAoL/r
Y3xtwOhYvsXzBHgvDjFGpE7F
=GFij
-----END PGP SIGNATURE-----
 
First create a union query to combine the records from the separate tables
(save this query and name it qryUnion):

SELECT tbl_shirts.int_clientID
FROM tbl_shirts
UNION ALL
SELECT tbl_pants.int_clientID
FROM tbl_pants
UNION ALL
SELECT tbl_socks.int_clientID
FROM tbl_socks;


Then create this query to return the results you seek:

SELECT Tbl_customers.aut_id, Tbl_customers.Client,
Count(qryUnion.int_clientID)
FROM Tbl_customers LEFT JOIN
qryUnion ON Tbl_customers.aut_id =
qryUnion.int_clientID
GROUP BY Tbl_customers.aut_id,
Tbl_customers.Client;
 
Back
Top