Results 1 to 10 of 10
  1. #1
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53

    Combine rows based on one column

    Prefacing that I believe the larger issue here is table design; however, that's not something I can fix at the moment.

    Here is an example:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	13.7 KB 
ID:	46989

    Correct me if I'm wrong, but if I'm generating a report by line item, then I really need the query to have one row per item. Otherwise, the report won't know how to handle that.


    So, to accomplish this, I need each row with TIMB as the EdbSpc gone; but the data from the TIMB rows placed in the first row.


    Thanks

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That's confusing.
    Is this the source data or nearly the required output or something else?

    Ideally we would like to see samples of the original data, and a mock up of the desired output from that sample.
    Enough of both datasets to cover all possible results.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Suggest you revise your post and provide a plain English description of the "business process" issue you are trying to resolve with this database. Readers need some context in order to offer focused advice.

    Seems OrdId and Orditm could represent a product ordered by a Customer, but, if you could arrange the pieces, it would help with communications.
    Good luck.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with the foregoing. However, might be worth pointing out that sorting and grouping a query for a report is usually not needed. Reports have their own sorting and grouping capabilities, so you might be wasting your time trying to get a query to do it for you. The report will override whatever you do in the query when in it comes to sorting/grouping.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Here is the source data that I started with:
    Click image for larger version. 

Name:	Source.PNG 
Views:	14 
Size:	40.2 KB 
ID:	46992

    My first post picture is a query result of me taking the EBId and EdbId columns and turning them into the organized data I need for the report. Then for ETop through EBottom, I applied a Max(). Without the Max(), the query results look like this:
    Click image for larger version. 

Name:	Step 2.PNG 
Views:	13 
Size:	31.3 KB 
ID:	46993

    I am basically weeding out all the extra zeros until only two rows remain for each item.

    Additional info:

    Yes, customers place orders (ordId) which have line items (ordItm). Each item has six "traits" (EBId) and three "locations" (EdbId). So based on the EBId and EdbId, that tells me where the EdbSz should go on the report. I have an OrdTbl, an OrdDetTbl, and an OrdDetBandTbl. Information on OrdDetBandTbl is entered on a subform of OrdDetTbl. OrdDetTbl has the records of each item. So, after I get OrdDetBandTbl data done, I'll join it with most of OrdDetTbl, and a couple fields from OrdTbl onto the final report.

    The problem is that nearly all items have two EdbSpc tied to each item; which means I will have two records per item. In my prior experience, this breaks reporting by item; so I'm trying to condense everything into one row per item.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Still not clear what you want the final output to look like.

    How did you classify Top, Left, Right, Bottom? Which EBId means what?

    So we don't have to rebuild table and input data, could either provide the db or a spreadsheet of raw data or could even copy/paste a table directly into post which would look like:

    TypeID TypeName
    1 Hours
    2 Purchases

    Switch to Advanced post editor to create/modify table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a new query based on the one posted in the original post, group by OrdItm, use Min for ErdSpc to get the OAKW and use Max for the others. Is that what you're after?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by June7 View Post
    Still not clear what you want the final output to look like.
    In my example, I'd like the final output to be: | 12345 | 1 | OAKW | 1.188 | .5 | .875 | .5 | .875 | 1.188 | TIMB |
    I want one record per item.


    Quote Originally Posted by June7 View Post
    How did you classify Top, Left, Right, Bottom? Which EBId means what?
    1 = top, 2 = bottom, 3 = left, 4 = right.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Build two queries, one for OAKW and one for TIMB. If you expect both to contain the same OrdItm values, join one query to the other, otherwise join both to a dataset of unique OrdItm values.

    If you still need help, provide data as text table or attached file as already suggested.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Quote Originally Posted by Gicu View Post
    Create a new query based on the one posted in the original post, group by OrdItm, use Min for ErdSpc to get the OAKW and use Max for the others. Is that what you're after?

    Cheers,
    I will test this. Thank you.

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

Similar Threads

  1. Replies: 8
    Last Post: 02-08-2016, 11:40 AM
  2. Replies: 15
    Last Post: 07-30-2015, 01:38 PM
  3. Replies: 4
    Last Post: 03-10-2015, 08:26 AM
  4. Combine datatable rows based in common value
    By pipelian in forum Access
    Replies: 1
    Last Post: 11-27-2012, 10:20 AM
  5. Query to find and COMBINE rows based on 2 fields
    By admessing in forum Queries
    Replies: 2
    Last Post: 12-13-2011, 12:59 PM

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