CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2000
    Location
    South Carolina,USA
    Posts
    2,210

    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.

  2. #2
    Join Date
    Apr 2000
    Location
    South Carolina,USA
    Posts
    2,210
    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
  •  





Click Here to Expand Forum to Full Width

Featured