JOins pls help

  • Thread starter Thread starter subs
  • Start date Start date
S

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
 
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.
 
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];
 
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


vbasean said:
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


subs said:
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
Click to expand...
Click to expand...
 
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



vbasean said:
this simple crosstab might help (change 'table1' to your table name)
Click to expand...
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];
Click to expand...
"subs" wrote:
Click to expand...

- Show quoted text -
Click to expand...

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
 
Back
Top