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"."I TEM" as "Item #", "OEINVD"."QTYSH IPPED" as "Quantity
Shipped", "OEINVD"."EXTIN VMISC" as "$ Sales", "OEINVD"."EXTIC OST" as
"$COGS", "OEINVH"."INVNU MBER" as "Invoice #", "OEINVH"."CUSTO MER" as
"Customer", "OECRDH"."CRDNU MBER" as "Credit Note #",
SUM("OECRDD"."Q TYRETURN") as "Quantity
Returned",SUM(" OECRDD"."EXTCRD MISC") as "$ Return",
SUM("OECRDD"."E XTCCOST") as "$ Return COGS"

FROM (("OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNU MBER" =
"OEINVH"."INVNU MBER") LEFT OUTER JOIN "OECRDH" ON "OEINVH"."INVNU MBER"
= "OECRDH"."INVNU MBER") INNER JOIN "OECRDD" ON "OECRDH"."CRDUN IQ" =
"OECRDD"."CRDUN IQ"

WHERE "OEINVD"."QTYSH IPPED">0 AND "OEINVH"."INVDA TE">20040501 AND
"OEINVD.ITE M" = "OECRDD.ITE M"

GROUP BY "OEINVD"."ITEM" , "OEINVD"."QTYSH IPPED",
"OEINVD"."EXTIN VMISC",
"OEINVD"."EXTIC OST","OEINVH"." INVNUMBER","OEI NVH"."CUSTOMER" ,
"OEINVH"."INVDA TE", "OECRDH"."CRDNU MBER";

......

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.INVNUMBE R

OEINVH.INVNUMBE R OuterLeft OECRDH.INVNUMBE R

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]

Re: Joins !!

"orekin" <nicholasrolfe@ yahoo.com.au> wrote in message
news:6f87d1d0.0 406120723.28b3e c40@posting.goo gle.com...[color=blue]
>
>
> 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.
>[/color]

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

[orekin]

Re: Joins !!

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

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

Here is a csv file showing my desired output (it is actually based off
the access data):

http://s2.yousendit.com/d.aspx?id=5E0E0733ACC854FB8483D4FD933F3F0C

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"."INVNU MBER" as "Invoice #",
"OEINVH"."CUSTO MER" as "Customer",
"OEINVH"."SHIPT O" as "Ship to Code",
"OEINVH"."SHPNA ME" as "Ship To Name",
"OEINVH"."INVNE TNOTX" as "Invoice Total",
"OEINVD"."I TEM" as "Item #",
"OEINVD"."QTYSH IPPED" as "Quantity Shipped",
"OEINVD"."EXTIN VMISC" as "$ Sales",
"OEINVD"."EXTIC OST" as "$COGS",
SUM("OECRDD"."Q TYRETURN") as "Quantity Returned",
SUM("OECRDD"."E XTCRDMISC") as "$ Return",
SUM("OECRDD"."E XTCCOST") as "$ Return COGS"

FROM "OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNU MBER" =
"OEINVH"."INVNU MBER"
INNER JOIN "OECRDH" ON "OEINVH"."INVNU MBER" = "OECRDH"."INVNU MBER"
INNER JOIN "OECRDD" ON "OECRDH"."CRDUN IQ" = "OECRDD"."CRDUN IQ"

WHERE "OEINVD"."QTYSH IPPED">0 AND "OEINVD"."I TEM" = "OECRDD"."I TEM"

GROUP BY
"OEINVH"."INVNU MBER",
"OEINVH"."CUSTO MER",
"OEINVH"."SHIPT O",
"OEINVH"."SHPNA ME",
"OEINVH"."INVNE TNOTX",
"OEINVD"."ITEM" ,
"OEINVD"."QTYSH IPPED",
"OEINVD"."EXTIN VMISC",
"OEINVD"."EXTIC OST"

UNION

SELECT
"OEINVH"."INVNU MBER" as "Invoice #",
"OEINVH"."CUSTO MER" as "Customer",
"OEINVH"."SHIPT O" as "Ship to Code",
"OEINVH"."SHPNA ME" as "Ship To Name",
"OEINVH"."INVNE TNOTX" as "Invoice Total",
"OEINVD"."I TEM" as "Item #",
"OEINVD"."QTYSH IPPED" as "Quantity Shipped",
"OEINVD"."EXTIN VMISC" as "$ Sales",
"OEINVD"."EXTIC OST" as "$COGS",
convert(0,sql_d ecimal) as "Quantity Returned",
convert(0,sql_d ecimal) as "$ Return",
convert(0,sql_d ecimal) as "$ Return COGS"

FROM "OEINVD" INNER JOIN "OEINVH" ON "OEINVD"."INVNU MBER" =
"OEINVH"."INVNU MBER"

WHERE "OEINVD"."QTYSH IPPED">0 AND ("OEINVH"."INVN UMBER" NOT IN (SELECT
DISTINCT OECRDH.INVNUMBE R from OECRDH));

[John Winterbottom]

Re: Joins !!

"orekin" <nicholasrolfe@ yahoo.com.au> wrote in message
news:6f87d1d0.0 406130202.12984 8c2@posting.goo gle.com...[color=blue]
> 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.
>[/color]

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]

Re: Joins !!

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=F6648C7CFDDEA40B136A8E6B61885510

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.INVNUMBE R
Customer OEINVH.CUSTOMER
Ship to Code OEINVH.SHIPTO
Ship To Name OEINVH.SHPNAME
Invoice Total OEINVH.INVNETNO TX
Item # OEINVD.ITEM
Quantity Shipped OEINVD.QTYSHIPP ED
$ Sales OEINVD.EXTINVMI SC
$COGS OEINVD.EXTICOST
Quantity Returned OECRDD.QTYRETUR N
$ Return OECRDD.EXTCRDMI SC
$ 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.INVNUMBE R Inner Join OEINVH.INVNUMBE R
OEINVH.INVNUMBE R Left Outer Join OECRDH.INVNUMBE R
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]

Re: Joins !!

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=F6648C7CFDDEA40B136A8E6B61885510

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.INVNUMBE R
Customer OEINVH.CUSTOMER
Ship to Code OEINVH.SHIPTO
Ship To Name OEINVH.SHPNAME
Invoice Total OEINVH.INVNETNO TX
Item # OEINVD.ITEM
Quantity Shipped OEINVD.QTYSHIPP ED
$ Sales OEINVD.EXTINVMI SC
$COGS OEINVD.EXTICOST
Quantity Returned OECRDD.QTYRETUR N
$ Return OECRDD.EXTCRDMI SC
$ 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.INVNUMBE R Inner Join OEINVH.INVNUMBE R
OEINVH.INVNUMBE R Left Outer Join OECRDH.INVNUMBE R
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]

Re: Joins !!

"orekin" <nicholasrolfe@ yahoo.com.au> wrote in message
news:6f87d1d0.0 406132049.47e29 a9a@posting.goo gle.com...[color=blue]
> 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.INVNUMBE R
> Customer OEINVH.CUSTOMER
> Ship to Code OEINVH.SHIPTO
> Ship To Name OEINVH.SHPNAME
> Invoice Total OEINVH.INVNETNO TX
> Item # OEINVD.ITEM
> Quantity Shipped OEINVD.QTYSHIPP ED
> $ Sales OEINVD.EXTINVMI SC
> $COGS OEINVD.EXTICOST
> Quantity Returned OECRDD.QTYRETUR N
> $ Return OECRDD.EXTCRDMI SC
> $ Return COGS OECRDD.EXTCCOST
>
> The problem I have is one of linking to OECRDD.[/color]

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.QTYSHIPPE D * d.EXTICOST),
(
select Sum(d2.QTYRETUR N)
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


[color=blue]
> 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.INVNUMBE R Inner Join OEINVH.INVNUMBE R
> OEINVH.INVNUMBE R Left Outer Join OECRDH.INVNUMBE R
> 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[/color]