Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Works Fine, accept when used by a report (2000)

    I have a query that runs fine on it's own, but when I use it as the source of a report, I get the following error:

    The specified field '[SC1 - TaskOperations].[Start Date]' could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079)

    Here is my query:

    SELECT qry411.Item, qry411.[Item Description], qry411.[WorkOrder ID], qry411.[Scheduled Resource Name], qry411.Status, qry411.[Successor Opn Number], [SC1-TaskOperations].[Start Date], [SC1-TaskOperations].[Start Time]
    FROM qry411 INNER JOIN [SC1-TaskOperations] ON (qry411.[Successor Opn Number] = [SC1-TaskOperations].[Opn Number]) AND (qry411.[WorkOrder ID] = [SC1-TaskOperations].[WorkOrder ID])
    WHERE ((([SC1-TaskOperations].[Start Date])<DateAdd("d",4,Now())))
    ORDER BY [SC1-TaskOperations].[Start Date];

    I think that my Select statement is fully qualified, so how come I get this error? Why when used by a report, but not on it's own?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Works Fine, accept when used by a report (2000)

    On which table(s) is the query qry411 based?

  3. #3
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Works Fine, accept when used by a report (2000)

    [SC1 - TaskOperations] and [SC1 - Tasks]:

    SELECT [SC1-TaskOperations].Item, [SC1-Tasks].[Item Description], [SC1-TaskOperations].[WorkOrder ID], [SC1-TaskOperations].[Opn Number], [SC1-TaskOperations].[Scheduled Resource Name], [SC1-TaskOperations].Status, [SC1-TaskOperations].[Start Date], [SC1-TaskOperations].[Start Time], [SC1-TaskOperations].[Finish Date], [SC1-TaskOperations].[Finish Time], [SC1-TaskOperations].[Successor Opn Number]
    FROM [SC1-Tasks] INNER JOIN [SC1-TaskOperations] ON [SC1-Tasks].[WorkOrder ID] = [SC1-TaskOperations].[WorkOrder ID]
    WHERE ((([SC1-TaskOperations].[Scheduled Resource Name]) Like "411*") AND (([SC1-TaskOperations].[Successor Opn Number])<>"None"))
    ORDER BY [SC1-TaskOperations].[Finish Date];


    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Works Fine, accept when used by a report (2000)

    Well, you see, [SC1-TaskOperations].[Start Date] and [SC1-TaskOperations].[Start Time] are fields in [SC1-TaskOperations] and in qry411, originally also from [SC1-TaskOperations]. In fact, I don't see why you need to introduce [SC1-TaskOperations] in your query again, since qry411 is already based on it. Since you use inner joins, you would get the same results if you omit [SC1-TaskOperations] from your query and get the Start Date and Start Time from qry411, and use those for the criteria and sort order.

    I can't really explain why your query works when opened directly but not as record source of a report, but it must have to do with the above.

  5. #5
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Works Fine, accept when used by a report (2000)

    What I am trying to do is this: Get the start time and Date of the Next Operation Number.
    In the [SC1 - TaskOperations] table are records for Work Orders, there is one entery for each operation so a Workorder number may appear several times. The field [Opn Number] Holds the Current Operation Number, and [Successor Opn Number] holds the the Next Operation Number.
    1st, I sort for all enteries using the "411" resorce in the [Scheduled Resource Name] field in query qry411.
    With the next query, I am trying to get the Start Time and Date of this Next Operation Number by linking qry411.[Successor Opn Number] back to [SC1 - TaskOperations].[Opn Number]

    Is there another way to do this within a single query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •