Joining two tables in VB 6

[jhowell]

hi to everyone,
Im creating an application which it can show all records of the two tables in single form. its like costumerTable and OrderList. how can i show a record of specific costumer from costumertable together with his product order details from OrderList table. Supposed I have costumerNo field from my costumertable and also i have costumerNo field in my Orderlist table. I also wants to show the order details in datagrid view supposed costumer bought 5 items. can anyone help me find solution. any help we'll be appreciated. thanks

[plusplus]

let's say you have variable costumernr with the costumer's nummer and variable amount
adodc1.recordsource = "Select * from tblorder where costumerno = " & costumernr
adodc1.refresh
amount = adodc1.recordcount 'gives you the amount of records with that costumer nummer

do while not adodc1.recordset.eof
'do whatever you want to do with the orders of that costumer nummer
loop

[debasisdas]

Join both the tables using SQL command and display the output on grid.

[ryan_vietnow]

Join the 2 tables by use of the LEFT JOIN command in SQL.

[jhowell]

Join both the tables using SQL command and display the output on grid.

can you please show me your code in LEFT JOIN. I really do not know how is it. thanks

[debasisdas]

Kindly post both of your tables structure for further details on JOIN.

[jhowell]

lets say from Customer Table I have CustCode, CustName, CustAdd, CustTel. from my OrderList I have CustCode, PONum, ItemName, QTY, Amt.

I want to get all the records that my customer bought. e.i. customer bought 5 items. the other day he bought 10 items. how can i use sql to join two tables to be able to show the record which my customer bought (the 10 items). what should i do. is there any changes to my table structure for this..kindly post your suggestion please...

[jhowell]

lets say from Customer Table I have CustCode, CustName, CustAdd, CustTel. from my OrderList I have CustCode, PONum, ItemName, QTY, Amt.

I want to get all the records that my customer bought. e.i. customer bought 5 items. the other day he bought 10 items. how can i use sql to join two tables to be able to show the record which my customer bought (the 10 items). what should i do. is there any changes to my table structure for this..kindly post your suggestion please...

[leovicAustria]

hi to everyone, Im creating an application which it can show all records of the two tables in single form. its like costumerTable and OrderList. how can i show a record of specific costumer from costumertable together with his product order details from OrderList table. Supposed I have costumerNo field from my costumertable and also i have costumerNo field in my Orderlist table. I also wants to show the order details in datagrid view supposed costumer bought 5 items. can anyone help me find solution. any help we'll be appreciated. thanks

>>try to used Query...In mS access

[ryan_vietnow]

this is the query:
"SELECT CustName, CustAdd, CustTel.,
PONum, ItemName, QTY, Amt.
FROM Customer
LEFT JOIN OrderList
USING (CustCode)
WHERE CustCode='*your input****' ";
Got it?
Hope it helps....

[jireh]

here's a sample:

str = "select [Book File].Author,[Book File].BookTitle,Return,Penalty,[Transaction File].StudentNumber from ([Transaction Table] left join [Book File] on [Transaction Table].AccessionNumber=[Book File].AccessionNumber left join [Student File] on [Transaction Table].StudentNumber=[Student File].StudentNumber where return=1 and penalty<>0 and BorrowDate>='" & Format(dtpDateFrom, vbShortDate) & "' and BorrowDate<='" & Format(dtpDateTo, vbShortDate) & "'"

This is a three joned table