Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals Query (2000)

    I have a totals query where I want to bring everything up to the Client level so I group by client number and sum members. The rest of the fields, I chose "Last" so I get the last record's data. Most of the time this works but for the field "broker", some the records could have a broker and sometimes it could be the last record, first record or anywhere in between. Is there a way (in a totals query) to get the first record that has something in this field or if all the records do not have data for this field then null would be what I get but if just one record has data for this field, then that is what I want.

    The heirarchy is Client Numbers. Each Client Number has multiple group numbers. The broker and the rest of the fields are at the group level. So if a client has 10 groups, each group could have a broker or it may not have a broker. To get this at the client level, I chose "Last" (or I could of chose "First") to just get the data at client level instead of having multiple Client Numbers. Thanks for your help. I hope this makes sense.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    I would use a DLookUp expression to achieve this:

    DLookUp("Broker","<!t>[table Name]<!/t>","Broker Is Not Null")

    - this should return the first non-null value of "Broker" if there is one, and Null if there are none. Make sure the "Totals" box is set to "Expression" otherwise the database will complain that it can't execute totals on a dlookup.

    Edited to sort out the square brackets
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Should this be entered in the "Field" area or criteria area of Broker field?

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    In the field area. Access will add it's own field name (Expr1) which you can change to something more meaningful (but don't use "Broker" - that will confuse things. "Broker1" would be ok).
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    I ran the totals query and I don't think I got the results that was intended. The broker field is the same name for each record and that isn't correct. It isn't just looking for the clients records, it is looking at the whole table. Maybe I am doing something wrong.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    I am getting a syntax error. Here is what I think you wrote: DLookUp("Broker"," "," (Broker Is Not Null ) and client_Number = ''' & Client_Number & ""')

    Are you using single quotes after client_Number? Should there be two "," after Broker?

  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Edited by HansV to correct problem with the way the Lounge software interprets some text

    No, that's totally my fault, I'm sorry. The DLookUp should be this:

    DLookUp("Broker","<!t>[table Name]<!/t>","(Broker Is Not Null) AND [Client Number]='" & [Client Number] & "'")

    (where "[Client Number]" is the name of your client number field. If the field name includes a space, keep the square brackets; otherwise you can ditch them).
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Totals Query (2000)

    There was a problem with Dave's reply - text starting with "table" between square brackets is seen as an HTML tag. I replaced the square brackets with <!t>[t]<!/t> and <!t>[/t]<!/t> tags, so it should be what Dave intended now.

  9. #9
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Sorry, I forgot that the Lounge software converts "<!t>[" and "]<!/t>" into tags; what I should have written was:
    DLookUp("Broker","<!t>[Table Name]<!/t>","Broker Is Not Null AND Client_Number='" & Client_Number & "'")

    I've assumed that Client_Number is actually a text field; if it's actually a number, you could use:
    DLookUp("Broker","<!t>[Table Name]<!/t>","Broker Is Not Null AND Client_Number= & Client_Number)

    Sorry about the confusion, I was rushing my answers a bit. Hopefully the above should work properly now.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  10. #10
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Thanks, Hans. <!t>[My mum always said I'd forget my head if it wasn't screwed on...]<!/t> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Totals Query (2000)

    I always find it a rather confusing feature of the Lounge software that anything starting with "table" or "tr" between square brackets is seen as a tag, I fall into the same trap from time to time. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    It works. I must have been typing the quotes wrong. Thanks for all your help. It works perfectly.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    I have another request with the same totals query. I am sorry to revisit this. I have another field (UHBP) where I pick "Last" to get the last records data. What they are requesting is this:
    Instead of "Last" is there a way similar to the DLookup to base what is in the UHBP field on the field "Member" where the record that has the largest member number, use the corresponding data in UHBP field. If the one record has 90 and Y for UHBP and another record has 3 for member and N for UHBP. I want UHBP to pick up the Y because the member number is highest. This is based within the Client Number just as before. Thanks for any help you can provide.

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

    Re: Totals Query (2000)

    Try:
    <code>
    DLookUp("[UHBP]","<!t>[Table Name]<!/t>","[Member]=" & DMax("[Member]","<!t>[Table Name]<!/t>","[Client Number]='" & [Client Number] & "'") & " AND [Client Number]='" & [Client Number] & "'")
    </code>
    I have assumed that Member is a number field.

    This DMax within a DLookup might slow down the query considerably. If that is a problem, there are other ways to retrieve the value.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    That worked fine. I copied the database to my C drive and ran this and then copied it back to the network drive. The network is very slow. I figure once a mth when I have to update this data, I will just copy the database to my C drive and then copy it back to the network. Just a side question. If I split this database, front end /back end, when I import my Excel file - how do I do this with a split database? Do I import into the backend ? My macros and queries that perform the delete from table, append etc are in the front end. Can I do this from the front end? I don't understand how this works when the data is being imported and then manipulated by macros etc. Thanks for your help. I have learned (even though you might not think so by my constant questions and problems) so much from this forum. There is so much about Access to know, It would take a lifetime to learn and understand everything.

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
  •