[orekin]

I've tried this query with a few DBMS, but they either freeze or tell me Ambiguous Outer Join:

SELECT "OEINVD"."ITEM" as "Item #", "OEINVD"."QTYSHIPPED" as "Quantity Shipped", "OEINVD"."EXTINVMISC" as "$ Sales", "OEINVD"."EXTICOST" as "$COGS", "OEINVH"."INVNUMBER" as "Invoice #", "OEINVH"."CUSTOMER" as "Customer", "OECRDH"."CRDNUMBER" as "Credit Note #", SUM("OECRDD"."QTYRETURN") as "Quantity Returned",SUM("OECRDD"."EXTCRDMISC") as "$ Return", SUM("OECRDD"."EXTCCOST") as "$ Return COGS"

FROM (("OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNUMBER" = "OEINVH"."INVNUMBER") LEFT OUTER JOIN "OECRDH" ON "OEINVH"."INVNUMBER" = "OECRDH"."INVNUMBER") INNER JOIN "OECRDD" ON "OECRDH"."CRDUNIQ" = "OECRDD"."CRDUNIQ"

WHERE "OEINVD"."QTYSHIPPED">0 AND "OEINVH"."INVDATE">20040501 AND "OEINVD.ITEM" = "OECRDD.ITEM"

GROUP BY "OEINVD"."ITEM", "OEINVD"."QTYSHIPPED", "OEINVD"."EXTINVMISC", "OEINVD"."EXTICOST","OEINVH"."INVNUMBER","OEINVH". "CUSTOMER", "OEINVH"."INVDATE", "OECRDH"."CRDNUMBER";

....

OEINVH & OEINVD represent invoices, and are linked to each other via inner join on INVNUMBER

OECRDH and OECRDD represent credit notes and are linked to each other via inner join on CRDUNIQ

What I'm trying to do is list all the invoice detail lines (OEINVD) with credit note information if applicable. My first problem is that the detail level information for credit notes is in OECRDD. The only way I can see to link from OEINVD is:

OEINVD.INVNUMER = OEINVH.INVNUMBER

OEINVH.INVNUMBER OuterLeft OECRDH.INVNUMBER

OECRDH.CRDUNIQ = OECRDD.CRDUNIQ

OEINVD.ITEM = OECRDD.ITEM

I need that last join, otherwise the recordset would have incorrect summing on the credit note side.

My second issue is this - not every invoice has a credit note, and just to make life difficult, there can be > 1 credit note against an invoice! So in other words an invoice could have 0,1 or more credit notes against it. Where there is > 1 credit note against an invoice I want to sum the credit note fields.

I've come to a dead end, how can I get this working ?
Thanks
Bill

[r937]

that looks like microsoft access

whaddya mean, "tried this query with a few DBMS"????

where do you want it to run?

[orekin]

I want to run it in Access. I have actually got it working in Pervasive, but not in Access, which won't accept the 'Ambiguous Outer Joins' ? For someone that knows SQL this would probably be easy to figure out ....

Here is a simple access database with the four tables I am interested
in:

http://s2.yousendit.com/d.aspx?id=8D...CDD280DECE067B

Here is a csv file showing my desired output:

http://s2.yousendit.com/d.aspx?id=5E...83D4FD933F3F0C

Neither of the above contain macros.

Below is the query I wrote in Pervasive v8, but:
(a) It crashes Pervasive for all but the smallest databases
(b) I'm pretty much positive it is badly written !!!

Thanks In Advance
Bill
__________

SELECT

"OEINVH"."INVNUMBER" as "Invoice #",
"OEINVH"."CUSTOMER" as "Customer",
"OEINVH"."SHIPTO" as "Ship to Code",
"OEINVH"."SHPNAME" as "Ship To Name",
"OEINVH"."INVNETNOTX" as "Invoice Total",
"OEINVD"."ITEM" as "Item #",
"OEINVD"."QTYSHIPPED" as "Quantity Shipped",
"OEINVD"."EXTINVMISC" as "$ Sales",
"OEINVD"."EXTICOST" as "$COGS",
SUM("OECRDD"."QTYRETURN") as "Quantity Returned",
SUM("OECRDD"."EXTCRDMISC") as "$ Return",
SUM("OECRDD"."EXTCCOST") as "$ Return COGS"

FROM "OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNUMBER" =
"OEINVH"."INVNUMBER"
INNER JOIN "OECRDH" ON "OEINVH"."INVNUMBER" = "OECRDH"."INVNUMBER"
INNER JOIN "OECRDD" ON "OECRDH"."CRDUNIQ" = "OECRDD"."CRDUNIQ"

WHERE "OEINVD"."QTYSHIPPED">0 AND "OEINVD"."ITEM" = "OECRDD"."ITEM"

GROUP BY
"OEINVH"."INVNUMBER",
"OEINVH"."CUSTOMER",
"OEINVH"."SHIPTO",
"OEINVH"."SHPNAME",
"OEINVH"."INVNETNOTX",
"OEINVD"."ITEM",
"OEINVD"."QTYSHIPPED",
"OEINVD"."EXTINVMISC",
"OEINVD"."EXTICOST"

UNION

SELECT
"OEINVH"."INVNUMBER" as "Invoice #",
"OEINVH"."CUSTOMER" as "Customer",
"OEINVH"."SHIPTO" as "Ship to Code",
"OEINVH"."SHPNAME" as "Ship To Name",
"OEINVH"."INVNETNOTX" as "Invoice Total",
"OEINVD"."ITEM" as "Item #",
"OEINVD"."QTYSHIPPED" as "Quantity Shipped",
"OEINVD"."EXTINVMISC" as "$ Sales",
"OEINVD"."EXTICOST" as "$COGS",
convert(0,sql_decimal) as "Quantity Returned",
convert(0,sql_decimal) as "$ Return",
convert(0,sql_decimal) as "$ Return COGS"

FROM "OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNUMBER" =
"OEINVH"."INVNUMBER"

WHERE "OEINVD"."QTYSHIPPED">0 AND ("OEINVH"."INVNUMBER" NOT IN (SELECT
DISTINCT OECRDH.INVNUMBER from OECRDH));

[r937]

access requires that you parenthesize your joins

select ...
  from (
       (
       OEINVD 
inner 
  join OEINVH 
    on OEINVD.INVNUMBER 
     = OEINVH.INVNUMBER
       )
inner 
  join OECRDH 
    on OEINVH.INVNUMBER 
     = OECRDH.INVNUMBER
       )
inner 
  join OECRDD 
    on OECRDH.CRDUNIQ 
     = OECRDD.CRDUNIQ

sorry, can't test this, can't read your MDB