Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Need Query Help (2003)

    I'm a decent Access jockey, but I don't know much about two-table queries. I'm hoping to get some help in creating one.

    I have two tables: tblAllLocated, with a couple dozen fields of information about 1,300 members of our organization, and tblContributions, with information about the contributions they have made over time. It has about 1,200 records. The common field is Handle, which is like a customer number.

    tblAllLocated has one record for each member. tblContributions has one record for each contribution. tblContributions has three fields: Handle, ContributionDate, and ContributionAmount. If a member has made one or more contributions, he has that number of records in tblContributions. If he has never made a contribution, he has NO records in tblContributions.

    I need a query that will include some (or all) of the fields from tblAllLocated, plus the most recent ContributionDate and ContributionAmount for each member, if he has made any contributions. It's not a requirement, but it would be nice if the most recent contribution amount would show up as $0 or "zero" or something similar for those who have made no contributions. Otherwise, a blank field will do.

    Who can give me a hand?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Query Help (2003)

    Hi Lou

    Look at the attached db and see is this is what you are after.

    thanks,
    jackal
    Attached Files Attached Files

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Query Help (2003)

    Here's a slightly different version of Jackal's file - as far as I can see the wrong amounts were being returned. (I don't like the First and Last functions particularly, so have gone a different route).
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Query Help (2003)

    rory, when i tried to ues the Max function, it pulled all the Amounts. I believe Lou was wanting the most recent date and amount.

    thanks,
    jackal

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Query Help (2003)

    Yes, which is not what your query is returning (at least not when I run it). For T1, the last contribution date is 1 Feb 07, amount $101 per the tblContributions table. The query returns 1 Feb 07, which is correct, and $100, which is the first contribution in the table, not the most recent.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Query Help (2003)

    I see why mine did not work. Once I created a relationship between the 2 tables on Handle, it works fine.

    thanks,
    jackal
    Attached Files Attached Files

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Query Help (2003)

    Unless you add a new record out of date order! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> If you add a new contribution for T1 with a date of say 3 Jan 07 and amount of $400, the $400 will appear in the query. You get the last entered record, not the amount related to the date you have returned, which is why I prefer to use the intermediate query.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need Query Help (2003)

    AHHH... I see.


    thanks,
    jackal

  9. #9
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Need Query Help (2003)

    635,743 from Rory seems to do what I want it to do -- find the most recent contribution and its amount. "Your last contribution was X on DateY"

    I'll try it on my database and see what happens. An Access guru and I developed a truly miraculous database for our membership. I did the specifications and wrote a lot of the VBA code, but he did almost all of the queries. As a result, though I'm the co-creator of a (self-described) miracle application, I'm dumb as a stone about queries.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Need Query Help (2003)

    I've imported Rory's queries into my database, changing field names as necessary. The qsel is working fine -- for each handle that has a contribution, it shows the date of the last contribution.

    The other query isn't working. It seems to have one line for each record in tblAllLocated, but it shows all contributions from all members, giving more than one line for members who have made more than one contribution. Though I don't know SQL, my programmer's sense tells me the problem has to do with the Member field in the original query. My actual database doesn't have such a field, though we do have LastName, FirstName, etc. The Handle is the field that is unique for each member.

    To reiterate, what I need is the amount of the contribution that was made on the most recent date, as returned by the qsel. This and the date will be in a record with other fields from tblAllLocated (FirstName, LastName, Address, etc.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Query Help (2003)

    Do you have more than one contribution per date per member? Are you sure you have created the same table links as were in the original query? It's hard to say without knowing eactly what you are doing, so if you couls post a stripped down copy of the relevant tables, we can sort something out.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Need Query Help (2003)

    Here's a partial copy of both tables, put into one database. It was 175K, so I zipped it. If that's not OK, I can strip some more records or something to make it <100K.

    Answering your question: It is possible that there would be more than one contribution from a given person in a single day, but that would be highly unlikely. If it occurred, I'd accept either one as the entry for that day.
    Attached Files Attached Files
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Query Help (2003)

    How's this? (note: to get just one where there are multiple per day, add grouping and choose max contribution)
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Query Help (2003)

    Hi Lou

    I created a relationship on the tables using Handle as the key and the created the query below
    Jerry

  15. #15
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Need Query Help (2003)

    Rory's seems to work great. Jezza's is missing something, though.

    I used Get External Data to import Rory's two queries into my real database. They seem to do the job, and I have enough knowledge to add more fields from tblAllLocated until the result of the query is exactly what I need: selected data from tblAllLocated, plus fields for the date and amount of the person's most recent contribution. I'll use that result as a data source for a Word mailmerge document that shows everything we know about the guy, so he can look at it and correct it if needed.

    Couldn't have done it without you.

    How does one go about learning how to do these multi-table queries? I've got a few books, but they're pretty opaque (to me, at least). I've made up a small cookbook for the more exotic single-table queries I often need when working with my database (the straightforward ones, I can pretty much do myself). I might invest time and money in an Access class, but I'm not sure if multi-table queries are regarded as elementary material, advanced material, guru-only material, or what.

    Thanks again for the help.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Page 1 of 2 12 LastLast

Posting Permissions

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