Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Well Thank you again Mr @Ratslinger, for your help and advise, and mainly not just giving me the answer when you could have, but pointing me in the right direction so that I was able to work it out for my self (only possible with the help you have given me with my last questions) I stripped down all the extra queries as per

@pascale You can add the Base file. Can't say any final result will become of it. Looked a bit more at the three Queries provided. Just as an example of Normalizing. In ALL three (and probably the other four) these conditions need to be met:

WHERE "EBSales"."NDT" = "paypal"."NDT" AND MONTH("EBSales"."Payé le") =:MOIS

Why not start with a result set which has this done and eliminate the six other times you request it? Again just an example.

i added to each "ID" from the sales table AND formed a LEFT JOIN between all, this then showed all results exactly as I wanted, I only learned this because I had to work out the answer myself, you did not give it to me on a plate! Thank you so much!!!!!! Here is the answer!`

SELECT DISTINCT "EBSales"."ID", "EBSales"."Payé le" AS "DATE", "EBSales"."Quantité" AS "QT", "EBSales"."shades" AS "LS", "EBSales"."stock" AS "ST", "postFR"."Poste FR €", "postEU"."Poste EU €", "postMONDE"."Poste MONDE €", "EBSales"."Prix total" AS "Prix en DEVISE", "FRSHADES€"."FR SHADES €" AS "FR LS €", "EUSHADES€"."EU TTC €" AS "EU LS €", "FRTTC€"."FR TTC €" AS "FR ST €", "EUTTC€"."EU TTC €" AS "EU ST €", "MONDETTC€"." MONDE TTC €", "STOCKVT€"."PrixdAchat", "paypal"."AvComm" + ( "REMVT"."Net" * "paypal"."Rate" ) AS "AvComm", "paypal"."Comm" * "paypal"."Rate" AS "Comm €", "REMVT"."Net" * "paypal"."Rate" AS "Rem €", "paypal"."NDT", "EBSales"."Pays" AS "Countries", "EBSales"."ID", "paypal"."Remboursement" FROM { oj "EBSales" LEFT OUTER JOIN "EUTTC€" ON "EBSales"."ID" = "EUTTC€"."ID" LEFT OUTER JOIN "EUSHADES€" ON "EBSales"."ID" = "EUSHADES€"."ID" LEFT OUTER JOIN "FRSHADES€" ON "EBSales"."ID" = "FRSHADES€"."ID" LEFT OUTER JOIN "FRTTC€" ON "EBSales"."ID" = "FRTTC€"."ID" LEFT OUTER JOIN "postFR" ON "EBSales"."ID" = "postFR"."ID" LEFT OUTER JOIN "postEU" ON "EBSales"."ID" = "postEU"."ID" LEFT OUTER JOIN "postMONDE" ON "EBSales"."ID" = "postMONDE"."ID" LEFT OUTER JOIN "MONDETTC€" ON "EBSales"."ID" = "MONDETTC€"."ID" LEFT OUTER JOIN "STOCKVT€" ON "EBSales"."stock" = "STOCKVT€"."Libellé personnalisé" }, { oj "paypal" LEFT OUTER JOIN "REMVT" ON "paypal"."Remboursement" = "REMVT"."NDT" } WHERE "EBSales"."NDT" = "paypal"."NDT" AND MONTH( "EBSales"."Payé le" ) = :MOIS

Thank you again Mr @Ratslinger

Kind regards

Paul