QueryToJoineMultipleTables

[jb62307]

I have the following 3 tables


Parts1-------*PartStyle*----------1 Style
PartNo PartNo StyleNo
Price StyleNo Description

I am trying to write a SQL statement that would return a record with the
columns below:

PartNo Price Description.

For every record in the Style table there would be a coulm in the result
set. Also in the description column I would like to put a Y, if there is a
partstyle record for it.

Parts PartStyle Style
1 1.00 1 1 1 Style1
2 2.00 1 3 2 Style2
2 2 3 Style3

If I run a query to select all Parts, from the above data, the output should
be:

Part No Price Desc1 Decs2 Desc3
1 1.00 Y Y

2 2.00 Y

I know how to do this in code, but would like to do as much as possible with
SQL. I am using Access 2007.

Thanks

 

[John Spencer]

Sounds as if you would need a crosstab query to get the result you want. BUT,
you would also need a ranking query as the source for the crosstab query.

SELECT A.PartNo, A.StyleNo
, 1 + Count(B.PartNo) as Rank
FROM PartStyle as A LEFT JOIN PartStyle as B
ON A.PartNo = B.PartNo
AND A.StyleNo > B.StyleNo
GROUP BY A.PartNo, A.StyleNo

TRANSFORM First(Style.Description) as TheDescription
SELECT Parts.PartNo, Parts.Price
FROM (Parts INNER JOIN qRank
ON Parts.PartNo = Q.PartNo)
INNER JOIN Style
ON Q.StyleNo= Style.StyleNo
GROUP BY Parts.PartNo, Parts.Price
PIVOT qRank.Rank

Given your table and field names you should be able to do that all in one
query. The SQL for that would look like:

TRANSFORM First(Style.Description) as TheDescription
SELECT Parts.PartNo, Parts.Price
FROM (Parts INNER JOIN
(
SELECT A.PartNo, A.StyleNo
, 1 + Count(B.PartNo) as Rank
FROM PartStyle as A LEFT JOIN PartStyle as B
ON A.PartNo = B.PartNo
AND A.StyleNo > B.StyleNo
GROUP BY A.PartNo, A.StyleNo
) As qRank
ON Parts.PartNo = Q.PartNo)
INNER JOIN Style
ON Q.StyleNo= Style.StyleNo
GROUP BY Parts.PartNo, Parts.Price
PIVOT qRank.Rank


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[jb62307]

John:

I modified the SQL and it returns the records I need. I do need all the
Style descriptions from the Styles table, even if there is not a record in
the PartStyle table. When the users opens up the form to work with the data
from this view, I have to have a column for each style.description. from the
style table. What I do is check to see if there is a value in the
style.description column and set a checkbox on the form, for the
style.description column. Itried to change the joins to include everything
from the style table and matching redords from the Q but get an error.

If the Styles table had 3 descriptions then the view should return all 3


Styles
Desc1
Desc2
Desc3

PartNo Price Desc1 Desc2 Desc3

Thanks