JOins pls help

[subs]

table A

carrier price year
op 3333 2009
ot 233 2009
op 1200 2008
op1 1400 2008


i need a query which can give me the following data
tables)-


Carrier price for 2009 price for 2008
op 3333 1200
ot 233 -
op1 - 1400


i need a join SQl Query. none of the Access Query joins work for this
scenario i.e left or right joins. i need to get all the records from
the original table in the output as shown above.No duplicates. pls
help
it is urgent

 

[Banana]

First, if this is truly urgent, I would encourage you to contact a local
consultant. This board is staffed by volunteers helping at their leisure
so there is no guarantee a reply will be delivered.

With that, I'd think you want a crosstab query. Google for abundant
articles discussing about how to create a crosstab query.

 

[vbasean]

this simple crosstab might help (change 'table1' to your table name)

TRANSFORM Avg(Table1.[Price]) AS AvgOfPrice
SELECT Table1.[Carrier], Avg(Table1.[Price]) AS [Total Of Price]
FROM Table1
GROUP BY Table1.[Carrier]
PIVOT Table1.[Year];

 

[vbasean]

TRANSFORM Avg(Table1.[Price]) AS AvgOfPrice
SELECT Table1.[Carrier]
FROM Table1
GROUP BY Table1.[Carrier]
PIVOT Table1.[Year];

that gets rid of the average per year column
--
~Your Friend Chris

this simple crosstab might help (change 'table1' to your table name)

TRANSFORM Avg(Table1.[Price]) AS AvgOfPrice
SELECT Table1.[Carrier], Avg(Table1.[Price]) AS [Total Of Price]
FROM Table1
GROUP BY Table1.[Carrier]
PIVOT Table1.[Year];
--
~Your Friend Chris

table A

carrier price year
op 3333 2009
ot 233 2009
op 1200 2008
op1 1400 2008


i need a query which can give me the following data
tables)-


Carrier price for 2009 price for 2008
op 3333 1200
ot 233 -
op1 - 1400


i need a join SQl Query. none of the Access Query joins work for this
scenario i.e left or right joins. i need to get all the records from
the original table in the output as shown above.No duplicates. pls
help
it is urgent

 

[subs]

TRANSFORM Avg(Table1.[Price]) AS AvgOfPrice
SELECT Table1.[Carrier]
FROM Table1
GROUP BY Table1.[Carrier]
PIVOT Table1.[Year];

that gets rid of the average per year column
--
~Your Friend Chris

this simple crosstab might help (change 'table1' to your table name)

TRANSFORM Avg(Table1.[Price]) AS AvgOfPrice
SELECT Table1.[Carrier], Avg(Table1.[Price]) AS [Total Of Price]
FROM Table1
GROUP BY Table1.[Carrier]
PIVOT Table1.[Year];

"subs" wrote:

- Show quoted text -

Thanks Chris

but what if i have another column like below

rrier price wgt year
op 3333 10 2009
ot 233 1 2009
op 1200 2 2008
op1 1400 4 2008


i need a query which can give me the following data
tables)-


Carrier price for 2009 price for 2008 wgt for 2009 wgt
for 2008
op 3333 1200
10 2
ot 233
- 1
op1 -
1400 4
i want results for both price and weight as shown above. how would
the query be different- can you pls tell me the SQL query