Join Expression not Supported

[amegahed3]

Hi All,



I use Access 2007, and I have the following SQL problem:

I have two tables TransformerTypePeriodBOMProducts and TransformerTypePeriodProducts. The first table has 4 fields: TransformerTypeID, OutputProductID, PeriodID and BOM, while the second has the following 4 fields: TransformerTypeID, OutputProductID,PeriodID and AssemblyCapacity. So as you can see, there are 3 fields (TransformerTypeID, OutputProductID and PeriodID) that are in both tables.

I need to delete all records in the first table that have the field AssemblyCapacity in the second table equal to 0 (and of course have similar TransformerTypeID, OutputProductID and PeriodID fields).

I tried many trials, the last I got was:

SELECT  TransformerTypeAssemblyPeriodBOMProducts.*
FROM ((TransformerTypeAssemblyPeriodBOMProducts  INNER JOIN TransformerTypeAssemblyPeriodProducts  ON TransformerTypeAssemblyPeriodBOMProducts.TransformerTypeID=TransformerTypeAssemblyPeriodProducts.TransformerTypeID) INNER JOIN TransformerTypeAssemblyPeriodProducts ON TransformerTypeAssemblyPeriodBOMProducts.OutputProductID=TransformerTypeAssemblyPeriodProducts.OutputProductID) INNER JOIN TransformerTypeAssemblyPeriodProducts ON TransformerTypeAssemblyPeriodBOMProducts .PeriodID=TransformerTypeAssemblyPeriodProducts.PeriodID
DELETE * FROM TransformerTypeAssemblyPeriodBOMProducts
WHERE TransformerTypeAssemblyPeriodProducts.AssemblyCapacity=0;

For which I get the error "join expression not supported".

So, my questions are:
1. How should the code be modified in order to run?
2.Would it do what I want to do (as explained above)?

Thanks,

Aly

[weekend00]

Do you mean you the two tables need to match on all the three fields and then all records in the first table that have the field AssemblyCapacity in the second table equal to 0?

I can't type so long names, I just give you a sample, you can alter to fit your tables:

delete distinctrow table1.* from table1 inner join table2 on table1.f1=table2.f1 and table1.f2=table2.f2 and table1=f3=table2.f3 where table2.field4=0

[amegahed3]

Yes, they need to match on the three fields.

Given that, then would the code that you gave do what I need to be done?

Thanks a lot for your reply.

Aly

[weekend00]

yes. please alter and try the code.
tell us if you get it done or not.

[amegahed3]

Hi,
Thanks a lot for your help.

I tried the following as per your advise:
DELETE distinctrow TransformerTypeAssemblyPeriodBOMProducts.*
FROM TransformerTypeAssemblyPeriodBOMProducts INNER JOIN TransformerTypeAssemblyPeriodProducts ON TransformerTypeAssemblyPeriodBOMProducts.Transform erTypeID=TransformerTypeAssemblyPeriodProducts.Tra nsformerTypeID AND TransformerTypeAssemblyPeriodBOMProducts.OutputPro ductID=TransformerTypeAssemblyPeriodProducts.Outpu tProductID AND TransformerTypeAssemblyPeriodBOMProducts .PeriodID=TransformerTypeAssemblyPeriodProducts.Pe riodID
WHERE TransformerTypeAssemblyPeriodProducts.AssemblyCapa city=0;
[/CODE]
But now, I get the following error: "invalid use of ".","!", or "()" in query expression". What do you think is wrong?

Thanks,
Aly

[amegahed3]

Well, I was actually finally able to get it done using the following code:

DELETE TransformerTypeAssemblyPeriodBOMProducts.*, TransformerTypeAssemblyPeriodProducts.AssemblyCapa city
FROM TransformerTypeAssemblyPeriodBOMProducts INNER JOIN TransformerTypeAssemblyPeriodProducts ON (TransformerTypeAssemblyPeriodBOMProducts.PeriodID =TransformerTypeAssemblyPeriodProducts.PeriodID) AND (TransformerTypeAssemblyPeriodBOMProducts.OutputPr oductID=TransformerTypeAssemblyPeriodProducts.Outp utProductID) AND (TransformerTypeAssemblyPeriodBOMProducts.Transfor merTypeID=TransformerTypeAssemblyPeriodProducts.Tr ansformerTypeID)
WHERE ((TransformerTypeAssemblyPeriodProducts.AssemblyCa pacity=0));

Thanks a lot for your help though.

Aly

[weekend00]

Glad to help.