Inner join

radek225

Registered User.
Local time
Today, 15:23
Joined
Apr 4, 2013
Messages
307
I have some code. I want to copy all columns from
Code:
tabela_zrodlowa = "tblGoraZlecenia"
but only one column which is called "Id_rodzajpracy" from
Code:
tabela_zdrodlooffset = "tblOffset"

How could I modify red instruction to do inner join?

Code:
Dim rec_GoraZlecenia As DAO.Recordset
  Dim rec_GoraZlecenia2 As DAO.Recordset
  Dim rec_GoraZlecenia3 As DAO.Recordset
  Dim tabela_docelowa As String
  Dim tabela_zrodlowa As String
  Dim tabela_zdrodlooffset As String
  Dim s As String
  
  tabela_zrodlowa = "tblGoraZlecenia"
  tabela_docelowa = "tblGoraZleceniaRobocze"
  tabela_zdrodlooffset = "tblOffset"
  Set rec_GoraZlecenia = dbs.OpenRecordset(tabela_docelowa, dbOpenDynaset)
  [COLOR="Red"]Set rec_GoraZlecenia2 = dbs.OpenRecordset("SELECT * FROM [" & tabela_zrodlowa & "]  WHERE ID_Zlecenia ='" & nrZlec & "'", dbOpenDynaset)[/COLOR]
  
  KopiujRekord rec_GoraZlecenia, rec_GoraZlecenia2, False, id_nr
 
JOIN only lets you merge two tables, and display them as such that have a common ID between the two.. The columns are not permanently copied across.. Every time you JOIN them they will display the result as one table view.. To perform such operation, you need to identify the common ID.. Then it will be something like..
Code:
SELECT [COLOR=Blue]tableA.fieldName1[/COLOR], [COLOR=Blue]tableB.fieldName2[/COLOR], [COLOR=Red]tableB.fieldName1[/COLOR]
FROM [COLOR=Blue]tableA[/COLOR] INNER JOIN [COLOR=Red]tableB[/COLOR] ON [COLOR=Blue]tableA.commonID[/COLOR] = [COLOR=Red]tableB.commonID[/COLOR];
 
I know how the definition of a join looks like. There are many types of.
That's what I past, it's a part of code which copy and past some information from one table to another. Now I have to do, not from one but two tables. I have to modify the code which has red color. I should write in inner join instruction all of columns from "tabela zrodlowa"? or maybe there is some instruction like "take all from 'tabela zrodlowa' but only one column 'id_rodzajpracy' from 'tabelza_zrodlooffset'"?
 
You can do like..
Code:
SELECT [COLOR=Red]tableA.[B]*[/B][/COLOR], [COLOR=Blue]tableB.onlyOneColumn[/COLOR]
FROM [COLOR=Red]tableA[/COLOR] INNER JOIN [COLOR=Blue]tableB[/COLOR] ON [COLOR=Red]tableA.idField[/COLOR] = [COLOR=Blue]tableB.commonID[/COLOR];
 
Why not try building the query using the QBE? You are using unnecessary variables in your code so the QBE should work just fine to create the query that joins the two tables and selects the columns you need from each. Use the querydef in code rather than the query string. If you need to modify the querystring further, switch the QBE to SQL view and copy and pase the SQL string into your module.
 
I know how the definition of a join looks like. There are many types of.
That's what I past, it's a part of code which copy and past some information from one table to another. Now I have to do, not from one but two tables. I have to modify the code which has red color. I should write in inner join instruction all of columns from "tabela zrodlowa"? or maybe there is some instruction like "take all from 'tabela zrodlowa' but only one column 'id_rodzajpracy' from 'tabelza_zrodlooffset'"?

Are you trying to build a dynamic Query based on user input? In that case, the QBE Solution suggested by Pat Hartman (with Parameters) would be an good option. You can also make the QBE version, and use the SQL Code as a basis for the VBA Code that you are trying to create.

-- Rookie
 

Users who are viewing this thread

Back
Top Bottom