Results 1 to 11 of 11
  1. #1
    Leonel is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    12

    Unhappy Records in s single row

    Hi, thank you; I think the answer may be easy, but at this point my brain in numb.



    I merged two tables where some fields are null for some records. When there's more than one line for a record, I need to merge them to show the info in a single line.

    The union query looks like this:


    Record FieldBefore FieldAfter
    Rec 1 abc (null)
    Rec 1 (null) 123
    Rec 2 (null) 857
    Rec 2 zzz (null)

    How can I get the following display:

    Record FieldBefore FieldAfter
    Rec 1 abc 123
    Rec 2 zzz 857

    Thank you

    I really appreciate your advice!
    Leonel

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    On fly

    Code:
    SELECT Record, MAX(Nz(FieldBefore,"")), MAX(Nz(FieldAfter,0)) FROM YourUnionQuery GROUP BY Record

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Change each union query segment to not return nulls? Maybe add the DISTINCT or DISTINCTROW predicates to the SELECT portion of the statements as well? Can't really say without knowing something about the tables/data.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Leonel is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    12
    Thank you so much!

    In the example I only included two rows per record to make it easier. Will this work the same if the record is split in three or more rows. I'm guessing it won't.

    Thank you!
    lg

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are referring to whom

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Or you have some rule to get merged value, or you have to merge values manually. Computer doesn't make decisions like "It looks for me better this way!" And you have to remember, in databases the phrase "This row is before another" is without meaning, unless you have some field like row number or entry datetime in your table, with according values saved there.

    When you have more than 3 rows possible, and you have to merge values based on those 3 columns only, the choices for calculated values are:
    for text values largest one or smallest non-empty one (you need a saved query joined to main query in 2nd case);
    for numeric values Sum, largest one, smallest non-empty one or average of non-empty entries (for 2nd and 3rd case, you again need to use a saved query).

  7. #7
    Leonel is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    12
    Thank you all!
    I'm sorry I didn't come back earlier. The pressure to finish the job was a bit too big. At the end I realized the request was impossible to fulfill because the requester asked for all records to be displayed in only one line, but in some cases the variables were 3 or more. If I had only two I could have had a sort of "before" and "after" column, but with unknown number of variables, I would have been forced to having queries (and reports) with an unknown number of columns.

    Thank you again
    lg

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not impossible I think. Just that if the number of fields varies, then would have to accomplish via code. Basically, recordset 1 (rs1) would create a DISTINCT set of values using whatever constitutes "record1". Using that as a list, go through the other fields and append both the field and value for every field that has data for rs1 list items. Not saying it's simple, just that if you can envision what needs to be done, it probably can be.

  9. #9
    Leonel is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    12
    Thank you, I agree it's not impossible for a query, and even for a report, but if for any reason the variables are way many, the paper size would not be enough (or the columns would be so narrow that they'd be useless).
    Best
    lg

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then someone should stop thinking with an Excel brain!

  11. #11
    Leonel is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    12
    @Micron, that's exactly the problem. RDBMS is an alien concept (but want the benefits of it).

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

Similar Threads

  1. Building a single variable value of different records
    By stephenaa5 in forum Programming
    Replies: 7
    Last Post: 01-29-2015, 08:36 PM
  2. Mutiple Records on a Single Line?
    By RMCook in forum Queries
    Replies: 1
    Last Post: 02-20-2014, 02:29 PM
  3. Linking Mutiple Records to Single Records
    By LukeJ Innov in forum Access
    Replies: 3
    Last Post: 04-23-2013, 08:59 AM
  4. Mutiple records into a single record
    By lwhatford in forum Database Design
    Replies: 3
    Last Post: 02-14-2012, 11:32 AM
  5. Multiple records on a single page
    By neo651 in forum Forms
    Replies: 1
    Last Post: 06-29-2011, 10:21 PM

Tags for this Thread

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