Help with SQL command for ADO

Hello everyone, I am trying to write a complex SQL statement for a ADO shape driven database.

I have 3 tables, as follows.

Table 1 - Payroll Info

Table 2 - Client Info

Table 3 - Employee Info

What I need is, in Table 1 it has certain employees that are assigned to certain clients. Within the row of data it has Employee Number, and Client Number.

I wish to open a recordset of data, that takes the Payroll Info Row, and join to Table 2, by Client Number (Social Security) because that is where the name of the clients are. Then join to table 3 by Employee Number (Social Security) to get the Employee Name. For example

Table 1 [Payroll Info]
-----------------------------------------------
ID | ClientNumber | Employee Number | Hours
-----------------------------------------------
1 | 123456789 | 234234235 | 45.50
2 | 123456777 | 542453452 | 23.00
3 | 123458888 | 542453452 | 32.00


Table 2 [Client Info]
-----------------------------------------------
ID | ClientNumber | Cliet Name
-----------------------------------------------
1 | 123456789 | Max Lopez
2 | 123456777 | Jack Smith
3 | 123458888 | Sally Red


Table 3 [Employee Info]
-----------------------------------------------
ID | Employee Number | Employee Name
-----------------------------------------------
1 | 234234235 | Micah Smith
2 | 542453452 | David Gomez


[ REPORT ]

Employee Number | Employee Name | Client Number | Client Name | Hours
-----------------------------------------------------------------------
234234235 | Micah Smith | 123456789 | Max Lopez |45.50
542453452 | David Gomez | 123456777 | Jack Smith |23.00
542453452 | David Gomez | 123458888 | Sally Red |32.00

[ REPORT ]

This is the SQL Code I had placed in the command, but it did not seem to want to work. Any help would be greatly appreciated, it has been so long since I used advanced SQL commands.

[code]
VarSQL = "SHAPE " _
& "{SELECT [" & DeptNum & "PR].*, [" & DeptNum & "EM].SSN, [" & DeptNum & "EM].FirstN, " _
& "[" & DeptNum & "EM].LastN, [BM" & DeptNum & BillDate & "].ClientNumber, " _
& "[BM" & DeptNum & BillDate & "].FirstName, [BM" & DeptNum & BillDate & "].LastName " _
& "[BM" & DeptNum & BillDate & "].AppHours, [BM" & DeptNum & BillDate & "].WrkHours " _
& "FROM [" & DeptNum & "PR] " _
& "INNER JOIN [" & DeptNum & "EM] " _
& "ON [" & DeptNum & "PR].ProviderNumber = [" & DeptNum & "EM].SSN " _
& "INNER JOIN [BM" & DeptNum & BillDate & "] " _
& "ON [" & DeptNum & "PR].ClientNumber = [BM" & DeptNum & BillDate & "].ClientNumber " _
& "} AS TotalEdits " _
& "COMPUTE TotalEdits BY BatchNumber"
[/code]

Thank you.
The Darthmoob
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories