Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    North Carolina USA in winter, Maine in summer
    Posts
    177
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Design a query to return only first occurence of a field

    It will be obvious that I am not a "power user" of Access. I am using Access 2007 and have a database of books that I have read over the last 10-12 years and one field is the name of the author. I would like to design a query (hopefully not using VBA, which I know nothing about) to return only the first occurence of the author so as to have a record of all authors which I have read over the years.

    I suspect that this is not a difficult task but it does have me stumped at the moment. Any help anyone could provide would be much appreciated!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.M.

    You can do the following:
    AuthorLIst.PNG
    Note: I could have counted the Titles vs ISBNs both will work.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    ExiledMainer (2013-01-23)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    North Carolina USA in winter, Maine in summer
    Posts
    177
    Thanks
    5
    Thanked 0 Times in 0 Posts
    RetiredGeek-

    Thanks for the (very prompt!) reply! Your sample query was just the ticket and I now have a query that does just what I wanted. Special thanks for the "Count" in your sample since I also wanted that feature but neglected to mention that in my original post. I am now trying to design a report from this query but keep getting a "Enter Parameter Value" for "DatePub" when I try to open/run my report. I really don't understand this since that field is not even in the query. I will keep playing with the report to get rid of that but, push come to shove, I can always simply print out the results of the query.

    Once again, thanks for your response!

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Why don't you show us the source of your query?

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.M.

    I forgot to mention I first selected Cross-Tab query. Then got the groupby and count fields in then changed back to Select Query. I know there is another way to do this but I just can't remember. I'll look it up when I get a chance...if I remember.

    Here's a TechRepublic link on how to set this up. Geez the Totals button who would have thought?
    Last edited by RetiredGeek; 2013-01-23 at 17:21.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    North Carolina USA in winter, Maine in summer
    Posts
    177
    Thanks
    5
    Thanked 0 Times in 0 Posts
    patt-

    I'm not sure exactly what you mean or know how to do so in any case!

  8. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    The SQL of your query is like SELECT var1,var2 FROM tablename WHERE var3 = 0

    Would you send a zipped compacted database with the query, report and tables applicable.
    Last edited by patt; 2013-01-24 at 17:04.

  9. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    North Carolina USA in winter, Maine in summer
    Posts
    177
    Thanks
    5
    Thanked 0 Times in 0 Posts
    patt-

    RetiredGeek's reply (post #2) solved my problem so there is no need to continue on. Thanks for your interest and posts!

  10. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Did you solve the Enter Parameter for "DatePub" problem?

  11. #10
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    North Carolina USA in winter, Maine in summer
    Posts
    177
    Thanks
    5
    Thanked 0 Times in 0 Posts
    patt-

    Nope, still haven't gotten that one figured out yet. Any help/suggestions appreciated!

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by ExiledMainer View Post
    RetiredGeek-
    I am now trying to design a report from this query but keep getting a "Enter Parameter Value" for "DatePub" when I try to open/run my report. I really don't understand this since that field is not even in the query. I will keep playing with the report to get rid of that but, push come to shove, I can always simply print out the results of the query.
    Did you accidentally put DatePub in the Report? From what you are saying the query runs w/o problem and if that is the case I would definitely look for DatePub somewhere in the report specification.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    That is why I suggested you send us a database containing the query, report and tables !!

Posting Permissions

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