INNER JOIN With Multiple Tables
hi,
i've searched a lot but couldn't find anything. I've 4 tables
table1: f1, f2, f3, f4
table2: f1, f2
table3: f1, f2
table4: f1, f2
i want to join theese tables so that the result will look like this;
table1.f1, table2.f1, table3.f1, table4.f1
the join conditions are
table1.f2 = table2.f2, table1.f3 = table3.f2, table1.f4 = table4.f2
i can join two by two but not 4 in the same time
can anyone help me?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Self Join With Multiple Tables
Hi, Ive a working Query...
SELECT dbo.TblLookupPreferences.ContactType, dbo.TblEmailAddress.Email, dbo.TblEmailAddress.Name, dbo.TblEmailAddress.Title,
dbo.TblEmailAddress.DoNotMail, dbo.TblLookupPreferences.AssociatedType
FROM dbo.TblAdmMain INNER JOIN
dbo.TblLookupPreferences ON dbo.TblAdmMain.AdminID = dbo.TblLookupPreferences.AssociatedID INNER JOIN
dbo.TblEmailAddress ON dbo.TblLookupPreferences.LookupValue = dbo.TblEmailAddress.EmailID
WHERE (dbo.TblLookupPreferences.ContactType = '7') AND (dbo.TblEmailAddress.DoNotMail = 'N') AND (dbo.TblLookupPreferences.AssociatedType = 'A')
But Now I want to add another table to the same view..
The Other TableName is 'TblProspectmembers'
In need to join tblprospectmembers [Memno] field =
TblLookupPreferences.AssociatedID
WHERE
TblLookupPreferences.AssociatedType = 'M'
but I cant figure out the exact syntax for this.
any help would be highly appreciated!
(oh im using Sql server 2000 and Access 2000 as a front end)
How To Join 6 Tables And Have 2 Tables Query 1 For Same Field??
Sorry that subject isn't clearer but it's kind of strange what I'm trying to do. I have a database table named "tblJob". This table (which gets updated a LOT) holds secondary keys (I think that's what they're called) of the primary keys of other tables. Here's the layout.
tblJob
jobID (auto-number)
custID (number -- from tblCustomer)
empID (number -- from tblEmployee)
locID (number -- from tblLocation)
homeID (number -- from tblHomeArea)
jobStatus (text)
tblCustomer
custID (auto-number)
custFName (text)
custLName (text)
tblLocation
locID (auto-number)
locAddr1 (text)
cityID (number -- from tblCity)
tblHomeArea
homeID (auto-number)
homeAddr1 (text)
homeAddr2 (text)
cityID (number -- from tblCity)
tblCity
cityID (auto-number)
cityName (text)
tblEmployee
empID (auto-number)
empName (text)
empUnitName (text)
Basically I want to query all the records from tblJob WHERE the jobStatus = 'Employee Assigned'. But I'm outputting all of the results into a datagrid in the form so that that the user can clearly see which employee is working with which customer, where (street and city), the customers name, etc.
Anyway, here is the query that I've written which SOMEWHAT works:
Code:
SELECT tblJob.jobID, tblJob.custID, tblJob.empID, tblJob.locID, tblJob.homeID, tblJob.jobStatus,
tblLocation.locID, tblLocation.locAddr1, tblLocation.cityID,
tblHomeArea.homeID, tblHomeArea.homeAddr1, tblHomeArea.cityID,
tblCustomer.custID, tblCustomer.custFName, tblCustomer.custLName,
tblEmployee.empID, tblEmployee.empUnitName,
tblCity.cityID, tblCity.cityName
FROM (((tblJob INNER JOIN tblLocation ON tblJob.locID = tblLocation.locID)
INNER JOIN tblHomeArea ON tblJob.homeID = tblHomeArea.homeID)
INNER JOIN tblCustomer ON tblJob.custID = tblCustomer.custID)
INNER JOIN tblEmployee ON tblJob.empID = tblEmployee.empID
WHERE tblJob.jobStatus='Assigned'
This code (which I've cleaned up using a text editor for purposes of this post) is being put into the ADODC.Recordsource field. When I run it in Access 2000 (as a query), I get a pop-up box that asks for "tblCity.cityID" and "tblCity.cityName". When I run it in VB 6.0, I get this error:
Quote:No value given for one or more required parameters
I'm pretty sure the problem is that I'm referencing the cityName field in my select but I'm not making that part of my INNER JOIN code.
Here's my problem. How do I run a query (is it another JOIN or a sub-query???) to get the cityName from tblCity where the tblCity.cityID matches tblLocation.cityID AND
get the cityname from tblCity where the tblCity.cityID matches tblHomeArea.cityID?
I've tried just adding in additional inner joins but none of that is working.
Any help with this would be greatly appreciated. I thought I had read somewhere that sub-queries are slower than JOINS but I am so new to JOINS that I can't figure this out and sub-queries are something I've never even touched.
Thank you in advance for any help you can give me.
DTFan
Edited by - DTFan on 8/22/2003 10:11:30 AM
Inner Join 3 Tables
i always get a syntax error on the following statement:
sqltekst = "select * from TblGebruikers inner join Tblstudenten on TblGebruikers.Gebruikersnr = Tblstudenten.Gebruikersnr and inner join TblLesgevers on TblGebruikers.Gebruikersnr = TblLesgevers.Gebruikersnr order by TblGebruikers.Naam"
tnx in advance for the help
Des
Join 3 Tables
hi guys! can you please help regarding this?! i tried to join 3 tables but it gives me an error, in join statement. heres my code:
Code:
Sub ListViewClick()
Dim i As Integer
i = ListView1.SelectedItem.Index
Set rs = New ADODB.Recordset
rs.Open "select tblColEnrol.ControlNum, tblColEnrol.Name, tblColEnrol.Surname, " & _
" tblColEnrol.Middle, tblColEnrol.Year, tblColEnrol.Course, " & _
" tblColEnrol.Semester, tblStudSubj.Subject, tblStudSubj.Description, tblGrades.Professor, " & _
" tblGrades.Prelim, tblGrades.Midterm, tblGrades.Final, tblGrades.FinalRating, tblGrades.Remarks, " & _
" from tblColEnrol inner join (tblStudSubj inner join tblGrades on tblGrades.ControlNum =" & _
"tblStudSubj.CtlNum) on tblGrades.ControlNum = " & _
"tblStudSubj.CtlNum where Surname='" & ListView1.ListItems.Item(i).ListSubItems(1).Text & "'", Con, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
txtControl.Text = rs.Fields("tblColEnrol.ControlNum")
txtYear.Text = rs.Fields("tblColEnrol.Year")
txtCourse.Text = rs.Fields("tblColEnrol.Course")
txtSem.Text = rs.Fields("tblColEnrol.Semester")
txtSubject.Text = rs.Fields("tblStudSubj.Subject")
txtDescription.Text = rs.Fields("tblStudSubj.Description")
txtProf.Text = rs.Fields("tblGrades.Professor")
txtPrelim.Text = rs.Fields("tblGrades.Prelim")
txtMidterm.Text = rs.Fields("tblGrades.Midterm")
txtFinal.Text = rs.Fields("tblGrades.Final")
txtFRating.Text = rs.Fields("tblGrades.FinalRating")
txtRemarks.Text = rs.Fields("tblGrades.remarks")
If IsNull(rs.Fields("tblGrades.Professor")) Or IsNull(rs.Fields("tblGrades.Prelim")) Or IsNull(rs.Fields("tblGrades.Midterm")) Or IsNull(rs.Fields("tblGrades.Final")) Or IsNull(rs.Fields("tblGrades.FinalRating")) Or IsNull("tblGrades.Remarks") Then
txtProf.SetFocus
End If
End If
If rs.BOF And rs.EOF Then
Exit Sub
End If
End Sub
Join To Tables
I have table name "Kerja" with primary key is "ID" and also table "PEST" with primary key is "PEST_ID". How I can join both table using its primary key?Code below does not work to join both table
Quote:
rec.Open "Select * from kerja where ID.kerja,PEST_ID.PEST", con, adOpenDynamic, adLockOptimistic
Is Their Is Any Way To Join 3 Tables In 1 ???
Hi All ....
I am using VB6 , ADO , ACCESS DataBase
I need to join 3 tables in 1 table without any condetion For Ex. :
Table 1 : markesales
Fields : KN , QUIN , TPR , SALEDATE
Table 2 : marketcamestuff
Fields : KNIN , QUININ , TPRIN
Table 3 : marketexp
Fields : EXP , EXPVALUE
Join the 3 Tables to have 1 table Named "Marketreporttable" Have all the Fields to send that table to crystal report To have that Report in the attachment ....
thx in advance .
Can You Inner Join More Then 2 Tables?
I have a database that has about 8 tables that are related in some way. I just figured out how to Inner Join 2 tables but I was wondering if you could join more then that. If so how would you go about coding that?
here is what I have now:
I want to join Products Table with the OrderDetails Table. ProductCode would be the joining field...
rsOrders.Open ("SELECT * FROM OrderDetails INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID where PickupDate >= #" & DateStart & "# And PickupDate <= #" & DateEnd & "# and OrderDetails.ProductCode >= " & StartPLU & " And OrderDetails.ProductCode <= " & EndPLU & ""), CN
Join Between Tables
earlier i was using foxpro and in that for e.g. i had 2 tables with the common field as sc_code, on which i set a relationship between the tables. and when i move a record in the main table, simultaneously the record pointer moves to the corresponding record in the other table. and if the corresponding record is not found the record pointer will be at EOF of the other table.
i want to do the same operation in visual basic. i know i can do it with seek method, but i want to know is there any other method which i can use for such kind of operations, as i have to set relationship between 5 to 6 tables at a time.
pls. guide.
regards
prakash
Join Two Tables
I haev two tables in Access 2000, with the same type of data in them.
Table1
ID, Name, Company
Table2
ID, Name, Company
I would like to join this two tables in a query like this:
Query1
Name, Company
Is this posible?
Tahnks
How To Join All These Tables?
Hi Friends,
I have an application in vb & access2000.
I have five tables {customer_master,parts_charge,oil_charge,labour_charge,service_records} in my database.
I want to generate query which have all fields from all tables on specific date. I mean If i enter 28/02/2004 than it gives me all records of this data from all tables. Even If there is any null or blank records it should be display in query result.
Please it become very tidious problem for me so reply me soon,
I have attached database herewith.
Pratik Mehta
Inner Join For 4 Tables?
I've got 4 tables in my database that I want to run a query against to get some information on a particular customers history. Here are the tables, along with the fields in each that I need:
tblCustHistory
custHistID, custItem, cityID, empID
tblCity
cityID, cityName, stateID
tblState
stateID, stateName
tblEmployee
empID, empName
What I am trying to do is run a query that will return the custHistID, custItem, cityName (from the city ID fields in tblCustHistory and tblCity), the state name (from state ID fields in tblCity and tblState) and the employee's name (from empID fields in tblCustHistory and tblEmployee).
I thought that the best way to approach this would be 3 inner joins but I keep getting this error:
Quote:Syntax error in JOIN operation
Here is the code that I am using. I'm not sure what is wrong (do you use paranthesis in VB??) but I've tried several derivations of this an all are creating the same error.
Code:
sqlGetCustHistory = "Select TOP 5 tCH.*, tCy.*, tSt.*, tEm.* " & _
"From (tblCustHistory tCH INNER JOIN tblCity tCy ON tCH.cityID = tCy.cityID) " & _
"(tblState tSt INNER JOIN tCy ON tSt.stateID = tCy.stateID) " & _
"tblEmployee tEm INNER JOIN tCH ON tEm.empID = tCH.empID"
conCustHistory.RecordSource = sqlGetCustHistory
I tried removing all of the parenthesis but then I got a different error (looked like VB couldn't distinguish between the Inner Joins.
Given the scenario that I outlined, are 3 inner joins the way to go or should I look at another type of Join?
Again, thank you in advance for any help that you can give.
DTFan
How To Join 2 Tables
I have table TER01 and table PTH_TEMP and i wanna add records from PTH_TEMP INTO TER01 but when i write
sql = "SELECT * INTO TER01 FROM PTH_TEMP"
i have error "Table Ter01 Exist"
Join Two Tables From Two Servers With Ado
I want to join two tables, but they are on different servers. The only examples that I've seen so far have two tables joined as long as they are on the same server. Can anyone explain (showing the code too) or tell me where I can find info on this? THANKS!
Join Tables With Query
Hello all, Could someone please point me in the right direction here. I have a query that returns recordsets that I would like to use in another query. The first query can return more than one record, so I need to loop through the query and assign the values to a variable, then loop through the second with the variables assigned by the first query. I may be doing this all wrong, and if so, HELP, PLEASE.
Could Join Work?????
Code:
'First Query, I need to use Date, and AccountNumber in the next Query
SqlStat = "SELECT Business_Ads.Date, Business_Ads.AccountNumber, Business_Ads.Business, Business_Ads.AdSize, Business_Ads.Classification, Business_Ads.Book, Business_Ads.Comments, Business_Ads.NetContract, Business_Ads.Rate, Business_Ads.ContractType, Business_Ads.SalesRep" _
& " From Business_Ads" _
& " GROUP BY Business_Ads.Date, Business_Ads.AccountNumber, Business_Ads.Business, Business_Ads.AdSize, Business_Ads.Classification, Business_Ads.Book, Business_Ads.Comments, Business_Ads.NetContract, Business_Ads.Rate, Business_Ads.ContractType, Business_Ads.SalesRep" _
& " HAVING Business_Ads.Date Between # " & txtDate1.Text & "# And #" & txtDate2.Text & "# AND Business_Ads.SalesRep='" & cboEmployee.Text & "'"
rs.Open SqlStat, dbCon
'This is the second query
SqlStat1 = "SELECT Payment.Date, Payment.AccountNumber, Payment.Business, Payment.Book, Payment.Payment, Payment.PaymentType, Payment.Number, Payment.SalesRep" _
& " From Payment" _
& " WHERE (((Payment.Date)=#9/9/2007#) AND ((Payment.AccountNumber)="727-9335"));
SQL Query, Inner Join 3 Tables
Gooday all,
got the followin query, to bring data from 2 related tables.
VB Code:
sSQL = "SELECT P.*, PD.Dtext FROM Products AS P INNER JOIN PaDimensions AS PD ON P.PaDim = PD.Id WHERE ProductName= '" & Combo5.Text & "'"
cant i seem to get the syntax right to get data from 3 related tables. tried many varietions like :
VB Code:
sSQL = "SELECT P.*, PD.Dtext, PP.Ptext FROM Products AS P INNER JOIN PaDimensions AS PD ON P.PaDim = PD.Id [color=Red]AS P INNER JOIN Price AS PP ON P.PriceCode = PP.Id[/color] WHERE ProductName= '" & Combo5.Text & "'"
or
VB Code:
sSQL = "SELECT P.*, PD.Dtext, PP.Ptext FROM Products AS P INNER JOIN PaDimensions AS PD ON P.PaDim = PD.Id [color=Red]AND P INNER JOIN Price AS PP ON P.PriceCode = PP.Id[/color] WHERE ProductName= '" & Combo5.Text & "'"
no success. when :
"Price"= 3rd related table.
"Id" is a field in "Price" related, on to many, to "PriceCode" field in "Products"
(in red are my additions to original query)
thanks in advanse for ideas/suggestions..
regards,
Join Two Tables From Two Different Databases
Dear Friends,
I have two different database, having many common fields in tables in both databases. I want to join tables from both databases in a recordset is it possible if yes how?. Please reply with sample codes.
Thanks
Trying To Make A Join Between Two Tables From Two DBs
I'm trying to make a join between two recordsets (ADO) from two differents connections. I just can't get it to work.
The two tables can be from anywhere on my drive so I have no choice to make two connections (I think so...). Now, I need to know if a piece of info from one table can be found in the other one. My query in theorically fine, but I don't know how to put (identify) the two recordsets from the two connections in my query.
I tried many ways, but I keep getting an error stating that I am asking for a command that can not be executed through ODBC. The problem is with the access to both tables: I tried other queries and they work fine.
Some help would be welcome, I am really stuck there.
Damby
(VB6)(URGENT)Sql To Join 2 Tables
Hon'ble gurus,
I am using VB6 and Access.
I have 2 tables named: Stock and Purchase and both have same fields.
STOCK
Items Company Quantity
-------------------------------------------------------------------------------------
Crocin Paramount 100
Anacin Midley 75
Stopache Paras 300
Anacin Paramount 40
PURCHASE
Crocin Paramount 90
Stopache Paras 125
Moov ointment Heal Pharma 30
Is it possible to make a query like the following from the tables?
Items Company Quantity
-------------------------------------------------------------------------------------
Crocin Paramount 190
Anacin Midley 115
Stopache Paras 425
Moov ointment Heal Pharma 30
I failed to write the sql for this as the items are not same in both the tables. I'll be immensely grateful if anybody can help me.
pkb_pkb
pkb_pkb
Join Tables, Or Other Method
I have a 'task' table with a priority field, this field is an integer (1-5), foriegn key to a priority table
(id (1-5), and name varchar2(20)).
I currently have the task table displaying in a datagrid, there is a column that displays the priority.
I'd like that column to display the 'name' associated with the priority instead of the id.
Should I just 'join the tables' for the select, or is there another way to do this job without the join.
FYI, there are several fields like this that I would end up having to join (big joins, big recordsets, you do the math . . )
I'd like to be able to sort by the order of the ID (not the name associated with the ID).
Suggestions? (I know with the datacombo object, I can show a name and have a 'number' entered into the field. But I haven't found a way (visual studio 6), to have a datacombo box be insize a datagrid column
Need Help With ADO Outer Join With 3 Tables.. Can't Get The Syntax Right
adoTimeCardsRS.Open "SELECT op_desc, hours_elapsed, qty_produced FROM timed LEFT OUTER JOIN operations ON operations.op_id = timed.op_id WHERE part_id = '" & txtPart.Text & "' ", deCosting.cnCosting, adOpenStatic, adLockReadOnly
Now, say I wanted to select emp_id from timeh where timeh.tim_id = timed.tim_id how would I go about implementing that? Every time I try to add even another table it gives me runtime errors.
I try the SQL builder with Visual Basic 6 but even that gives me bad syntax when I let it make the syntax for me.. ????
Thanks!
How To Join Tables And Generate Report
i have an access database with about thirty tables, each table have about ten or more coumns or fields. one patient may have different tests from different tables of the database. i am stuck up to generate reports, especially of one patient with tests from different tables and single report with all the test results on it. how can i generate such reports in vb6 and how can i join the tables by sql parametrized queries. please help me.
Compare/join Two Tables From Different Databases
Hi I am currently using a data environment to store sql queries the trouble is I have got to a query where I need to use tables from two different datasources, is this possible. The query being:
SELECT sttrgprf.register_id, sttrgprf.register_group, sttrgprf.day_of_week, MID(sttrgprf.start_time, 12, 2) & MID(sttrgprf.start_time, 15, 2) AS start_time, MID(sttrgprf.end_time, 12, 2) & MID(sttrgprf.end_time, 15, 2) AS end_time, sttrgprf.room_code, WeekNo.WeekNo FROM sttrgprf, WeekNo WHERE sttrgprf.end_week >= WeekNo.WeekNo AND sttrgprf.start_week <= WeekNo.WeekNo AND (sttrgprf.acad_period = @AcadYear) AND (sttrgprf.room_code LIKE @RoomCode & '%') AND (WeekNo.WeekNo = @WeekNo1) ORDER BY day_of_week, start_time
This essentially queries sttrgprf for all unique register id's and using there start week and end week generates an individual record for each register (because WeekNo contains no's 1 to 53). This works when I connect datasources through msaccess unfortunatley though the sttrgprf datasource is a ms sql server where no table can be added to it (designed by someone else). Any help appreciated.
Populate A Grid From A JOIN Tables
Anybody know how to populate a grid from a JOIN tables database. If you have an example please let me know because I do not know how to start.
Thanks
How Can I Use Data Relationship Or Join Tables
I want join to tables from an index field then I want to collecting field form both tables. Also, I want to place those field into a grid, in another words I want to place some field into text boxes and another fields into a grid. How can I do that. here is an example of my code.
FROM table1 JOING table2 ON table1.customer = table2.orders;
I am tired trying this but it does not work in my collection, I will apreciate your help
Inner Join Help Needed , How Can I Make Two Tables Into One
Basically I want to make two tables into one, I am trying to do an inner join on two tables so I have one big recordset to work with , this is what I have which doesn't work
SELECT fqorders.* from fqorders , fqordernote.* from fqordernote inner join orderitem on fqorders.orderitem = fqordernote.orderitem;
I want all fields from both these tables into one big table or recordset so I can work with it , the common field is orderitem.
Can anyone show me how to write this ?
cheers
locutus
Join Two Tables From 2 Differents Database
Hello
I have this code:
'''''Table1 is in Database1
'''''Table2 is in Database2
Dim wkODBC as DAO.workspace
Dim conDb1 as Dao.connection
Dim qdef as DAO.querydef
Dim rs as Dao.recordset
Dim strConnect as string
Dim SQL as string
strConnect="ODBC;DSN=AS400-Database1;UID=uid;PWD=pwd"
set wkODBC=dbengine.createworkspace("","","",dbuseODBC)
wkODBC.defaultcursordriver=dbUseODBCCursor
set conDb1=WkODBC.openconnection("",dbdriverComplete,true,strConnect)
set qdef=conDb1.CreateQueryDef("")
SQL="SELECT table1.field1,table2.field1 "
SQL=SQL & " from Table1 Inner Join "
SQL=SQL & [ODBC;DATABASE=AS400-Database2;DSN=AS400-Database2].Table2 "
SQL=SQL & "On table1.field1=table2.field1 "
qdef.SQL=SQL
set rs=qdef.openrecordset(dbopensnapshot,dbreadOnly,dbreadOnly)
[Not Resolved]JOINING TWO TABLES ( USING INNER JOIN )...HOW TO USE?
hI,
i have two tables; borrower and borrowerdetails, i want to combine these two tables using sql statement INNER JOIN, but i have encounter errors using using INNER JOIN. below is my code. tell me whats my mistake...when my form loaded i want to show all data(s) of table borrower and borrowerdetails in my text boxes.
error message: No value given for one or more required parameters....
here's my code:
Private Sub Form_Load()
strsql = "SELECT Borrower.BwdID, BwdDetails.DetailsID, Borrower.BorrowersName, Borrower.Address, Borrower.Phone, BwdDetails.AlbCode, BwdDetails.Albtype, BwdDetails.Title, BwdDetails.ArtistName, BwdDetails.Artistcode, BwdDetails.Quantity, BwdDetails.DtBwd, BwdDetails.BwdTo, BwdDetails.DtRtnd, BwdDetails.RtndTo FROM Borrower INNER JOIN BwdDetails ON Borrower.BwdID = BwdDetails.DetailsID"
Set RS = New ADODB.Recordset
RS.Open strsql, ConnectString(), adOpenKeyset, adLockPessimistic, adCmdText
With RS
If RS.RecordCount <> 0 Then
txtBorrowID = !bwdid
txtName = !borrowersname
txtAddress = !Address
txtPhone = !phone
txtAlbumCode = !albcode
txtAlbumtype = !albtype
txtTitle = !Title
txtArtistName = !artistname
txtArtistCode = !artistcode
txtQty = !quantity
txtDtBwd = !dtbwd
txtBwdto = !bwdto
txtDtRtnd = !dtrtnd
txtRtndBy = !rtndby
Text1 = !detailsID
Else
txtBorrowID = ""
txtName = ""
txtAddress = ""
txtPhone = ""
txtAlbumCode = ""
txtAlbumtype = ""
txtTitle = ""
txtArtistName = ""
txtArtistCode = ""
txtQty = ""
txtDtBwd = ""
txtBwdto = ""
txtDtRtnd = ""
txtRtndBy = ""
End If
End With
End Sub
Edited by - edd_hills on 12/26/2004 10:12:14 PM
Order Of Tables In A Join Relation
Hai every body,
I would like to know in which order we should place the tables in a join relation in the Where Clause (Not in FROM clause). In the sense, should the Driving table come first or it should be on the right side of the operator.
For example,
SELECT emp.empno, emp.ename, dept.deptname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND
emp.empno = '1056'
Here the driving table is emp. So in the join relation "emp.deptno = dept.deptno ", emp come first ie, on the left side of the operator which is the commonly used method. Is this the right way of writing the relation or should the driven table (dept) come first ie, dept.deptno = emp.deptno. Which one is better... ?
When i took the explain plan, there was no difference at all for both forms.
Please help..
Regards
Issac
Incorrect Syntax Near Keyword INNER; Join On 3 Tables
Hey all. I have a fairly complex query that involves updating fields in 3 tables.
It is an SQL Server database...
I have this written:
Code:
cn.Execute "UPDATE REQSTION INNER JOIN ((WOP INNER JOIN EQUIP ON WOP.EQNUM = EQUIP.EQNUM) " & _
"INNER JOIN WO ON WOP.WONUM = WO.WONUM) ON REQSTION.REQUISITIONNUM = WOP.ITEMNUM SET " & _
"WO.TASKDESC = '" & txtJOBDESC & "', WOP.LOCATION = '" & txtLOCATION & "', " & _
"WOP.DESCRIPTION = '" & txtVENDOR & "', WOP.QTYREQD = " & CDbl(txtQTY) & ", " & _
"WOP.TOTALUNITCOST = " & CCur(txtCOST) & ", REQSTION.REQUISITIONDATE = '" & txtRequestDate & "', " & _
"REQSTION.REQUESTORNAME = '" & txtRequestor & "', " & _
"REQSTION.NEEDBYDATE = '" & dtpNEEDED.Value & "', REQSTION.ACCTNUM = '" & txtACCT & "', " & _
"REQSTION.NOTES = '" & txtREMARKS & "' " & _
"WHERE REQSTION.REQUISITIONNUM = '" & txtIR & "'", recs
and it's telling me INCORRECT SYNTAX NEAR KEYWORD INNER. Can someone take a look and help me out here plz? Thanks.
Editing Data In A Grid With Join Tables
Hello,
I'm trying to create a grid that I will be able to add and update in. My RecordSource is SQL query with JOIN tables and database is SQL 2000.
I need grid to have combo boxes or dropdown lists that users can select only one of the values that is already in that database field.
I've done this in Access using Datasheet form and it was simple. It is like continious form. Does VB6 have something like Datasheet or it needs to be created and how?
I've checked postings, but couldn't find something that fits my needs.
Thanks!
Creating Data Reports From Join Of Three Tables
hello all,
please suggest how to create data report where data is bein fetched from join of 3 tables.
As i am creating reports for the first time in , i m facing a lo of trouble...with data environment, i dont know how to mentio data source in terms of " app.path" and hence the defaul c:/desktop/mydatabase.mdb is being accepted as the path by th connection.
Moreover can anyone plz suggest if i cn use PL/SQL 4 data repor creation...as the table/sql statement doesn't serve m purpose...or can i use the stored procedure option to call procedure to display...n if so how??
i am pretty hopeful of ur cooperation n suggestions...
thanking u in advance....
Tanu
Access Query(how To Join Three Tables) (Unresolved)
It will check cat1,cat2 and cardno in each table and get it's appropriate valuse. If in any table it has more than one value in same day than it will give the sum of that day.
In short I want to join three tables based on three parameters and group by datewise. It will check for cat1,cat2 and cardno in all three tables and display values.
I have attached complete datbase with original data and required data with table structure.
I am using three parameter cat1,cat2 and card no.
Hope u can understand it.
Eagerly waiting.
with regards
pratik mehta
EDIT Shandy:Add Unresolved To Subject
Edited by - Shandy on 9/9/2003 10:44:31 AM
Dear Dinz, Join Two Tables In A Datareport Still Problemistics Please.
Thanks a lot ! But a problem for DataReport.
please inform me where to add this Query.
what you mean that 'eg' and 'Master.Party' in your answer ?
how we can set the text box of the datafield of datareport for 'eg' ?
And how Datasource of Datareport = recordset ? which recordset ?
please inform in some details as this kind of report (combining two tables in one report) is new for me.
Please Help Crystal Report 10 Logon Failed When SQL Three Tables Join
I am working on VB6 for crystal reports 10 Develop Edition , the database is MYSQL. i need three tables to create a report .it works fine in local LAN (any of the computers,windows),but when connecting by remote access through VPN ,an weird error comes out :Logon Failed Details:01000:[Microsoft][ODBC SQL Sever Driver][DBNETLIB]connectionopen(connect()).
but if i use only one table to create it no matter in LAN or VPN works perfectly .after googling.knowing that each table need User ID ,Password,DSN to login .but it didn't work for me .could it be possible that the SQL string is not so appropriate ?
anyone who can give me some hints or codes i would be greatly appreciate. thanks a lot in advance
here is my code:
Option Explicit
Const CONSTR = "Provider=SQLOLEDB;data Source=***;Initial Catalog=northwind; " & _
"User Id=***;Password=***"
Dim strSQL As String
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim crystal As CRAXDRT.Application 'LOADS REPORT FROM FILE
Dim report As CRAXDRT.report 'HOLDS REPORT
Dim CRXDATABASETABLE As CRAXDRT.DatabaseTable
Private Sub Form_Load()
'THIS CODE IS USED TO DISPLAY A CRYSTAL REPORTS 9 REPORT USING
'DATA PULLED FROM A MYSQL DATABASE USING ADO AND VISUAL BASIC 6
'YOU NEED REFERENCES TO: Microsoft ActiveX Data Objects 2.7 Library
' Crystal Reports 9 ActiveX Designer Run Time Library
'YOU NEED THE COMPONENT: Crystal Reports Viewer Control 9
'ADD THE CRVIEWER91 COMPONENT TO YOUR FORM AND NAME IT CRViewer.
CRViewer.EnableExportButton = True
CRViewer.DisplayBorder = False 'MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False 'THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False 'REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False 'ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
Dim strsql1 As String
Me.WindowState = 2
strsql1 = "select customer.country,customer.name,supplier.country,supplier.name,order.id,order.product from customer,supplier,order where customer.id='" & Trim(form.id1.Text) & "' and supplier.id='" & Trim(form.id2.Text) & "' and order.id='" & Trim(form.id3.Text) & "'"
If conn.State = adStateClosed Then _
conn.Open CONSTR
If rs.State = adStateOpen Then rs.Close
Set rs.ActiveConnection = conn
rs.Open strsql1, conn, adOpenKeyset, adLockOptimistic
Set crystal = New CRAXDRT.Application 'MANAGES REPORTS
Set report = crystal.OpenReport(App.Path & "order Report.rpt") 'OPEN OUR REPORT
For Each CRXDATABASETABLE In report.Database.Tables
CRXDATABASETABLE.ConnectionProperties.DeleteAll
CRXDATABASETABLE.ConnectionProperties.Add "DSN", "***"
CRXDATABASETABLE.ConnectionProperties.Add "user id", "***"
CRXDATABASETABLE.ConnectionProperties.Add "password", "***"
Next CRXDATABASETABLE
report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET
report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET
CRViewer.ReportSource = report 'LINK VIEWER TO REPORT
CRViewer.ViewReport 'SHOW REPORT
Do While CRViewer.IsBusy 'ZOOM METHOD DOES NOT WORK WHILE
DoEvents 'REPORT IS LOADING, SO WE MUST PAUSE
Loop 'WHILE REPORT LOADS.
CRViewer.Zoom 100
rs.Close 'ALL BELOW HERE IS CLEANUP
conn.Close
Set conn = Nothing
Set crystal = Nothing
Set report = Nothing
End Sub
Private Sub Form_Resize() 'MAKE SURE REPORT FILLS FORM
CRViewer.Top = 0 'WHEN FORM IS RESIZED
CRViewer.Left = 0
CRViewer.Height = ScaleHeight
CRViewer.Width = ScaleWidth
Edited by - worldapart on 5/17/2006 1:55:04 AM
Importing Multiple Sheets From A Excel Spreadsheet Into Multiple Tables
hello all,
I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with.
In a workbook at the start of the year (january) i will have 4 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named xyz). then sheet 4 through the next sheets till sheet 12 (named balance1, balance2, abalance3...so on till balance12) should all go into one table 'table4' (named balance) and all these sheets should keep appending starting from 1 then 2 all the way to 12.
I am able to put one sheet into one table and below is the code....i need help with the other part of my requirement
Code: ( text )
Dim cn As ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim rsAccess As New ADODB.Recordset
' Open Excel Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Test.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
' Open Access Connection
Set cnAccess = New ADODB.Connection
With cnAccess
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Documents and SettingskrishnamDesktopIntercompany Consolidation.mdb;"
.Open
End With
' Load ADO Recordset with Excel Sheet1Data
oRs.Open "Select * from [abc$]", cn, adOpenStatic
MsgBox oRs.RecordCount
' Load ADO Recordset with Access Data
rsAccess.Open "select * from tbl_abc", cnAccess, adOpenStatic, adLockOptimistic
MsgBox rsAccess.RecordCount
'Synchronize Recordsets and Batch Update
Do While Not (oRs.EOF)
rsAccess.AddNew
For i = 0 To 11 -----11 columns in table 1
rsAccess.Fields(i).Value = oRs.Fields(i).Value
Next
rsAccess.Update
oRs.MoveNext
Loop
End Sub
please help me so that i can move forward...
Multiple JOIN In SQL Using VB6
Hi there,
Is it possible to use join multiple tables in a querry, given the following.
I am writing an attendance program for a small unit within a college. People that enter the unit fall into one of three categories: supervisors, tutors, and students. With different personal info collected on each group, I have made three seperate tables (Supervisors, Tutors, and Students) to store their personal details.
There is a forth table, Usage, where data is stored about the individuals entering the unit. Everyone signs in using a K number (a generic ID system used by the colloege). I would like to make one search that will determine which of the three tables above (Supervisors, Tutors, and Students) contains the K number being inputed (ie, is the person entering the center a supervisor, tutor, or student). Also, if it is in none of the three tables, then it identifies the person as being a first timer.
The code I am using is:
Code:
Public Sub OpenTable(Criteria As String)
'action taken when attempt to search the database fails
On Error GoTo OpenTableError
'connect to database if required
If Db Is Nothing Then Set Db = OpenDatabase(DbFilename)
'assign recordset to dbtbl
Set DbTbl = Db.OpenRecordset(Criteria)
'if no errors, exit sub
Exit Sub
OpenTableError:
'notify user of error
MsgBox "Error connecting to the database, using the following query:" & vbNewLine & vbNewLine & Criteria
End Sub
KNumber = "K00000001"
OpenTable "
SELECT
Supervisor.KNumber as supKN, Tutors.KNumber as tutKN, Students.KNumber as stuKN
FROM
Usage RIGHT JOIN Supervisors, Tutors, Students
WHERE
(Supervisors.KNumber = Usage.KNumber OR Tutors.KNumber = Usage.KNumber OR Students.KNumber = Usage.KNumber)
AND
(Supervisors.KNumber = '" & KNumber & "' OR Tutors.KNumber = '" & KNumber & "' OR Students.KNumber = '" & KNumber & "')"
Thanks for any help.
Multiple INNER JOIN
Can anyone give me an example how to do MULTIPLE INNER JOIN. What i want to do is link three tables, example
Select table1.no, table1.name, Table2.no, table2.CategoryID, table2.description, table3.category From Table1 INNER JOIN Table2 ON table1.no=table2.no INNER JOIN table3 ON Table2.CategoryID=Table3.CategoryID
Thanks for your reply!
Recalling Data From Multiple Fields In Multiple Tables
I am working on a fairly large project for recording client information into a db which has quite a few fields which are spread out among several tables. The code is already written that saves the data to it's appropriate fields in their appropriate tables.In this particular case, under the front tab of the form is the head person's personal information. On the third tab is information pertaining to the head person's dependents. The code I have written saves the head person's data as well as the data pertaining to that person'd dependents' to a table named [Person]. A few lines lower, those persons' IDs are also entered into an additional table called [Relation], in which are only 2 fields: Relation_Parent_ID and Relation _Child_ID. In another control's code, I recall the data of a given person's ID back into the form from which it was recorded. But how do I code it so that even the children's data is recalled when the parent's data is re-inserted into the form? The code to recall every piece of data that is saved for that person's ID, except for the children's data, is already written and functional. How can I cause the children's data, also, to be recalled when the parent's data is recalled?
Thank you very much for all your help
Happieman
a.k.a. Bill
Edited by - Happieman on 6/11/2005 10:18:40 AM
Sql Multiple Inner Join And Not Null
Im using:
SELECT dbo.tblLOOKUPSalesRep.RepName, dbo.[tblLOOKUP-States].StateName, dbo.[tblLOOKUP-States].ID
FROM dbo.tblIRC_QS_Files INNER JOIN
dbo.tblLOOKUPSalesRep ON dbo.tblIRC_QS_Files.TechInt = dbo.tblLOOKUPSalesRep.RepInit INNER JOIN
dbo.tblIRC_CallLog ON dbo.tblLOOKUPSalesRep.RepInit = dbo.tblIRC_CallLog.TechInt INNER JOIN
dbo.[tblLOOKUP-States] ON LEFT(dbo.tblIRC_CallLog.MemNo, 2) = dbo.[tblLOOKUP-States].ID
WHERE (dbo.[tblLOOKUP-States].ID IN ('06', '28', '29', '31', '90')) AND (dbo.tblIRC_QS_Files.ExpDate <> NULL)
Unfortunately it doesn't work unless I take the not null expdate oout of the equation?
Any Help would be appreciated!!!
Multiple LEFT JOIN Help Please
Hi Everyone
I am using an ADODB Connection to query tables in access.
Have to use left joins as fields may be empty in the right hand table.
Just cant seem to get the syntax correct for mutiple joins (got it working for one join though)
Some help would be great, as I dont want to waste vast amounts of time on this.
My code is below:
Code: locQuerySQL = "SELECT * FROM Solicitor " & _
"LEFT JOIN Addresses ON Solicitor.solicitorAddressID = Addresses.addressID " & _
"LEFT JOIN TelephoneNumbers ON Solicitor.solicitorPhoneID = TelephoneNumbers.telephoneID " & _
"WHERE solicitorChildID = " & locChildID & ""
'query the database
DBRecordset.Open locQuerySQL, DBConnection
thanks in advance for any help
cheers
Daniel
Multiple .MDF Files With 1 Table OR 1 With Multiple Tables?
In the past, I justed just one .MDB (access) database file with multiple tables, but have run across several commercial programs that have multiple .mdb files with one table. Those tables are then all linked.
Is there reason to go one way or the other and why? If using multiple .mdb files, how do you create the links between the tables in different .mdb files?
--Gary
Updating Multiple Tables With Multiple Recordsets
Hi,
I am trying to update two tables at once, based on the same text box inputs I seem to come close but it will not update the second table. Here is my code:
Option Explicit
Dim Rs As Recordset
Dim Rs2 As Recordset
Dim CN, CN2 As Connection
Private Sub cmdBack_Click()
If Not Rs.BOF Then
Rs.MovePrevious
End If
If Rs.BOF And Rs.RecordCount > 0 Then
Rs.MoveFirst
End If
End Sub
Private Sub cmdNext_Click()
If Not Rs.EOF Then
Rs.MoveNext
End If
End Sub
Private Sub cmdRefresh_Click()
Rs.Requery
End Sub
Private Sub cmdSearch_Click()
Rs.MoveFirst
Rs.Find "LastName = '" & txtSearch & "'"
If Rs.BOF Or Rs.EOF Then
If MsgBox("The record you entered was not found, Do you want enter another name ?", vbQuestion + vbYesNo, "ID Check") = vbYes Then
txtSearch = ""
txtSearch.SetFocus
Exit Sub
Else
Rs.MoveFirst
txtSearch = ""
Exit Sub
End If
Else
'Fills in the data with the fields based on the found data
txtLastName = Rs.Fields("LastName")
txtAmount1 = Rs.Fields("Amount1")
txtAmount2 = Rs.Fields("Amount2")
End If
End Sub
Private Sub Command1_Click()
Dim ST As String
Rs.AddNew ' Add a new record
Rs.Fields("LastName") = txtLastName
Rs.Fields("Amount1") = txtAmount1
Rs.Fields("Amount2") = txtAmount2
Rs.Update
'CurrentDB.Execute ("Update tblName2 Set Last_Name = '" & txtLastName & "',Amounta = '" & txtAmount1 & "',Amountb = '" & txtAmount2 & "'")
Rs2.AddNew ' Add a new record
Rs2.Fields("Last_Name") = txtLastName
Rs2.Fields("Amounta") = txtAmount1
Rs2.Fields("Amountb") = txtAmount2
Rs2.Update
End Sub
Private Sub Form_Load()
Set CN = New ADODB.Connection
CN.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"
Set Rs = New Recordset
Rs.Open "Select LastName, Amount1, Amount2 from tblLastNames", _
CN, adOpenKeyset, adLockOptimistic
Set CN2 = New ADODB.Connection
CN2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"
Set Rs2 = New Recordset
Rs2.Open "Select Last_Name, Amounta, Amountb from tblName2", _
CN2, adOpenKeyset, adLockOptimistic
Set txtLastName.DataSource = Rs
Set txtAmount1.DataSource = Rs
Set txtAmount2.DataSource = Rs
txtLastName.DataField = "LastName"
txtAmount1.DataField = "Amount1"
txtAmount2.DataField = "Amount2"
End Sub
I think it has to do with the adOpenKeyset, adLockOptimistic at the end of the connection string, but if it is this it will not let me take it out or chage it. I also tryed moving things around and that did not woprk.
If any one has any ideas, of waht I am doing wrong, I would really appreciate it
Thank You
Join Recordsets From Multiple DBase Connections
I am importing data from a dBase table into Excel with the following VBA code:
dim db as DAO.Database
dim rs as DAO.Recordset
dim dbPath as String
dim strSQL as String
dbPath = "i:common"
strSQL = "select field1, field2 from table1;"
Set db = OpenDatabase(dbPath, False, False, "dBase IV")
Set rs = db.OpenRecordset(strSQL)
Sheets("sheet1").Range("a1").CopyFromRecordset rs
This works great, except now I have a situation where I need to joing this query to a dBase table in another directory (requiring separate database connections).
Any suggestions???
I thought about just creating a separate connection and query, importing the data into an temporary Excel sheet, and working with the data there, but there are too many records.
Thanks,
Mark
Multiple Left Outer Join With Where Clause
This seems simple and basic, but I can't find samples on any VB books or tutorial.
Table A has fields a1, a2, a3
Table B has fields b1, b2, b3
Table C has fields c1, c2, c3
What's wrong with
Select A.*, B.*, c.* from A where A.a3 = 'Y' ((left outer join B on A.a1 = B.b1) left outer join C on A.a1 = C.c1 and A.a2 = C.c2) order by A.a1, A.a2
How should I put the brackets?
Thanks.
SQL With Multiple Tables...
Hi...I was just wondering...
I have three tables...Agencies, ProductsOrdered, and Orders.
I have 5 listboxes representing 'Monday' to 'Friday'...what I need to do is go into the database and retrieve the Agencies...plop it into these listboxes according to the day the Agencies place and order which is in the ProductsOrdered table. My question is...do I have to go into the ProductsOrdered table, get the OrderID for orders place through Monday to Friday, open up the Orders table, match the OrderID from the ProductsOrdered table to the OrderID from the Orders table, get the AgenyID from this table and open up the Agencies table and finally get the Agency and populate it into the listboxes!!! This is the long way of doing it, can I just SQL with InnerJoin to do all of this? I hope I'd explained this well enough!!!
THANKS!!!
|
TRACKER
