Please help with sql statement

[WaveOut]

Here are my access tables:

PART TABLE
{
part_id
manufacturer
part_number
part_name
}

PLANT TABLE
{
plant_id
plant_name
plant_address
plant_phone
}

INVENTORY TABLE
{
inventory_id
part_id
plant_id
quantity
}

I want to run a query that will display all of the parts that exist at mutiple plants. If it only exist at 1 plant I do not want it in the query at all. I need the following information in the query:

manufacturer,part_number,part_name,plant_name,quantity

example:
GE | 1234 | tube | Plant Jones | 24
GE | 1234 | tube | Plant Kline | 31
GE | 1234 | tube | Plant Krist | 6
RCA| 3232 | pipe | Plant Knols | 92
RCA| 3232 | pipe | Plant Kline | 4

Any help with this will be greatly appreciated. This one really has me stumped!

Dave

 

[clapag22]

You could make a view:

create view v_parts_at_mult_plants as
select part_id, plant_id
from inventory
group by part_id, plant_id
having count(*) > 1
GO

then do:

select pr.manufacturer, pr.part_id, pr.part_name, pl.plant_name, i,quantity
from v_parts_at_mult_plan v, part pr, plant pl, inventory i
where v.part_id = pr.part_id
and v.plant_id = pl.plant_id
and v.part_id = i.part_id
and v.plant_id = i.plant_id
GO

I didn't test any of this out but it looks like it should work...