Results 1 to 6 of 6
  1. #1
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65

    Vertical input to horizontal output

    I want to convert a sequential list to a crosstab type of output.

    The input is derived from an Excel table in the format:
    DocNr ... DocDate ...... TypeCod ... HholdID ... AsSocRef
    193.09 ... 12/Dec/2009 ... AS ........ 2537 ........ 25
    195.09 ... 12/Dec/2009 ... AC ........ 2537 ........ 25


    197.09 ... 14/Dec/2009 ... FM ........ 2537 ........ 25
    286.14 ... 15/Apr/2014 .... AS ........ 2537 ........ 25
    289.14 ... 16/Apr/2014 .... FM ........ 2537 ........ 25

    The DocNr is unique, its date is also specific to that document although many documents can have the same date.
    There are 6 possible document types: AS, AC, FM, PI, RE, CE. DocType = TypeCod.
    Each document relates to only one household but households may have repeats of any type or none of any type, but they will have at least one document.

    The objective is to be able to produce a report for any one household, listing all its documents in a tabulated form:
    HholdID ... DocType ... DocNr .... DocDate ....... DocType ... DocNr ....... DocDate ....... DocType ... DocNr ..... DocDate ... etc ... etc
    2537 ....... AC .......... 195.09 ... 12/Dec/2009 ... AS ......... 193.09 ... 12/Dec/2009 ..... FM ......... 197.09 .... 14/Dec/2009
    2537 .................................................. ........ AS ......... 286.14 ... 15/Apr/2014 ..... FM .......... 289.14 ... 16/Apr/2014
    .
    I have 6 queries which separately identify the documents for each household by each type but not all households have every type of document.
    These queries take the form as follows, with a separate but similar structure for each DocType
    Code:
    SELECT [AsSocRef] & Format([data],"yy") AS ID1, AsSocDocList.HholdID, AsSocDocList.AsSocRef, AsSocDocList.TypCod, AsSocDocList.DocNr AS ASnr, AsSocDocList.Data AS ASdata
    FROM AsSocDocList LEFT JOIN AsSocDocType ON AsSocDocList.TypCod = AsSocDocType.DocTypeCod
    GROUP BY [AsSocRef] & Format([data],"yy"), AsSocDocList.HholdID, AsSocDocList.AsSocRef, AsSocDocList.TypCod, AsSocDocList.DocNr, AsSocDocList.Data
    HAVING (((AsSocDocList.TypCod)="as"))
    ORDER BY AsSocDocList.AsSocRef;
    My problem is how to merge them. It looks as though I need to somehow create 6 simultaneous left-joins to compile the records for each household.
    Is ALTER TABLE a possible solution? If so, what is its syntax?
    Thanks for reading this far!!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this is not how databases report.
    They report vertically.
    DocType,DocDate,DocNr

    it is much easier to read too.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Think you are looking at a lot of issues - what if a household has so many docs they won't fit on the page, what if some households have one of a particular doctype and another has 2, or 3.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Whilst crosstabs have many uses in databases, I agree with both Ranman & Ajax that this isn't suitable for what you're asking
    If you want that format, stick to using Excel
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Or use Allen Browne's custom function ConcatRelated http://allenbrowne.com/func-concat.html

    However, it can slow performance and the string length might exceed what is allowed and making output line up in columns by type would be extra complication.

    And yet another approach is VBA code that writes records to a 'temp' table - table is permanent but records are temporary. This will be complicated code.
    Last edited by June7; 12-07-2018 at 09:06 PM.
    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.

  6. #6
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65
    Thanks guys, you have at least confirmed that I haven't overlooked something simple!
    Back when I was being paid to do this I would regularly use Access for processing and then move to Excel to make use of its better presentation options, so maybe that is the way to go. I have also benefitted from Allen Browne's skills in the past and I will explore that route further.

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

Similar Threads

  1. Horizontal instead of vertical
    By UT227 in forum Reports
    Replies: 2
    Last Post: 07-10-2017, 10:25 AM
  2. Equal horizontal/vertical doesn't work?
    By warmslime in forum Access
    Replies: 1
    Last Post: 03-19-2016, 12:53 PM
  3. Display Names horizontal instead of vertical
    By bradleyg in forum Reports
    Replies: 2
    Last Post: 10-03-2014, 12:39 PM
  4. Replies: 12
    Last Post: 05-23-2013, 10:51 AM
  5. Horizontal to vertical data
    By hoachen in forum Access
    Replies: 3
    Last Post: 07-18-2012, 10:28 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