Left Join dilemma is this possible?

J

JP

I am trying to create a recordset in Excel 2000 from two Access 2000 tables
to include all items from the first table "Liquor" and only items from the
second table "LiquorOrders" that match either a date or an order number. I
have 19 items in the first table and I would like to select all 19 items and
only these 19 items with a Status = True, with corresponding data from the
"LiquorOrders" with blanks where an order for an item was not placed on that
specific date or order number.

This at first seemed possible but after much researching and experimentation
I have all but given up. I would appreciate any and all assistance.

"SELECT Liquor.LiquorID,* FROM Liquor LEFT JOIN LiquorOrders ON
Liquor.LiquorID = LiquorOrders.LiquorID WHERE Status = true AND
LiquorOrders.OrderNumberID = 10"
 
J

Jamie Collins

Tushar Mehta wrote ...
Why don't you just use the GUI either in Access or in MS Query to
create the necessary query? Then, you can check the generated SQL.
Click to expand...

Tushar,
From the detail posted it appears the OP wants a join that looks like this:

SELECT
T1.*
FROM
Liquor T1
LEFT JOIN
LiquorOrders T2
ON
T1.LiquorID = T2.LiquorID
OR T1.Liquor.some_date_column =
T2.LiquorOrders.some_other_date_column
;

Can you suggest how the OP can generate such a join using the MS Query UI?

Jamie.

--
 
T

Tushar Mehta

The same way as one would in Access. Add both tables of interest, add
a link between the 2 tables (drag one field from one table to the
appropriate field in the other table), and finally double-click the
line representing the link and choose from inner/left/right join.

For a 'employee and project' database, the result in SQL of a left join
from employee to project would be:

SELECT `Employee$`.Name, `Project$`.Id
FROM {oj `C:\Temp\Book5`.`Employee$` `Employee$` LEFT OUTER JOIN `C:
\Temp\Book5`.`Project$` `Project$` ON `Employee$`.No = `Project$`.
`Employee ID`}

As far as the OP's 'detailed' post goes, it might make sense to you,
though, it didn't to me -- hence, the suggestion of the GUI approach.
I don't know how to interpret a request for an outer join with a
criterion based on the secondary table! Faced with those specs, both
Query and Access treat the outer join as though it were an inner join.
I, on the other hand, would complain. <g>

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
J

Jamie Collins

Tushar Mehta wrote ...
Add both tables of interest, add
a link between the 2 tables (drag one field from one table to the
appropriate field in the other table), and finally double-click the
line representing the link and choose from inner/left/right join.
Click to expand...

Your steps are to create a join on one column but I'm wondering if the
OP's question is how do they use the GUI tools to create a join on one
of two columns e.g.

Table1 T1 LEFT JOIN T2
ON T1.ID=T2.ID
OR T1.MyDateCol=T2.MyDateCol

How does one do this using the GUI tools? I honestly don't know
because I don't use them.

Jamie.

--
 
J

JP

Thanks for the discussion and suggestions. What I am trying to accomplish
is not only difficult but is also difficult to explain. In an attempt to
clarify my question I am providing part of my tables below with my latest
successful results. In researching this further I realized that I needed to
first create and run a query to select only the LiquorOrders.OrderNumberID
that I was interested in. For the example below, I queried the
LiquourOrders Table LiquorOrders.OrderNumberID =2 directly in Access with
the following sql:

LiquorOrdersQuery:
SELECT LiquorOrders.*
FROM LiquorOrders
WHERE (((LiquorOrders.OrderNumberID)=2));

I then created a second Query linking the Liquor table with the
LiquorOrdersQuery

TheQuery:
SELECT Liquor.*, LiquorOrdersQuery.*
FROM Liquor LEFT JOIN LiquorOrdersQuery ON Liquor.LiquorID =
LiquorOrdersQuery.LiquorID;

This gave me the desired results of creating a recordset that contained all
items from my Liqour Table with order details from LiqourOrders table that
matched the criteria of LiquorOrders.OrderNumberID =2 (or any OrderNumberID
I specify). While these results represent what I wanted to accomplish it
however creates a whole new dilemma. How do I use this in Excel VBA. It
would appear that both queries should be combined and run as a single query
but I have not been able to find any information on how to make this happen.

What I am trying to do using Excel 2000 in VBA is create a recordset (like
below) that will list all items from Table1 and additional fields from table
2 that are linked using a left join on an ID field. I would appreciate any
suggestions on what this should look like. I have included a simple test
macro I am using to check results in Excel. The sql statements are taken
from Access and need to be combined or changed to achieve the desired
recordset.

Sub JoinIt()
Dim sql As String
Dim x As Byte
Dim recLO As New Recordset
'
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
ThisWorkbook.Path & "\liqOrder.mdb;Jet OLEDB:Database;"
'
'this code needs to be combined to create a "Query on Query"
sql = "SELECT LiquorOrders.* FROM LiquorOrders WHERE
(((LiquorOrders.OrderNumberID)=2))"
sql = "SELECT Liquor.*, LiquorOrdersQuery.* FROM Liquor LEFT JOIN
LiquorOrdersQuery ON Liquor.LiquorID = LiquorOrdersQuery.LiquorID"
'
recLO.Open sql, conn, adOpenKeyset, adLockReadOnly
'
For x = 1 To recLO.RecordCount
MsgBox x & " of " & recLO.RecordCount & " " & recLO!Item & " " &
recLO!OrderNumberID & " " & recLO!OrderStamp
recLO.MoveNext
Next x

If recLO.State = 1 Then recLO.Close
If conn.State = 1 Then conn.Close
'
End Sub

Liquors (table 1 LEFT side)
LiquorID Item Par Unit Cost Stock DateAdded DateCancelled Status
1 Amaretto 6 1 $1.99 123456 04-Nov-03 TRUE
2 Bailey's Irish Cream 6 1 $1.99 123456 04-Nov-03 TRUE
3 Cabernet Sauvignon, BV Coastal 48 1 $1.99 123456 04-Nov-03 TRUE
4 Chambord 3 1 $1.99 123456 04-Nov-03 TRUE
5 Chardonnay, Chateau St. Michelle 60 1 $1.99 123456 04-Nov-03 TRUE
6 Courvoisier, VSOP 3 1 $1.99 123456 04-Nov-03 TRUE
7 Frangelico 6 1 $1.99 123456 04-Nov-03 TRUE
8 Godiva Chocolate Liqueur 3 1 $1.99 123456 04-Nov-03 TRUE



LiquorOrders (table 2 RIGHT side)
DateID OrderNumberID LiquorID Ordered Received OrderStamp CastID
ReceivedStamp ReceivedCastID
11/1/2003 1 8 1 1 11/1/2003 17:01 jper001 11/12/2003 9:01 jper001
11/30/2003 2 4 1 11/30/2003 10:04 jper001
11/30/2003 2 8 2 2 11/30/2003 10:04 jper001 12/3/2003 12:01 jper001
11/30/2003 2 10 3 3 6/23/2004 10:44 jper001 6/23/2004 10:45 jper001
11/30/2003 2 13 1 1 11/30/2003 10:04 jper001 6/23/2004 10:43 jper001
12/2/2003 3 1 1 12/2/2003 16:01 jper001
12/2/2003 3 3 3 12/2/2003 16:01 jper001
12/2/2003 3 4 4 12/2/2003 16:01 jper001
12/2/2003 3 5 5 12/2/2003 16:01 jper001
12/2/2003 3 7 7 12/2/2003 16:01 jper001


Recordset (this is what I want to accomplish)
Liquor.LiquorID Item Par Unit Cost Stock Status DateID OrderNumberID
Ordered Received OrderStamp CastID ReceivedStamp ReceivedCastID
1 Amaretto 6 1 $1.99 123456 TRUE
2 Bailey's Irish Cream 6 1 $1.99 123456 TRUE
3 Cabernet Sauvignon, BV Coastal 48 1 $1.99 123456 TRUE
4 Chambord 3 1 $1.99 123456 TRUE 11/30/2003 2 1 11/30/2003 10:04
jper001
5 Chardonnay, Chateau St. Michelle 60 1 $1.99 123456 TRUE
6 Courvoisier, VSOP 3 1 $1.99 123456 TRUE
7 Frangelico 6 1 $1.99 123456 TRUE
8 Godiva Chocolate Liqueur 3 1 $1.99 123456 TRUE 11/30/2003 2 2 2
11/30/2003 10:04 jper001 12/3/2003 12:01 jper001



Thanks again for your suggestions,

Joseph
 
J

Jamie Collins

JP said:
In researching this further I realized that I
needed to first create and run a query to
select only the LiquorOrders.OrderNumberID
that I was interested in.
Click to expand...

No, when using the Jet 4.0 provider (which you are) I always seem to
be able to achieve results in one query (and is better for the
optimizer AFAIK). Try this:

SELECT
L2.*,
L1.*
FROM
Liquor L1
LEFT JOIN
LiquorOrders L2
ON L1.LiquorID = L2.LiquorID
WHERE
L2.OrderNumberID=2
;

Jamie.

--
 
Top