Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44

    Cross Tab Query

    I get unique records from a data base that instead of giving me vertical information on the same record I get 3. How can I create a query that will take the 3 records and make them one. Example pasted below. One Doc # and 3 columns. I would like to move up the Obligation and Expenses to one row with the Commit



    Doc Commit Obligation Expense
    10WRMPP12 4689736
    10WRMPP12 897459
    10WRMPP12 54271
    Any help is appreciated

    Thank you
    Gene


  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Actually, that data is vertical.
    Maybe this will work for you (I have never used it)
    http://allenbrowne.com/func-concat.html

    EDIT - just realized that you probably don't want all the values in one field. You want data in 4 fields across? I probably should have suggested 3 queries joined on Doc (or one query with 3 self joins) because the data already looks like a cross tab.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Try this by substituting hikerdood for the name of your table.
    Code:
    SELECT hikerdood.Doc, hikerdood.Commit, hikerdood_1.Obligation, hikerdood_2.Expense FROM (hikerdood 
    INNER JOIN hikerdood AS hikerdood_1 ON hikerdood.Doc = hikerdood_1.Doc) INNER JOIN hikerdood AS hikerdood_2 ON hikerdood_1.Doc = hikerdood_2.Doc
    WHERE (((hikerdood.Commit) Is Not Null) AND ((hikerdood_1.Obligation) Is Not Null) AND ((hikerdood_2.Expense) Is Not Null));

  4. #4
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Micron,

    Will give it a try and let you know.

    Thank you for your time

    Regards

    Gene

  5. #5
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Micron.. I posted the code in my query below and get "Syntax Error in the from Clause"

    SELECT FY102275.Doc, FY102275.Commit, FY102275.Obligation, FY102275.Expense FROM FY102275
    INNER JOIN FY102275 AS FY102275.Obilgation ON FY102275.Doc = Obligation.Doc) INNER JOIN FY102275 AS FY102275.Expense ON Obligation.Doc = Expense.Doc
    WHERE (((FY102275.Commit) Is Not Null) AND ((FY102275.Obligation) Is Not Null) AND ((FY102275.Expense) Is Not Null));

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You left off all the aliases. You replaced hikerdood_1 etc. with the name of a table, not hikerdood.
    More like FY102275_1 etc.
    I can tell you that it works, so you'll get there!

    Doc Commit Obligation Expense
    10WRMPP12 4689736 897459 54271
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I would import the raw data to Power Query and do a Fill Up function and then delete the rows containing nulls. This can then be brought back into Access.

  8. #8
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    [QUOTE=Micron;444524]You left off all the aliases. You replaced hikerdood_1 etc. with the name of a table, not hikerdood.
    More like FY102275_1 etc.
    I can tell you that it works, so you'll get there!

    Doc Commit Obligation Expense
    10WRMPP12 4689736 897459 54271

    Micron: Created a new table named it hikerdood using below code and got some data but not like you did. See results at bottom

    SELECT hikerdood.Doc, hikerdood.Commit, hikerdood_1.Obligation, hikerdood_2.Expense FROM (hikerdood
    INNER JOIN hikerdood AS hikerdood_1 ON hikerdood.Doc = hikerdood_1.Doc) INNER JOIN hikerdood AS hikerdood_2 ON hikerdood_1.Doc = hikerdood_2.Doc
    WHERE (((hikerdood.Commit) Is Not Null) AND ((hikerdood_1.Obligation) Is Not Null) AND ((hikerdood_2.Expense) Is Not Null));

    Doc Commit Obligation Expense
    10WRMPP12

    54271
    10WRMPP12


    10WRMPP12


    10WRMPP12

    54271
    10WRMPP12


    10WRMPP12


    10WRMPP12 4689736
    54271
    10WRMPP12 4689736

    10WRMPP12 4689736

    10WRMPP12
    897459 54271
    10WRMPP12
    897459
    10WRMPP12
    897459
    10WRMPP12
    897459 54271
    10WRMPP12
    897459
    10WRMPP12
    897459
    10WRMPP12 4689736 897459 54271
    10WRMPP12 4689736 897459
    10WRMPP12 4689736 897459
    10WRMPP12

    54271
    10WRMPP12


    10WRMPP12


    10WRMPP12

    54271
    10WRMPP12


    10WRMPP12


    10WRMPP12 4689736
    54271
    10WRMPP12 4689736

    10WRMPP12 4689736



    Any thoughts.

    Regards
    Gene

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Any thoughts
    Your data isn't the same as what you posted and I used?
    Doc Commit Obligation Expense
    10WRMPP12 4689736
    10WRMPP12 897459
    10WRMPP12 54271

    Or, the blanks are not Null - they are zls (empty strings). If you don't know how to find out: create a query that uses "" as criteria in every field you want to check. Any zls's returned will still appear blank, but they are not null.

    Did you consider the suggestion to eliminate the Nulls (which might not be Null)?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Presuming each Doc will have no more than 3 records and only 1 of the 3 amount fields of each record has numeric data, an aggregate query should compress the records.

    SELECT Doc, Max(Commit) AS DocCom, Max(Obligation) AS DocObl, Max(Expense) AS DocExp FROM Tablename GROUP BY Doc;

    However, there cannot be empty string in fields because any string value will sort after numbers and return as Max(). So do a Replace() operation to make sure there are no empty strings in any fields. Replace with Null (or 0). Null sorts before anything else.

    I NEVER allow empty strings in fields.
    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.

  11. #11
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Quote Originally Posted by Micron View Post
    Your data isn't the same as what you posted and I used?
    Doc Commit Obligation Expense
    10WRMPP12 4689736
    10WRMPP12 897459
    10WRMPP12 54271

    Or, the blanks are not Null - they are zls (empty strings). If you don't know how to find out: create a query that uses "" as criteria in every field you want to check. Any zls's returned will still appear blank, but they are not null.

    Did you consider the suggestion to eliminate the Nulls (which might not be Null)?
    Micron: Bingo..thanks a mil. The file comes from another database which I pull down into excel and then into access to run a query. Did not realize that what I thought were blank cells had something in them. I will also consider the suggestion to eliminate the Nulls.

    Doc Commit Obligation Expense
    10WRMPP12 4689736 897459 54271


    Thanks Again

  12. #12
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Thank you for your response. Will try this also.

    Regards

    Gene

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    curious as to how you made that work. Did you write it so that it would work on "" and Null, or just ""?

  14. #14
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Quote Originally Posted by Micron View Post
    Your data isn't the same as what you posted and I used?
    Doc Commit Obligation Expense
    10WRMPP12 4689736
    10WRMPP12 897459
    10WRMPP12 54271

    Or, the blanks are not Null - they are zls (empty strings). If you don't know how to find out: create a query that uses "" as criteria in every field you want to check. Any zls's returned will still appear blank, but they are not null.

    Did you consider the suggestion to eliminate the Nulls (which might not be Null)?
    I actually reformatted the excel file and on my import spec put the commit, obligation and expense fields as double. I am going to try the Replace() operation in access so that I don't have to worry about reformatting the excel file. I could also do a find and replace on blank cells in the excel file and put in zero's and then use criteria in the query >0. I also created the query that uses "" as criteria and saw where fields still appeared blank but they where not null.

    This was a great exercise for me and learned something new.

    Again thanks for your help..much appreciated.

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    NP. Glad you got it solved, though I'm not understanding how you could get away with specifying the target field is a double yet the source file is a zls. You could also modify the query that was looking for Null so that it looks for "" - or either. Or convert the "" to 0 as you said and alter the query to <> 0.
    There's always more than one way to do anything, I suppose.
    Good luck with your project.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-15-2017, 08:44 AM
  2. Cross Tab Query
    By Tammy in forum Queries
    Replies: 4
    Last Post: 05-29-2015, 09:58 AM
  3. Cross Tab Query
    By vishal09 in forum Access
    Replies: 3
    Last Post: 04-04-2015, 10:52 AM
  4. Cross Tab Query
    By RycherX in forum Queries
    Replies: 1
    Last Post: 04-26-2010, 12:19 PM
  5. Cross Tab Query
    By nengster in forum Queries
    Replies: 0
    Last Post: 02-18-2009, 07:12 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