SQL Help

[MG5020]

Hi,

I have a table that has the following fields:

Name
Rates
Tier

What I would like to do is display the table based on the name, rates, and tier type. For example I would like to display this:

Name Rates Tier
-----------------------
John Smith 100.00 Indiv
John Smith 210.00 2Party
John Smith 180.00 ParentChild
John Smith 320.00 Family

in a single line query like this

Name Tier1 Tier2 Tier3 Tier4
--------------------------------------
John Smith 100.00 210.00 180.00 320.00

I am sure this has been done before but I am not sure of the SQL code to do it. Can someone help me out?


Mark

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MG5020]

I need to do it in a SQL statement, not in VB code.

 

[dhookom]

Can you use a crosstab query or does this all need to be in a single expression?

Duane
Hook'D on Access
MS Access MVP

 

[MG5020]

I would like it as a single expression. The names will be different, i.e.

John Smith 100.00 210.00 180.00 320.00
Bob Jones 95.00 188.00 170.00 300.00
John Q Public 125.00 250.00 220.00 400.00


I have never used a crosstab query so I don't know if this is what a crosstab query looks like.

Mark

 

[dhookom]

A crosstab query would create separate columns for each value.

There is an update type query that would store the single values in a larger text of memo field. Consider the Northwind sample database with a new [Products] field added to the Categories table. If you want to store all of the products for each category in this field, you could use:

UPDATE Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID SET Categories.Products = [Products] & "-" & [ProductName];

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

FYI, the crosstab query would have a SQL view like:

TRANSFORM First(TT_MG5020.Rates) AS FirstOfRates
SELECT TT_MG5020.Name
FROM TT_MG5020
GROUP BY TT_MG5020.Name
PIVOT TT_MG5020.Tier;

This would create a view like:

Name         2Party     Family     Indiv     ParentChild
John Smith  $210.00    $320.00   $100.00         $180.00
Mark        $300.00              $110.00         $200.00

Also, IMO you should never name a field name. Name is a property of objects and you can create some major nightmares. Avoid reserved words and be more explicit with your naming.



Duane
Hook'D on Access
MS Access MVP