-
September 23rd, 2003, 03:12 PM
#1
SQL Query problem
I have an Access data base with Three tables
'
Table 1 is Customer Information
Table 2 is Project Information
Table 3 is Task information
Table 3 contains pointers into Table1 and 2
Table 1 and 2 contain, among other things, unique IDs and Names like "CustomerName, ProjectName.
My problem is how to display the contents of Table 3 and replacing table 1 and 2 pointers with the Name Field from Table 1 and 2.
The following Query will successfully extract the Customer name from Table 1 but I can not figure out how to extract ProjectName from Table 2 at the same time.
'
rs1.Open "Select * from tasklist,ClientList " & _
"where tasklist.ClientID = ClientList.ClientID " & _
"Order by mstrdate,strttime ", _
Conn1, adOpenKeyset, adLockOptimistic
'
Above query works as expected but does not allow me to display "ProjectName".
'
The following simple change of adding Projects to the first line causes a inordinate number of bad records to be selected
The first query gives me 460 records the second gives me on the order of 400,000.
'
rs1.Open "Select * from tasklist,ClientList,Projects " & _
"where tasklist.ClientID = ClientList.ClientID " & _
"Order by mstrdate,strttime ", _
Conn1, adOpenKeyset, adLockOptimistic
'
The data base only has 460 records
'
Can anyone help me to be able to display the ProjectName from Table 2?
I have tried UNION of two Select statements to no avail (Various error messages or incorrect output)
'
Thanks for any help you can provide
John G.
-
September 23rd, 2003, 03:39 PM
#2
I do this all the time. 5 minutes after the Post I figured it out.
'
For what its worth, here is the solution.
'
rs1.Open _
"Select * from tasklist,Projects,ClientList " & _
"where tasklist.projectID = Projects.projectID " & _
"and tasklist.ClientID = ClientList.ClientID " & _
"Order by mstrdate,strttime ", _
Conn1, adOpenKeyset, adLockOptimistic
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|