Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Splitting out Listing Tables

    I am trying to work out how I can take a table of data that lists AddressID,
    LetterNumber & LetterDate in that form to this way

    AddressID Letter1 Letter1Date AddressID Letter2 Letter2Date AddressID
    Letter3 Letter3Date

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for use where?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I need to show in a query the dates of Letter1 2 3 and add to an existing query showing other information

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and where are you using that queries results i a form, report, more than one place?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I'm using it in a query. The problem is that I have another table which I need to extract data from is by column and not by row as normal database works. So I need to extract the data from the table and make a query where it puts the fields I need into columns

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    If I understand correctly, the row-major table you have is fine. What you want to do is have the column-major table conform to this. I do this in some of my reports as well.

    If this is so, leave your row-major table alone. What you want to do is open the column-major table in VBA, and read the whole thing into an array. If your table contains 500 records, and each record contains 5 required fields, you might say

    Code:
    Dim AryTbl(500,5) As String (or whatever)
    When you're finished reading in the entire table, close it. Now open a new table (design it ahead of time, so you'll know exactly the field locations) in VBA and read from the array. Put the data into the new table field by field, record by record.

    Read up on arrays in the Help if necessary.

    Hope this helps,

    Sam

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by JezLisle
    I'm using it in a query. The problem is that I have another table which I need to extract data from is by column and not by row as normal database works. So I need to extract the data from the table and make a query where it puts the fields I need into columns
    and where are you using that query, are you using once (in a form or report) or in several forms and reports.. IE do you need to use this frequently, is it a single report that requires this data

    Ie can the result you want be achieved by some BA smoke and mirrors in background on a form or report
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I will be using the query on a daily basis. It will be going into a report but not a report built as a form or report in Access. I will be extracting the data for someone to analysize.

    I need to do it this way as the end users dont have access to Access due to issues with an IT department at work.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How dynamic is the data? You say you have to query it on a daily basis, does this mean it needs to be calculate daily?

    If it's something you can complete as a batch process once per day, then you have some pretty easy VBA options that run in the same vein as Sam's suggestion. If it needs to be real-time things become more annoying.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Attached is a text file with the table of existing data.

    I need to run this query on a daily basis, to add along with other data into a report built elsewhere...

    Using this example this is what I want to do

    As Is
    "AddressID","LetterNum","LetterDate"
    "10172000407",1,6/5/2008 16:03:43
    "10172000407",2,13/5/2008 17:34:57

    What looking to do
    "AddressID","Letter1","Letter1Date","Letter2","Let ter2Date","Letter3","Letter3Date"
    "10172000407",1,6/5/2008 16:03:43,2,13/5/2008 17:34:57
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    has anyone any ideas on this?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is letterno always between 1 and 3? Never ever any other number?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'll assume so - just extend the logic
    Code:
    SELECT     AddressID
        , MAX(Switch(LetterNum = 1,1)) AS LetterNum1
        , MAX(Switch(LetterNum = 1,LetterDate)) AS LetterDate1
    ......
    FROM    mytable
    GROUP BY AddressID

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also assumes addressID and letternum combinations are unique....

  15. #15
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Yes there are no more than 3 letters Dates and maximum of 3 letter numbers.
    All AddressID as also unique.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •