Results 1 to 5 of 5
  1. #1
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62

    Suggestions on how to build this query(ies)

    I'm working on a project that needs data shifted in a way that I've not seen before. The source data looks like this:



    UniqueRecord# Bob Smith Phone# 5/15/18 11:00
    UniqueRecord# Bob Smith Phone# 5/16/18 10:00
    UniqueRecord# Bob Smith Phone# 5/18/18 09:00

    What I need to have happen, is that I need to append subsequent Dates and Times onto the end of the line, so the resulting like will only have 1 record for Bob Smith, and the Dates and Times following, as such:

    UniqueRecord# Bob Smith Phone# 5/15/18 11:00 5/16/18 10:00 5/18/18 09:00

    There might be anywhere from 1 to 10, say, appearances of Bob Smith, as he has subsequent appointments that week.

    I'm trying to figure out how to build this, so that I can capture the first appearance of Bob Smith, then append out the subsequent dates and times as shown above. Any thoughts are greatly appreciated!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This is actually a very common topic.

    Options:

    1. VBA code, review http://allenbrowne.com/func-concat.html

    2. A CROSSTAB query that includes a field that calculates a sequential identifier for each UniqueRecord# group. This calculated value become a ColumnHeader in CROSSTAB. Requires a unique ID field, autonumber type should serve. Something like:

    TRANSFORM First(DateField) AS FirstOfDate
    SELECT [UniqueRecord#], [PhoneNumber]
    FROM tablename
    GROUP BY [UniqueRecord#], [PhoneNumber]
    PIVOT DCount("*","tablename","UniqueRecord#='" & [UniqueRecord#] & "' AND ID<" & [ID])+1;

    3. VBA code that writes records to a 'temp' 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.

  3. #3
    Sephaerius is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Aug 2013
    Posts
    62
    This is really tricky, surprisingly so. I assumed there would be a more straightforward solution. I appreciate those suggestions, but I'm not exactly looking to concatenate the date and times, and crosstab isn't working because I have FAR too many records. This list has over 2000 names, some of whom only have 1 date/time, while others have up to 6, and some even have multiple same-day, in my current dataset, but I want to build it to be flexible in case we get oddballs that have even more.

    If anyone has any further suggestions I would be in your debt!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Only once have I ever encountered a situation where multiple values needed to be concatenated into one field (in my case, comma separated) and with good reason: it violates the normal form and ought to be rarely done. While there might be a good reason as to why you must do this, it's not evident in your posts.

    If you have far too many unique values to transpose into fields/columns, then I believe the only solution will prove to be a vba procedure that iterates over a table/query/recordset and appends every value related to the primary key into a new field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't see why the suggested CROSSTAB won't accommodate, other than the DCount() function can perform rather slowly on large dataset.

    The 3 suggestions really are the only options I am aware of.
    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.

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

Similar Threads

  1. VBA Query Build
    By jo15765 in forum Programming
    Replies: 4
    Last Post: 03-20-2017, 08:14 AM
  2. Build Query
    By Kyoshikawa in forum Queries
    Replies: 7
    Last Post: 10-06-2015, 10:03 PM
  3. Replies: 8
    Last Post: 07-24-2014, 11:11 PM
  4. Query taking too long, suggestions?
    By theperson in forum Queries
    Replies: 11
    Last Post: 05-29-2013, 10:17 AM
  5. How to build query
    By kashif.special2005 in forum Queries
    Replies: 2
    Last Post: 03-02-2012, 02:18 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