Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    7

    Unanswered: Too many records from query. How can I do this more elegantly?

    I have found "Top 1" queries to be tricky to use, can anyone see if I am going completely wrong. This problem is an A-level revision paper and the solution needs to avoid VBA.

    I posted an issue the other day where I has two queries (winning bidders and plots (each of which matched the design criteria for some of the bids) .
    Each produced the same number of records. They were in the correct order to do the next thing which was

    To create an update query which took a column from each query and did an update to the plots table of the first query using a field (bidder ID) from the second.

    Regardless of which join I used, I found that joining the two queries resulted in each bidder getting some plots or each plot getting some bidders instead of each plot getting one bidder.

    In the end I used a "top 1" query on the bidders to find the next bidder and a "top 1" query on the plots to get the associated plot.
    If I put a sort in a top 1 query, I lose the top 1 part and get all the records. Similarly, if I convert to update query I lose the top 1.

    As a fix, I fed 1 query into another and then into another to get the result I needed. I then had to call this mess from a macro to get all the records done.
    Without VBA, how can I do this more elegantly?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If this comes from an exam paper then please could you post the question exactly? You've made some interpretations and include some implementation details that, for me, get in the way of the question somewhat.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Elegantly and using macros are 2 things that do not go well together. If you want to really get into the power of opening/running queries, vba code (versus macros) is the route to go.

    It sounds like you need to practice a bit with the different types of queries. There's a multitude of ways to design queries and you'll need to get more specific on what exactly you want to accomplish with a specific query. It's too general of a question to ask "how can I design this query differently". - There are tons of different ways such as using Make-Table queries, Summarization queries, append queries, etc...

    Query joins between a table or joining another nested query is often critical on what results you return.

    I would strongly encourage learning vba versus learning macros. They accomplish the same thing but you can also do soo, soo much more with vba. If this is your career path (ie. developing), you really need to know vba to survive in this career. I personally only used macros for my 1st project or 2 (many, many years ago). I haven't designed a macro in over 25 years.
    Last edited by pkstormy; 02-04-10 at 09:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2010
    Posts
    7

    Exam question.

    Google this

    N33078A GCE Applied ICT 6957 01 May 2008.pdf

    In the mark scheme, there are marks for automating task 4. This is not clear from the question.

    I have done 1,2,3 and do not expect users of this forum to plough through 1,2,3 in order to answer my query on 4.

  5. #5
    Join Date
    Feb 2010
    Posts
    7

    Re VBA v macros

    I have no axe to grind so far as VBA is concerned. I can see that in any practical sense that would be the way to go if you have to use Access.

    The exam board concerned insist that VBA is unnecessary so far as these papers are concenred so I am helping someone teaching the course to do example solutions.

    When programming user interfaces to database I tend to use java/my-postgres-oracle-sql anyway. The way that the query grid in access trashes sql versions of queries is a pretty severe source of frustration as well!

Posting Permissions

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