F1 Please - A Simple Query Challenges me 1

[Deadline]

Hi,

I have 2 tables like this :

*sample datas only*

Table A

EmpID TripNo StateCode
00322  1000   VA
00322  1000   GA
00322  1000   CA
00123  1001   CA
00123  1001   VA
00123  1001   GA

Table B 
StateCode     StateDesc
VA            Virginia
CA            California
GA            Georgia

I need a query that'll fetch ALL the statedesc as a SINGLE string from Table B for each (tripnumber+EmpId).

*answers in VB context will be very helpful*

I know you can help me..

Thank you in advance.

RR
:-|

 

[Deadline]

*Update*

Actually, the question that needs to be answered in my case is "fetch all the places and employee id, where each employee is visiting";
*vb context*


Thanks in advance.

RR

 

[ghubbell]

RR,
Give this a whirl in a new module...Find/Replace the table names for yours. Hope it's what you want!

Sub SolveThis()
On Error GoTo ErrST
Dim SQL As String, SQL2 As String, Rs As Recordset, Rs2 As Recordset, Db As Database, strTheString As String, strEmpID As String, intTripNo As Integer
Set Db = CurrentDb()
SQL = "SELECT DISTINCT YourFirstTable.TripNo, YourFirstTable.EmpId FROM YourFirstTable ORDER BY YourFirstTable.TripNo"
Set Rs = Db.OpenRecordset(SQL, dbOpenSnapshot)
If Rs.EOF Then
Rs.Close
Exit Sub
End If
Do Until Rs.EOF
SQL2 = "SELECT YourFirstTable.*, YourSecondTable.StateDesc FROM YourFirstTable LEFT JOIN YourSecondTable ON YourFirstTable.StateCode = YourSecondTable.StateCode WHERE (((YourFirstTable.EmpId) = '" & Rs!EmpId & "') And ((YourFirstTable.TripNo) = " & Rs!TripNo & &quot) ORDER BY YourFirstTable.TripNo"
Set Rs2 = Db.OpenRecordset(SQL2, dbOpenSnapshot)
If Rs2.RecordCount = 0 Then
Rs2.Close
GoTo ByPass
End If
strEmpID = Rs!EmpId
intTripNo = Rs!TripNo
If Not IsNull(Rs2!StateDesc) Then strTheString = Rs2!StateDesc
If Rs2.EOF Then GoTo ByPass
Rs2.MoveNext
Do Until Rs2.EOF
strTheString = strTheString & ", " & Rs2!StateDesc
Rs2.MoveNext
Loop
Debug.Print strEmpID & " " & intTripNo & " " & strTheString
ByPass:
strEmpID = Empty
intTripNo = Empty
strTheString = Empty
Rs.MoveNext
Loop
ExitST:
Exit Sub

ErrST:
MsgBox Err.Number & " " & Err.Description, vbInformation, "Solve this! error.... "
Resume ExitST
End Sub Gord
ghubbell@total.net

 

[Deadline]

Hi Gord,
I am indebted you. You almost saved my job. Thank you very much. That is what exactly I wanted.

But could you please explain me the second query(SQL2)in plain English ?


Thank you very much again.

RR

 

[ghubbell]

Hi RR,
Your very welcome and thank you too.

The second query in plain English would be &quot;Give me all of the records from the first table, and the StateDesc from the second where the StateDesc in the first equals the StateDesc in the second, and I don't care if it is in the second table, just make sure I see all from the first, but the records I want to see from the first must have an employeeID that equals <Something> And a TripNo that equals <something>.

Geeze I read this back and I confuse myself!

You can copy your SQL2 and open a new query in SQL view and paste it in. Don't try to run it but flip it to QBE (design) view and change the '&quot; & Rs!... stuff to real numbers (or nothing to start) and you should see its structure.

The key is in the loop, Rs & Rs2 are from the same tables so I just use &quot;Copy1&quot; to compare against &quot;Copy2&quot;.

Real glad to here it goes, and tell your boss that Gord says to be nice. This isn't rocket science but it isn't exactly general use programming either!

Gord
ghubbell@total.net