SQL Join problems

[Shataken]

I have two tables that I need data from.
The table1 has a field that has a number in it (InComment is the field name). The number in the InComment field is the key in table2. I need to select all the data in table1, then pull in the field in the other table that corresponds to the value in the comment field in table1.

For example.

Table1(Machines ) InComment value = 34
Table2(Comments ) CommentID = 34
Table2(Comments ) Comment = "Comment Text"

I know this is some sort of join, but I have no clue how to get it to work. Below is what I am trying, that returns nothing:

SELECT Machines.MachineID, Machines.LoadID, Machines.AssetNum, Machines.SerialNum, Machines.ModelNum, Machines.InComment, Machines.MachineLoadNum, Machines.MachineSalesOrderNum, Comments.CommentID, Comments.Comment
FROM Machines LEFT JOIN Comments ON Machines.InComment = Comments.CommentID
WHERE (((Machines.LoadID)=1993) AND ((Machines.MachineSalesOrderNum) Like 'BRP553%'));

[MMcCarthy]

The joins look fine. Try this ...

[CODE=sql]
SELECT Machines.Machin eID, Machines.LoadID , Machines.AssetN um, Machines.Serial Num, Machines.ModelN um, Machines.InComm ent, Machines.Machin eLoadNum, Machines.Machin eSalesOrderNum, Comments.Commen tID, Comments.Commen t
FROM Machines LEFT JOIN Comments
ON Machines.InComm ent = Comments.Commen tID
WHERE (((Machines.Loa dID)=1993)
AND ((Machines.Mach ineSalesOrderNu m) Like 'BRP553*'));
[/CODE]

Just make sure that Comments.Commen tID and Machines.InComm ent have the same data type.

[NeoPa]

The joins look fine. Try this ...

[CODE=sql]
SELECT Machines.Machin eID, Machines.LoadID , Machines.AssetN um, Machines.Serial Num, Machines.ModelN um, Machines.InComm ent, Machines.Machin eLoadNum, Machines.Machin eSalesOrderNum, Comments.Commen tID, Comments.Commen t
FROM Machines LEFT JOIN Comments
ON Machines.InComm ent = Comments.Commen tID
WHERE (((Machines.Loa dID)=1993)
AND ((Machines.Mach ineSalesOrderNu m) Like 'BRP553*'));
[/CODE]

Just make sure that Comments.Commen tID and Machines.InComm ent have the same data type.

Access typically works with ANSI-89 rather than ANSI-92 standard wildcard characters. Instead of '%' and '_' it will use '*' and '?'.

See Access wildcard character reference for a full reference.

[Shataken]

Access typically works with ANSI-89 rather than ANSI-92 standard wildcard characters. Instead of '%' and '_' it will use '*' and '?'.

See Access wildcard character reference for a full reference.

That was it. Replacing the % with * solved it...man, that caused me about 5 handfulls of hair.

[NeoPa]

I still use the ANSI-89 compatible version myself, but I made a mental note when the new one became available in Access (2003) that I should remember it as it was likely to come up some time.
Glad it was helpful :)

[MMcCarthy]

I still use the ANSI-89 compatible version myself, but I made a mental note when the new one became available in Access (2003) that I should remember it as it was likely to come up some time.
Glad it was helpful :)

Ade

When you get some of that elusive thing called spare time can you put an article together on ANSI standards in Access

Mary

[NeoPa]

Standards in M$ software - that would take all of 5 seconds to write surely ;)

[MMcCarthy]

Standards in M$ software - that would take all of 5 seconds to write surely ;)

LOL!

Off you go then.

[Shataken]

Got another one. It has to be something to do with ANSII standards, but I hve tried both % and * in the LIKE statement.

cmbLoadNum.text = "BRP572-A"

This is how the SQL command looks in my code :

strSQL = "INSERT INTO Statements ( MachineID, AssetNum, SerialNum, ModelNum, PONumber, MachineSalesOrd erNum, OutgoingLoadNum , ShippingMachNum , ShipDate, InvoicedDate, InvoiceTxnID, StatementNum, CustomerName, ShipAdd1, ShipAdd2, ShipCity, ShipState, ShipZip, ShipZipPlus4, BillAdd1, BillAdd2, BillCity, BillState, BillZip, BillZipPlus4 )" & _
" SELECT Machines.Machin eID, Machines.AssetN um, Machines.Serial Num, Machines.ModelN um, Loads.PONumber, Machines.Machin eSalesOrderNum, Machines.Outgoi ngLoadNum, Machines.Shippi ngMachNum, Machines.Shippi ngDate, Machines.Invoic edDate, Machines.Invoic eTxnID, Machines.Statem entNum, Customer.Custom erName, Customer.ShipAd d1, Customer.ShipAd d2, Customer.ShipCi ty, Customer.ShipSt ate, Customer.ShipZi p, Customer.ShipZi pPlus4, Customer.BillAd d1, Customer.BillAd d2, Customer.BillCi ty, Customer.BillSt ate, Customer.BillZi p, Customer.BillZi pPlus4" & _
" FROM Customer INNER JOIN (Machines INNER JOIN Loads ON Machines.LoadID = Loads.LoadID) ON Customer.CustID = Loads.CustID" & _
" WHERE Machines.Outgoi ngLoadNum like '" + Mid(cmbLoadNum. Text, 1, (InStr(cmbLoadN um.Text, "-") - 1)) + "*'"


This is how it looks when I put a watch on it :

INSERT INTO Statements ( MachineID, AssetNum, SerialNum, ModelNum, PONumber, MachineSalesOrd erNum, OutgoingLoadNum , ShippingMachNum , ShipDate, InvoicedDate, InvoiceTxnID, StatementNum, CustomerName, ShipAdd1, ShipAdd2, ShipCity, ShipState, ShipZip, ShipZipPlus4, BillAdd1, BillAdd2, BillCity, BillState, BillZip, BillZipPlus4 )
SELECT Machines.Machin eID, Machines.AssetN um, Machines.Serial Num, Machines.ModelN um, Loads.PONumber, Machines.Machin eSalesOrderNum, Machines.Outgoi ngLoadNum, Machines.Shippi ngMachNum, Machines.Shippi ngDate, Machines.Invoic edDate, Machines.Invoic eTxnID, Machines.Statem entNum, Customer.Custom erName, Customer.ShipAd d1, Customer.ShipAd d2, Customer.ShipCi ty, Customer.ShipSt ate, Customer.ShipZi p, Customer.ShipZi pPlus4, Customer.BillAd d1, Customer.BillAd d2, Customer.BillCi ty, Customer.BillSt ate, Customer.BillZi p, Customer.BillZi pPlus4
FROM Customer
INNER JOIN (Machines INNER JOIN Loads ON Machines.LoadID = Loads.LoadID) ON Customer.CustID = Loads.CustID
WHERE Machines.Outgoi ngLoadNum like 'BRP572*'

It works when I pasdte the output into Access query, but I get an empty result set when it runs through my vb.net 2005 code?? Any suggestions on this one?

Thanks,
Jason

[NeoPa]

Jason,
You need to repost this in a separate thread.
Before you do though, I strongly suggest that you format such a large piece of code a lot more clearly. It MUST be in CODE tags, and it should not be left as a simple outpouring onto the page. If it's not laid out to be readable easily, you may well find that no-one bothers to read it. After all, why should they go to the trouble if you're not prepared to.