Results 1 to 4 of 4
  1. #1
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    26

    Cross Tab maybe not sure...

    Hi Folks

    I need some help I am getting a csv file of some data and its not in the format I want. I want to take that csv import it into a table and then manipulate that data and make a new table with it.

    My information looks like this...

    My header column the numbers 0 through 23 represent hours of the day 0 being midnight

    CUSTOMERNUMBER,Date,0,1,2,3,4,5,6,7,8,9,10,11,12,1 3,14,15,16,17,18,19,20,21,22,23
    100002,20160208,2.359,2.599,2.702,2.827,2.59,2.959 ,3.283,3.443,2.981,3.204,6.099,2.53,2.479,3.818,11 .269,8.183,5.817,4.555,2.042,4.052,2.481,3.337,6.2 79,3.732
    100003,20160208,2.228,2.055,2.304,2.069,2.257,1.38 ,2.191,1.426,2.212,1.509,1.995,1.474,1.517,0.459,1 .552,1.755,5.165,0.965,2.669,0.614,0.567,1.247,4.9 36,3.864
    100005,20160208,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0
    100006,20160208,3.127,2.214,2.367,2.781,2.734,3.10 1,2.726,3.191,2.302,4.514,3.202,3.623,2.75,2.168,2 .053,3.199,2.634,2.384,1.943,3.644,2.487,3.831,3.5 04,2.252

    I need it to look more like this.

    CustomerNumber, Date & HOUR,Usage
    100002,20160208 0:00,2.359
    100002,20160208 1:00,2.599
    100002,20160208 2:00,2.827
    100002,20160208 3:00,2.59

    ect.....

    Seems like something a query could do then just make it a make table query and so on...

    Your help is appreciated.



    Thank you
    CapJLP

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,180
    If it's a one-time thing, I'd run a bunch of append queries, each getting a different column. If it's an ongoing thing, you can create a UNION query that pulls the data into the format you're looking for.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    26
    Quote Originally Posted by pbaldy View Post
    If it's a one-time thing, I'd run a bunch of append queries, each getting a different column. If it's an ongoing thing, you can create a UNION query that pulls the data into the format you're looking for.
    Its a on going process. I haven't' had to much luck with UNION queries working in Access. I have used them in ORACLE before and they seem fine. Do you have a suggestion on what it should look like?

    I think you mean something like this.

    SELECT [FME-Hourly].[CustomerNumber], [FME-Hourly]![Date] & " " & "0:00" AS [DateTime], [FME-Hourly].[0] AS [Usage]
    FROM [FME-Hourly];
    Union
    SELECT [FME-Hourly].[CustomerNumber], [FME-Hourly]![Date] & " " & "1:00" AS [DateTime], [FME-Hourly].[1] AS [Usage]
    FROM [FME-Hourly];



    Thank you

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,180
    Yes, something like that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. cross table
    By gad10 in forum Queries
    Replies: 3
    Last Post: 12-28-2013, 06:31 AM
  2. Cross Tab & relationships
    By tomburns1987 in forum Queries
    Replies: 2
    Last Post: 02-01-2013, 01:53 AM
  3. Problem with Cross qry
    By fcarboni in forum Access
    Replies: 1
    Last Post: 05-11-2012, 01:51 PM
  4. Cross Tab Query.
    By cap.zadi in forum Queries
    Replies: 1
    Last Post: 01-04-2012, 01:29 PM
  5. Cross tab help
    By A S MANN in forum Queries
    Replies: 5
    Last Post: 10-28-2010, 07:46 AM

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