Results 1 to 10 of 10
  1. #1
    michaeltorpedo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    6

    Line item query

    Hello all i am a rookie with access.

    I have a table with the flowing headings
    Material-Quantity-Description- Price

    I want to group all of the material into 1 and count the quantity of that item.

    The description may vary a little bit. Example-Cell phone can also be seen on another line as Cell phone 1.5

    abc-1-Cell Phone-10
    abc-2-Cell phone 1.5-10
    abc-3-Cell Phone-10
    abc-3-Cell Phone 1.3-10
    abcd-3-House Phone-20
    abcd-2-House Phone 11.5-20
    abcd-1-House Phone 10.5-20


    abcde-2-Office Phone-30
    abcde-2-Office Phone 3.5-30

    I would like to see

    abc-9-Cell Phone-40
    abcd-6-House Phone-60
    abcde-4-Office Phone-60

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    abcde-Office Phone-60
    Do you mean:
    abcde-4-Office Phone-60
    ?

  3. #3
    michaeltorpedo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    6
    Robeen yes i did sorry about that.

  4. #4
    michaeltorpedo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    6
    Quote Originally Posted by Robeen View Post
    Do you mean:
    abcde-4-Office Phone-60
    ?
    Yes i fixed original post.

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    There's a problem with what you're trying to do - because your descriptions are not consistent.
    abcd-3-House Phone-20
    abcd-2-House Phone 11.5-20
    abcd-1-House Phone 10.5-20
    . . . will not [by default] group into one group because there are three different descriptions.

    I think the ideal thing would be for there to be two Description fields - one for the Overall description and one for 'Sub' description.
    Can you make that change to your data?

    If you CAN make that change to your data - the grouping will become easy.

    If you don't have the option to make that change, then it becomes a little bit more complicated.

    Let us know . . .

  6. #6
    michaeltorpedo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    6
    Quote Originally Posted by Robeen View Post
    There's a problem with what you're trying to do - because your descriptions are not consistent.

    . . . will not [by default] group into one group because there are three different descriptions.

    I think the ideal thing would be for there to be two Description fields - one for the Overall description and one for 'Sub' description.
    Can you make that change to your data?

    If you CAN make that change to your data - the grouping will become easy.

    If you don't have the option to make that change, then it becomes a little bit more complicated.

    Let us know . . .
    I don't know any way to remove the description but what we can do is eliminate the description field for the query and i can inert those descriptions after separately.

  7. #7
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    If you do not have the Description field in your query, then all you have to do is:
    1. Create -> Query Design.
    2. Choose your Table.
    3. Drag in Material, Quantity &Price.
    4. Click the big 'Totals' button.
    5. Leave the 'Total' row under Material as 'Group'.
    6. Change the 'Total' row under Quantity and Price to 'Sum'.

    There is a way to include the Description field in this query using the 'IIF()' function.
    Would you like to try it?

  8. #8
    michaeltorpedo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    6
    OMG that was toooooooooooo easy. I guess i overlooked the whole thing.

    I would like to try with the IIF function. Where should i put that iff function at?

    I have another table that has the Material and description correctly. How can i get the current query to get the description from the other table called prices. I had an idea but didn't work.

    Figured it out. I used the second table that had the Materiel and description. i LINKED Material to Material and then added Description to the Query and WALA it worked like magic.

    THANKS FOR ALL OF YOUR HELP ROBEEN

  9. #9
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Happy to help!
    Do you still need to try the IIF function or is your problem totally resolved?

  10. #10
    michaeltorpedo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    6
    No i don't need to try the IIF function thanks so much!... It is totally resolved. The boss is happy hahahahhah

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 28
    Last Post: 03-13-2012, 07:48 AM
  2. Replies: 7
    Last Post: 10-25-2011, 08:32 PM
  3. Replies: 7
    Last Post: 04-11-2011, 03:58 PM
  4. Last matched item in an unmatching query
    By kabaa01 in forum Queries
    Replies: 0
    Last Post: 05-19-2010, 03:46 PM
  5. Replies: 0
    Last Post: 08-26-2009, 11:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums