Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Match two fields and sum column (Access 2002)

    Hi

    I am total beginner to Access,so I am making a simple request (I' m sure it is to you guys).

    I would like to match the Key Acc to the Post Code and sum columns 2004 and 2005.

    So the result would be as follows

    ID Key Acc No Name Post Code 2004 2005
    1 123456 Smith NN5 6RG 150 150
    This is just small sample, May have one Key Acc with several post codes, so I need to match the post codes to the Key Acc But I only want to see one Total for all Post Codes in 2004 and 2005
    If you are a fool at forty, you will always be a fool

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match two fields and sum column (Access 2002)

    Braddy, you can use a select query to do this, the picture attached shows the result, and the query design of how to attain that result. Hope this helps!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Match two fields and sum column (Access 2002)

    Hi Jeremy

    Not quite the result I was looking for if you see my origional post 2004 would read 150 and 2005 would read 150.

    Thanks for the reply.

    Braddy

    NB What I am trying to do is because a Key Acc has more than post code I want to show just one post code but to show a single total for the whole key account, so that I can eliminate multiples of the post codes.
    If you are a fool at forty, you will always be a fool

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

    Re: Match two fields and sum column (Access 2002)

    Why do you need ID, Name and Post Code in the query? If you're going to aggregate on Key Acc, it's misleading to include those fields. Try the following:
    - Create a new query based on tblTest1.
    - Add the Key Acc, 2004 and 2005 fields to the query grid.
    - Select View | Totals.
    - Leave the Total option for Key Acc as is (Group By)
    - Set the Total option for 2004 and 2005 to Sum.
    - Switch to datasheet view to see the result.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match two fields and sum column (Access 2002)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> I guess that's exactly what he's lookin for, and I learned something new <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Thank you.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Match two fields and sum column (Access 2002)

    Hi Hans

    I don't need the ID but I do need Key Acc Name and possibly the post code because it needs to be allocated to the salesman who looks after this account.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Match two fields and sum column (Access 2002)

    But they are not unique, as you indicated yourself. So you can't have "the" salesman who looks after an account. In the first post, both Smith and Jones (alas) correspond to Account 123456.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Match two fields and sum column (Access 2002)

    Hi Hans

    I hear what you say, I need to take another look at this, and you guessed it, I will be back.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Match two fields and sum column (Access 2002)

    Hi Hans

    I'm sure if this is the correct procedure or whether I should make a new post , please forgive If I am wrong!

    I would like to do the following:

    Make 1322899 = 1322899
    and 1355447 = 1322899 because both the codes belong to CustomerName Premier Lodge, I only want to see CustomerName Premier Lodge once,

    But with a summary of Indirect2004 and Indirect2005 and Direct2004 and Direct2005

    ie 200 100 50 24

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Match two fields and sum column (Access 2002)

    1) Do you want to change 1355447 to 1322899 in the table itself, or only in the query, to be able to create the summary.

    2) How do you decide that 1355447 must be changed to 1322899, and not 1322899 changed to 1355447?

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Match two fields and sum column (Access 2002)

    Hi Hans

    I'm not sure it changing the numbers in the table would be the way to go, please see a better sample attached.

    Everything here would be summarised to Number 1306375
    If you are a fool at forty, you will always be a fool

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

    Re: Match two fields and sum column (Access 2002)

    Try the following:
    - Create a new query based on tblTest1.
    - Add the Key Acc, CustomerName, 2004 and 2005 fields to the query grid.
    - Select View | Totals.
    - Leave the Total option for Key Acc as is (Group By)
    - Set the Total option for CustomerName to First.
    - Set the Total option for 2004 and 2005 to Sum.
    - Switch to datasheet view to see the result.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Match two fields and sum column (Access 2002)

    Hi Hans

    It did not work when I set Set the (Total option for CustomerName to First.)
    But when I set Key Acc to to First I got the result I was looking for.

    Please accept my grateful thanks.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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