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

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

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

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

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

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

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

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

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.