Results 1 to 6 of 6
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Can I do this in one query (Access any version)

    In the attached db there is a table of items for auctions.
    There is a field Location which is actually used as a Category field and is blank for most items. When this is blank, the location (i.e. category) is the previous non blank location when the items are sorted into Auction order, and LotNo order.

    I now want to provide the capacity to search for items within categories, so I want a query that shows the Category next to each item.
    Query 3 does what I want, but it uses query1 and query2 to get there.

    I want to do this in a single query if I can, so I can put it into a single SQL statement that be put into an ASP script. In the context it would be difficult to have any saved queries.

    Can this be done?
    Regards
    John



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

    Re: Can I do this in one query (Access any version)

    I don't do ASP, so if the following is way off, I apologize in advance. Can you use functions such as DMax in ASP? If so, you can use

    SELECT tblwebItems.ItemID, tblwebItems.AuctionID, tblwebItems.LotNo, tblwebItems.LotDescription, DLookUp("Location","tblWebItems","ItemID=" & DMax("ItemID","tblWebItems","AuctionID=" & [AuctionID] & " AND LotNo<=" & [LotNo] & "AND Location Is Not Null")) AS Category
    FROM tblwebItems;

    If not, try

    SELECT tblwebItems.ItemID, tblwebItems.AuctionID, tblwebItems.LotNo, tblwebItems.LotDescription, (SELECT Max(s.Location) FROM tblWebItems AS s WHERE s.ItemID = (SELECT Max(t.ItemID) FROM tblWebItems AS t WHERE t.AuctionID=tblWebITems.AuctionID AND t.LotNo<=tblWebItems.LotNo AND t.Location Is Not Null)) AS Category
    FROM tblwebItems;

    These queries are stored as Query4 and Query5 in the attached database.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Can I do this in one query (Access any versio

    Thanks Hans

    Yes you can use DMax and DLookup within ASP so I can get the first one to work, until I try to do with it what I really want.

    Once I specify that I only want to look for items within a specific category I run into the problem of trying to set criteria against calculated fields.

    So I think I will have to find a way of having a saved query.
    Regards
    John



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

    Re: Can I do this in one query (Access any versio

    Try repeating the entire expression for Category in the WHERE clause:

    WHERE (((tblWebItems.LotDescription) Like "*soup*") AND ((DLookUp("Location","tblWebItems","LotNo=" & DMax("LotNo","tblWebItems","AuctionID=" & [AuctionID] & " AND LotNo<=" & [LotNo] & "AND Location Is Not Null")))="Ceramics"))

    or

    WHERE (((tblwebItems.LotDescription) Like "*soup*") AND (((SELECT Max(s.Location) FROM tblWebItems AS s WHERE s.LotNo = (SELECT Max(t.LotNo) FROM tblWebItems AS t WHERE t.AuctionID=tblWebITems.AuctionID AND t.LotNo<=tblWebItems.LotNo AND t.Location Is Not Null)))="Ceramics"))

    depending on which method you use.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Can I do this in one query (Access any versio

    Yes I have tried this and it does work, but it takes a very long time to return any results, even with the small data set I have at the moment.

    I have also tried it using a saved query. The moment I set criteria against Category it still becomes pretty slow.

    I will experiment with some more variations.

    There is actually a 'real' category field in the table that is usually left blank. Another option is to try to populate that with data.

    thanks again
    Regards
    John



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

    Re: Can I do this in one query (Access any versio

    Filling the Category field in the table is probably the best solution in terms of performance. Two levels of subqueries will never be speed demons.

Posting Permissions

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