Open Database, query then make a string?

[sempuritoza]

i am very unexperienced with access so even for small things like this i need some help. i have a table(A) which has a one to many relationship with table (B) and i need to get all the fields from table B that correspond in table A.
i know i need to use the modeule builder but i dont know how to connect to the db and then run a query and then put that into a loop and continously concatinate the string to itself plus the new value(this is the basic procedure i am geussing cause this is how i would do it in PHP)

what is the syntax that i have to put together to

1. connect to the database
2.run a query that selects all the fields in table B which are relevant to table A
3. start a loop in which i can concatinate all the fields
4. reutrn the string

any help will be greatley appreciated, i feel like i am computer illetrate at the moment and this aint a nice feeling

thanx

 

[Schof]

What database (Eg. Access, Oracle, etc) do the tables reside in?

Do you want all fields in A and B or do you only want certain ones?

Are you looking for certain records in A (and B) or you want all records?

Why concatinate the fields? Without worrying about implementation, what is it you are trying to do with the records?

 

[sempuritoza]

the database type is microsoft access

i want all the fields in table B that are tied to the primary key in table A but in access when u so this it will return a separate record for each field in table B that is equal to the field in table A

i am looking for specific records in table A but this always changes

why concatenate the fields, as i said i dont want a separate record for evert entry in table B i cause all the other values are still the same it is only the one specific field that changes, therefor i just want to return all the fields once and concatenate the column in table B that has multiple instance for the specific instance in table A.

i hope this is a bit more clear now, if it isnt then if it is okay withu can i mail u an ERD of the two tables and give u a more detailed explanation.

i was also told that crosstab query might be the solution but i havnt ventured that far yet

thanx for ur time and help, hope to hear from u
lata
E

 

[VBslammer]

I'm also trying to figure out your logic, but here's something to open the discussion:

Function ConcatStringValues() As String
  Dim strSQL As String
  Dim strReturn As String
  Dim rst As DAO.Recordset
  
  strSQL = "SELECT TableB.StringVal FROM TableA INNER JOIN TableB ON TableA.PK = TableB.FK;"
  
  Set rst = CurrentDb.OpenRecordset(strSQL)
  
  With rst
    While Not .EOF
      strReturn = strReturn & .Fields("StringVal") & ";"
      .MoveNext
    Wend
    .Close
  End With
  
  ConcatStringValues = strReturn
  
End Function

VBSlammer

Unemployed in Houston, Texas

 

[Schof]

Here are some changes I think need to be made to the example in order to solve your problem, which (I think) is:

1) You want to connect to an external MS-Access DB.
2) You want all the fields from Table A
3) You only want 1 (or more, it doesn't matter) field from Table B resulting from the join with Table A.
4) You want to concatenate all of the fields in Table B resulting from 3).


Function ConcatStringValues() As String
On Error GoTo Err_ConcatStringValues

Dim strSQL As String
Dim strReturn As String
Dim rstTableA As DAO.Recordset
Dim rstTableB As DAO.Recordset
Dim dbs as database
Dim strWhereClause As String

Set dbs = OpenDatabase("C:\xyz\myDB.mdb&quot

strSQL = "Select * from TableA "
strWhereClause = "Where ..."

Set rst = dbs.OpenRecordset(strSQL & strWhereClause)

' Do whatever you want with the resulting record

strSQL = "SELECT TableB.StringVal FROM TableA INNER JOIN TableB ON TableA.PK = TableB.FK "

Set rst2 = dbs.OpenRecordset(strSQL & strWhereClause)

With rst2
While Not .EOF
strReturn = strReturn & .Fields("StringVal&quot & ";"
.MoveNext
Wend
.Close
End With

ConcatStringValues = strReturn

Exit_ConcatStringValues:
rst.Close
rst2.Close
dbs.Close
Exit Function

Err_ConcatStringValues:
MsgBox "Error " & Err & ": " & Err.Description
ConcatStringValues = ""
Resume Exit_ConcatStringValues
End Function

 

[sempuritoza]

thanx to all u guys for the help, i have learnt more than i came to find out and each of u have helped me considerably and the information i have gathered here i am sure i will be able to re use in the near future everything seems to be wortking and if i get stuck again i know where to come...

ciao