Results 1 to 4 of 4
  1. #1
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78

    access can not find the input table or query


    i would like to filter a list and then make a another filter on the filtered list with subquery but i got error "access can not find the input table or query list1"

    my query is like below :

    Code:
    SELECT
    list1.[Project No],list1.[Document Type],list1.Zone,list1.Discipline,list1.[PO NO],list1.[Vendor Name],list1.VendorDesc,list1.[Tag No],
    list1.OwnerDocumentNo,list1.[DOCUMENT / DRAWING TITLE],list1.[VEDOR TRANSMITTAL NO],list1.Rev,list1.[Doc STATUS],
    list1.[AC CODE],list1.[MC AC Status],list1.[AC Desc],list1.Transmittal_to_Site,list1.Transmittal_to_Site_date,list1.HyperDoc
    
    FROM
    
    (SELECT [tblDocument-VP].[Project No], [tblDocument-VP].[Document Type], [tblDocument-VP].Zone, [tblDocument-VP].Discipline, [tblDocument-VP].[PO NO], [tblDocument-VP].[Vendor Name], tblVendorName.VendorDesc, [tblDocument-VP].[Tag No], [tblDocument-VP].OwnerDocumentNo, [tblDocument-VP].[DOCUMENT / DRAWING TITLE], [tbltransmittals-VP].[VEDOR TRANSMITTAL NO], [tbltransmittals-VP].Rev, [tbltransmittals-VP].[Doc STATUS], [tbltransmittals-VP].[AC CODE], [tbltransmittals-VP].[MC AC Status], tblAC_Code.[AC Desc], [tbltransmittals-VP].Transmittal_to_Site, [tbltransmittals-VP].Transmittal_to_Site_date, [tbltransmittals-VP].HyperDoc
    FROM (tblVendorName RIGHT JOIN [tblDocument-VP] ON tblVendorName.[VENDOR NAME] = [tblDocument-VP].[Vendor Name]) LEFT JOIN (tblAC_Code RIGHT JOIN [tbltransmittals-VP] ON tblAC_Code.[AC Code] = [tbltransmittals-VP].[AC CODE]) ON [tblDocument-VP].OwnerDocumentNo = [tbltransmittals-VP].OwnerDocumentNo
    WHERE [tbltransmittals-VP].Transmittal_to_Site IS NOT Null) AS list1
    
    WHERE (list1.Rev)=(select max(REV) from list1 as l1 where l1.[OwnerDocumentNo]=list1.[OwnerDocumentNo])
    please help and please do not care about many field names in the query.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is referring to not finding this list1

    WHERE (list1.Rev)=(select max(REV) from list1 as l1 where l1.[OwnerDocumentNo]=list1.[OwnerDocumentNo])

    you have define list1 in your main query so you would need to define it again in your subquery which is effectively treated as a separate query.

    Suggest put your list1 in a separate query and then reference it in this one

    e.g. query list1

    Code:
    SELECT [tblDocument-VP].[Project No], [tblDocument-VP].[Document Type], [tblDocument-VP].Zone, [tblDocument-VP].Discipline, [tblDocument-VP].[PO NO], [tblDocument-VP].[Vendor Name], tblVendorName.VendorDesc, [tblDocument-VP].[Tag No], [tblDocument-VP].OwnerDocumentNo, [tblDocument-VP].[DOCUMENT / DRAWING TITLE], [tbltransmittals-VP].[VEDOR TRANSMITTAL NO], [tbltransmittals-VP].Rev, [tbltransmittals-VP].[Doc STATUS], [tbltransmittals-VP].[AC CODE], [tbltransmittals-VP].[MC AC Status], tblAC_Code.[AC Desc], [tbltransmittals-VP].Transmittal_to_Site, [tbltransmittals-VP].Transmittal_to_Site_date, [tbltransmittals-VP].HyperDoc
    FROM (tblVendorName RIGHT JOIN [tblDocument-VP] ON tblVendorName.[VENDOR NAME] = [tblDocument-VP].[Vendor Name]) LEFT JOIN (tblAC_Code RIGHT JOIN [tbltransmittals-VP] ON tblAC_Code.[AC Code] = [tbltransmittals-VP].[AC CODE]) ON [tblDocument-VP].OwnerDocumentNo = [tbltransmittals-VP].OwnerDocumentNo
    WHERE [tbltransmittals-VP].Transmittal_to_Site IS NOT Null
    main query

    Code:
    SELECT
    list1.[Project No],list1.[Document Type],list1.Zone,list1.Discipline,list1.[PO NO],list1.[Vendor Name],list1.VendorDesc,list1.[Tag No],
    list1.OwnerDocumentNo,list1.[DOCUMENT / DRAWING TITLE],list1.[VEDOR TRANSMITTAL NO],list1.Rev,list1.[Doc STATUS],
    list1.[AC CODE],list1.[MC AC Status],list1.[AC Desc],list1.Transmittal_to_Site,list1.Transmittal_to_Site_date,list1.HyperDoc
    
    FROM list1
    
    WHERE (list1.Rev)=(select max(REV) from list1 as l1 where l1.[OwnerDocumentNo]=list1.[OwnerDocumentNo])
    but you will probably find this to be very slow - better to use a join

    query list1MaxRev

    Code:
    select OwnerDocumentNo, max(REV) AS maxRev from list1 GROUP BY OwnerDocumentNo
    main query

    Code:
    SELECT *
    FROM List1 INNER JOIN List1MaxRev ON List1.OwnerDocumentNo=list1MaxRev.OwnerDocumentNo AND List1.Rev=List1MaxRev.maxRev

  3. #3
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    Thanks my problem solved, just one question if i do all the queries in one query with SQL expression and using Alias, speed is different? which one is better than when execute?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    aliasing itself makes no difference - although does make queries easier to read by using shorter and perhaps more informative aliases.

    Speed is more affected by indexing or lack of.

    Generally speaking it is faster to use an aliased table per my last suggestion - but this is not always possible if for example it needs to reference fields in the other table to determine what to return (such as the sum of values between two dates in the main table). In which case you need to use a sub query. Using a subquery as you have means the subquery has to be run for each record, with my method, it is run just once which is why it should be faster.

    As to whether or not to alias your table in a query e.g.

    Code:
    SELECT *
    FROM List1 INNER JOIN (select OwnerDocumentNo, max(REV) AS maxRev from list1 GROUP BY OwnerDocumentNo) List1MaxRev ON List1.OwnerDocumentNo=list1MaxRev.OwnerDocumentNo AND List1.Rev=List1MaxRev.maxRev
    it is a matter of personal preference - by aliasing, the query becomes harder to read as sql and what is happening is less obvious in the query builder. But on the other hand, you only have the one query. The other consideration is if the aliased table is used elsewhere - if so, you would then need to maintain it in multiple places.

    There is also the matter of the query plan which the db engine builds when the query is first run after construction or change. This is a small overhead which becomes more apparent if you construct and execute your queries in VBA since the query plan needs to be built each time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 09-12-2016, 09:13 AM
  2. Replies: 1
    Last Post: 11-04-2014, 12:07 PM
  3. Replies: 1
    Last Post: 10-12-2011, 08:07 AM
  4. Find duplicates query to populate input form
    By kctalent in forum Queries
    Replies: 6
    Last Post: 08-22-2011, 03:12 PM
  5. Replies: 10
    Last Post: 02-02-2011, 05:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums