Joins !!

[orekin]

Access 2002 will not accept this query - Ambiguous Outer Join. But I
can't see how to get the recordset I need without an Ambiguous Outer
Join ? Here is the query as it currently stands:

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

[John Winterbottom]

"orekin" <ni***********@yahoo.com.au> wrote in message
news:6f**************************@posting.google.c om...

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.

Post your table structure with some sample data and the output you need and
someone will be able to help.

[orekin]

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 (it is actually based off
the access data):

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

The csv file is based off the data I have put in the access db.
Neither of the above contain macros.

Below is a query I wrote in Pervasive v8 that works fine, 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));

[John Winterbottom]

"orekin" <ni***********@yahoo.com.au> wrote in message
news:6f**************************@posting.google.c om...

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 (it is actually based off
the access data):

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

The csv file is based off the data I have put in the access db.
Neither of the above contain macros.

Thanks for the example. Now I need a short description of what exactly you
want to achieve here. I couldn't work it out from the tables you sent, (e.g.
"show each invoice toether with a summary of charges against that invoice
for the dates specified"). Also one of your join columns has incompatible
data types, (CRDUNIQ is text in one of the tables and decimal in another -
you need to fix that).

[orekin]

Hi John

Please find attached modified access db, with CRDUNIQ as long integer
in both tables. This was an oversight when I created the example
database:

http://s2.yousendit.com/d.aspx?id=F6...6A8E6B61885510

What am I trying to achieve - Create an Access Database used
specifically for management reporting.

The table we are looking at is the central table for Sales. From this
table I plan to have Excel pivot table with:
Categories = Customer, Ship To Location, Item #, Invoice Date (I'll
need to add that field to the query).
Measures = Sales (Quantity), Sales ($), COGS ($), Returns (Quantity),
Returns ($), Return COGS ($), Net Sales (Quantity), Net Sales ($),
Net COGS ($)

Management are not interested in seeing the details of credit notes.
Just the effect of credit notes on each line in our table.

So our output table should have the same number of lines as OEINVD,
and contain summarised credit note information for each line. For
example:
Invoice INV000000005759 Item 110222 has:
Quantity Returned = 3 $ Return = 0.87 $ Return COGS = $0.24
But note however that the above numbers are the sum of credit notes
CN0000000046726 and CN0000000046727.

The fields in our table are sourced from:
Invoice # OEINVH.INVNUMBER
Customer OEINVH.CUSTOMER
Ship to Code OEINVH.SHIPTO
Ship To Name OEINVH.SHPNAME
Invoice Total OEINVH.INVNETNOTX
Item # OEINVD.ITEM
Quantity Shipped OEINVD.QTYSHIPPED
$ Sales OEINVD.EXTINVMISC
$COGS OEINVD.EXTICOST
Quantity Returned OECRDD.QTYRETURN
$ Return OECRDD.EXTCRDMISC
$ Return COGS OECRDD.EXTCCOST

The problem I have is one of linking to OECRDD.

An invoice can have 0, 1 or >1 credit notes. The only way I could see
to link things was to start from OEINVD and:
OEINVD.INVNUMBER Inner Join OEINVH.INVNUMBER
OEINVH.INVNUMBER Left Outer Join OECRDH.INVNUMBER
OECRDH.CRDUNIQ Inner Join OECRDD.CRDUNIQ
OEINVD.ITEM Inner Join OECRD.ITEM

See how we need to go through INVNUMBER and CRDUNIQ before we can get
to the credit note details table ? We also need that last condition
(OEINVD.ITEM Inner Join OECRD.ITEM) otherwise our credit note sub
totals are incorrect.

Thanks in Advance
Bill

[orekin]

Hi John

Please find attached modified access db, with CRDUNIQ as text in both
tables. This was an oversight when I created the example database:

http://s2.yousendit.com/d.aspx?id=F6...6A8E6B61885510

What am I trying to achieve - Create an Access Database used
specifically for management reporting. I have spreadsheets that will
link to the reporting database and show pivot tables and filtered
lists.

The table we are looking at is the central table for Sales. From this
table I plan to have Excel pivot table with:
Categories = Customer, Ship To Location, Item #, Invoice Date (I'll
need to add that field to the query).
Measures = Sales (Quantity), Sales ($), COGS ($), Returns (Quantity),
Returns ($), Return COGS ($), Net Sales (Quantity), Net Sales ($),
Net COGS ($)

Management are not interested in seeing the details of credit notes.
Just the effect of credit notes on each line in our table. It is
important to know credit note action against particular items because
that could point out problems with suppliers, our warehouse procedures
.... etc

So our output table should have the same number of lines as OEINVD,
and contain summarised credit note information for each line. For
example:
Invoice INV000000005759 Item 110222 has:
Quantity Returned = 3 $ Return = 0.87 $ Return COGS = $0.24
But note however that the above numbers are the sum of credit notes
CN0000000046726 and CN0000000046727.

The fields in our table are sourced from:
Invoice # OEINVH.INVNUMBER
Customer OEINVH.CUSTOMER
Ship to Code OEINVH.SHIPTO
Ship To Name OEINVH.SHPNAME
Invoice Total OEINVH.INVNETNOTX
Item # OEINVD.ITEM
Quantity Shipped OEINVD.QTYSHIPPED
$ Sales OEINVD.EXTINVMISC
$COGS OEINVD.EXTICOST
Quantity Returned OECRDD.QTYRETURN
$ Return OECRDD.EXTCRDMISC
$ Return COGS OECRDD.EXTCCOST

The problem I have is one of linking to OECRDD.

An invoice can have 0, 1 or >1 credit notes. The only way I could see
to link things was to start from OEINVD and:
OEINVD.INVNUMBER Inner Join OEINVH.INVNUMBER
OEINVH.INVNUMBER Left Outer Join OECRDH.INVNUMBER
OECRDH.CRDUNIQ Inner Join OECRDD.CRDUNIQ
OEINVD.ITEM Inner Join OECRD.ITEM

See how we need to go through INVNUMBER and CRDUNIQ before we can get
to the credit note details table ? We also need that last condition
(OEINVD.ITEM Inner Join OECRD.ITEM) otherwise our credit note sub
totals are incorrect.

Thanks
Bill

[John Winterbottom]

"orekin" <ni***********@yahoo.com.au> wrote in message
news:6f**************************@posting.google.c om...

Hi John

Please find attached modified access db, with CRDUNIQ as long integer
in both tables. This was an oversight when I created the example
database:

http://s2.yousendit.com/d.aspx?id=F6...6A8E6B61885510

What am I trying to achieve - Create an Access Database used
specifically for management reporting.

The table we are looking at is the central table for Sales. From this
table I plan to have Excel pivot table with:
Categories = Customer, Ship To Location, Item #, Invoice Date (I'll
need to add that field to the query).
Measures = Sales (Quantity), Sales ($), COGS ($), Returns (Quantity),
Returns ($), Return COGS ($), Net Sales (Quantity), Net Sales ($),
Net COGS ($)

Management are not interested in seeing the details of credit notes.
Just the effect of credit notes on each line in our table.

So our output table should have the same number of lines as OEINVD,
and contain summarised credit note information for each line. For
example:
Invoice INV000000005759 Item 110222 has:
Quantity Returned = 3 $ Return = 0.87 $ Return COGS = $0.24
But note however that the above numbers are the sum of credit notes
CN0000000046726 and CN0000000046727.

The fields in our table are sourced from:
Invoice # OEINVH.INVNUMBER
Customer OEINVH.CUSTOMER
Ship to Code OEINVH.SHIPTO
Ship To Name OEINVH.SHPNAME
Invoice Total OEINVH.INVNETNOTX
Item # OEINVD.ITEM
Quantity Shipped OEINVD.QTYSHIPPED
$ Sales OEINVD.EXTINVMISC
$COGS OEINVD.EXTICOST
Quantity Returned OECRDD.QTYRETURN
$ Return OECRDD.EXTCRDMISC
$ Return COGS OECRDD.EXTCCOST

The problem I have is one of linking to OECRDD.
There are 2 basic ways to solve the problem; one is to use a subquery to get
the credit note information, the other is to use a derived table. Since you
only want a single computed column you are probably better off with a
subquery.

This is an example of how to do it. I tried to match what you have in the
spreadsheet but failed - for one thing, your table and column names hurt my
eyes :) probably you have to deal with something that is already set up so
you can't change that. Also, you have 58 rows in the spreadsheet but 71 in
table OEINVH , (or is it table OEINVD? I forget - the names don't mean much
to me - if you had something like "invoices" and "invoiceDetails" it would
be so much better!)

Anyway, here is an example of a subquery to get credit note summaries for
each line. Have a look at how it works and see if you can continue. If
you're still stuck, post back and I'll get some eyedrops and try and finish
it

select d.INVNUMBER as [Invoice #],
i.CUSTOMER as Customer,
i.SHIPTO as [Ship To name],
sum(d.QTYSHIPPED * d.EXTICOST),
(
select Sum(d2.QTYRETURN)
from OECRDD as d2 inner join OECRDH as h2
on d2.CRDUNIQ = h2.CRDUNIQ
where h2.INVNUMBER = d.INVNUMBER
) as QtyReturned
from OEINVD as d left join OEINVH as i on d.INVNUMBER = i.INVNUMBER
group by d.LINENUM, d.INVNUMBER, i.CUSTOMER, i.SHIPTO
order by d.LINENUM, d.INVNUMBER
An invoice can have 0, 1 or >1 credit notes. The only way I could see
to link things was to start from OEINVD and:
OEINVD.INVNUMBER Inner Join OEINVH.INVNUMBER
OEINVH.INVNUMBER Left Outer Join OECRDH.INVNUMBER
OECRDH.CRDUNIQ Inner Join OECRDD.CRDUNIQ
OEINVD.ITEM Inner Join OECRD.ITEM

See how we need to go through INVNUMBER and CRDUNIQ before we can get
to the credit note details table ? We also need that last condition
(OEINVD.ITEM Inner Join OECRD.ITEM) otherwise our credit note sub
totals are incorrect.

Thanks in Advance
Bill