Dear Samantha:
Yes, it can be done. What you are asking for is a FULL OUTER JOIN.
Access doesn't do FULL OUTER JOINs directly, but they can be simulated
with
a union of a left and a right join.
So, I need to rewrite this as a JOIN.
SELECT A.pn pnA, B.pn pnB, A.qty qtyA, B.qty atyB,
A.avgcost avgcostA, B.avgcost avgcostB,
A.qty - B.qty [qtyA-qtyB], A.avgcost - B.avgcost [avgcostA-avgcostB]
FROM YourTable A
LEFT JOIN YourTable B
ON LEFT(A.pn, LEN(A.pn) - 1) = LEFT(B.pn, LEN(B.pn) - 1)
WHERE LEFT(A.pn, LEN(A.pn) - 1) = LEFT(B.pn, LEN(B.pn))
AND RIGHT(A.pn, 1) = "A" AND (RIGHT(B.pn, 1) = "B" OR B.pn IS NULL
UNION ALL
SELECT A.pn pnA, B.pn pnB, A.qty qtyA, B.qty atyB,
A.avgcost avgcostA, B.avgcost avgcostB,
A.qty - B.qty [qtyA-qtyB], A.avgcost - B.avgcost [avgcostA-avgcostB]
FROM YourTable A
RIGHT JOIN YourTable B
ON LEFT(A.pn, LEN(A.pn) - 1) = LEFT(B.pn, LEN(B.pn) - 1)
WHERE LEFT(A.pn, LEN(A.pn) - 1) = LEFT(B.pn, LEN(B.pn))
AND A.pn IS NULL
I hope I got that right. Look at the results very carefully. It's
getting
a bit complex for me to do without being able to test along the way.
Make
sure you have data that tests all possibilities, and look at them
carefully.
Tom Ellison
Samantha said:
Hi Tom,
This helps but it seems like it will leave out some data in the final
query.
For example, what if pn data is:
pn: 100A, 100B, 200A, 200B, 300A, 400B
In the above case, the resulting query would leave out "300A" and
"400B".
I
want to show all of the data. Can this be done?
Thank you.
:
Dear Samantha:
You need to JOIN the table on itself, based on a portion of the pn
value.
SELECT A.pn pnA, B.pn pnB, A.qty qtyA, B.qty atyB,
A.avgcost avgcostA, B.avgcost avgcostB,
A.qty - B.qty [qtyA-qtyB], A.avgcost - B.avgcost
[avgcostA-avgcostB]
FROM YourTable A, YourTable B
WHERE LEFT(A.pn, LEN(A.pn) - 1) = LEFT(B.pn, LEN(B.pn))
AND RIGHT(A.pn, 1) = "A" AND RIGHT(B.pn, 1) = "B"
Does this work?
I have done it filtering a cross-product. This is a way of making a
JOIN
(without saying JOIN).
Tom Ellison
Hi Tom,
Let me clarify by starting over. I have a complicated query that
results
in
the following 3 fields:
pn
qty
avgcost
The data in field "pn" needs to be split into 2 categories: A or B.
Sample data:
pn: 100A, 100B, 200A, 200B
qty: 10, 15, 12, 9
avgcost: 1.0, 1.5, 3.2, 3.6
In the pn field, "100A" and "100B" are basically 2 categories of
similar
products , and we want to compare the qty and avgcost of both
categories.
What I need help on is how to create another query on top of the
existing
query to give me this result:
pnA, pnB, qtyA, qtyB, avgcostA, avgcostB, qytA-qtyB,
avgcostA-avgcostB
Result with sample data:
100A, 100B, 10, 15, 1.0, 1.5, -5, -0.5
200A, 200B, 12, 9, 3.2, 3.6, 3, 0.4
I hope this helps to explain the situation. Thanks for helping me
out.
:
Dear Samantha:
I suspect what the problem may be. If you'd post the query you
have
so
far,
I'd be more certain.
You cannot reference the new columns pnA and pnB within the query
in
which
they are created. You can, however, use the expressions:
LEFT(pn, LEN(pn) - 1)
RIGHT(pn, 1)
These would have the values you want.
Now, are these values necessary to JOIN to data in other tables or
rows?
Is
that where the qtyA and qtyB originate? I'm not at all clear on
where
this
is going next.
Tom Ellison
Hi Tom,
Thanks for your quick response. Whie your answer provides me add
two
columns, I still can not do a comparison between the other
fields,
e.g.:
qtyA
vs qtyB.
I hope I'm not confusing you.
:
Dear Samantha:
Use functions LEFT(), RIGHT(), and LEN() to get this:
pnA: LEFT(pn, LEN(pn) - 1)
pnB: RIGHT(pn, 1)
Tom Ellison
I have a query that results with the following 3 fields:
pn
qty
avgcost
But the data in field "pn" is two types: it either ends with
"A"
or
"B".
What I need is to divide the data in field "pn" into 2
columns:
"pnA"
and
"pnB", so that I can do comparison between A and B, as
follows:
pnA pnB qtyA qtyB avgcostA avgcostB
avgcostA-avgcostB
qytA-qtyB
Is there any way to construct a query to show the above? Any
help
is
very
much appreciated.
