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

    Unanswered: Query produces too mnay records.

    I end up in this problem with two data sets that I just want to place side by side, in the same order, in a report table. I can't do it.

    I have an example question from an old exam. You make a database where users bid for house designs and if they are successful in their bid they knock out the lowest bidder for that design of house.

    When the bidding is over there are 57 successful bids and 57 plots. The successful bids are accessed easy enough by searching on a "bid currently successful field".

    There is a one to one match between successful design bids and plots available for that design.

    So a row from the 57 successful bids query would be

    date, value of bid, successful, ID, design.
    20/1/2010 $218000 Yes joe.bloggs DT3

    A Row from the plots table will have
    Plot Id, Design e.g.
    TW1001, DT3

    Once the bids are over I want to generate a report or report on an on-screen form where the successful bids and plots are joined up to allocate plots to bids. This should produce 57 entries.
    The successful bids query is easily made to be in the same order as the plots table.

    Whenever I try to generate a query to do this I end up with either plots allocated lots of times to bidders or bidders allocated lots of times to plots.


    Any ideas?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by nerak99 View Post
    Once the bids are over I want to generate a report or report on an on-screen form where the successful bids and plots are joined up to allocate plots to bids. This should produce 57 entries.
    Use a JOIN statement in your query. Without looking in depth in to your particular problem, I'm going to guess you're going to need an Inner Join on the Design field, but I'll let you as the developer figure out those details This should hopefully get you pointed in the right direction at least. Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Feb 2010
    Posts
    7

    Query does that with the join

    Thanks very much for your response. I guessed it was a join issue as well however ..

    I have tried a join. With a join in, I get 477 records rather than 57. Depending on which join I use I either get each bidder with every plot or each plot with every bidder,
    With no join I get a cross over with both and around 3000 records!

    It seems to me that this should be easy and I am missing something obvious.

    It maybe that I need to do some sql that access can't do with its query building tools but I am sure that this is not necessary.

    After all I just need to tag each plot onto the same row as each row from the successful bid query, in the same order.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the query desinger is a prety good tool for most jobs, but it is only a front end to writing the SQL yourself.. if you look at the SQL view (usually the left most tool button under the 'file' menu option you will see the SQL the designer is building. if you are unsure of your MS Access join syntax it can be useful to plonk the tables you need onto the surface, make the links as required, or use the suggested links based on the RI links. bear in mind you can modify those links within the query by editing the link properties.

    if you want the best bid per plot then I presume at some stage you'd want to look at the 'top' predicate or max predicate
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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