Alpha Video Training
Results 1 to 13 of 13

Thread: Inverting Table Info

  1. #1
    H. Donnell
    Guest

    Default Inverting Table Info

    Say I have the following table:

    Wash Car Go Shopping Go to Movies

    Mary 7/7/2005 7/2/2005 7/3/2005
    Jane 7/3/2005 7/3/2005 7/8/2005
    Bob 7/9/2005 7/8/2005 7/6/2005

    And I want the convert all this info into the following list or table (by way of a Report or something):

    7/2/2005 Go Shopping (Mary)
    7/3/2005 Wash Car (Jane)
    7/3/2005 Go to Movies (Mary)
    7/3/2005 Go Shopping (Jane)
    7/6/2005 Go to Movies (Bob)
    7/7/2005 Wash Car (Mary)
    7/8/2005 Go to Movies (Jane)
    7/8/2005 Go Shopping (Bob)
    7/9/2005 Wash Car (Bob)

    Is there a simple way to do this? Seems like it would be a standard relational database thing to do, but I can't figure out how in AF6. TIA

  2. #2
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default RE: Inverting Table Info

    H.:
    You have not provided the fields these values are stored in. Assuming the dates, all these dates, are stored in the same field, you need to create an index or a query based on that field and sort it whichever way you want.

    In fact, you could do better than that. If you are designing a report, you could break it down by the date, then by the person by using groups so you would end up with a report that says:
    1/1/05:
    Mary:
    1-Pretend to cook breakfast
    2-Take the only family car and go Shopping
    3-Shop some more
    Jack:
    1-Clean the dishes, put out the garbage
    2-Find where is Mary
    3-Cancel all your credit cards before it's too late
    1/2/05:
    Mary:
    1-Run off to Albaquerque
    2-Call the police
    and so on..

    Or you could break it up by the person then the date:
    Mary:
    1/1/05:
    1-Pretend to cook breakfast
    2-Take the only family car and go Shopping
    3-Shop some more
    1/2/05:
    1-Run off to Albaquerque
    2-Call the police
    Jack:
    1/1/05:
    1-Find Mary
    2-File for divorece

    Gabe

  3. #3
    H. Donnell
    Guest

    Default RE: Inverting Table Info

    Oh-- Each date is in its own field. And each person is in its own field. And each activity is in its own field. They are in a table with 4 columns and 3 rows. (All my spacing came out weird because of the way the message board handles text).

    I think I understand what you're saying about groups, but that's not what I'm aiming to do. I'm aiming specifically for all the dates to end up being in a column on the left and, if it makes for sense, for the activities to be in the next column to the right and persons in the next column to the right of that.

  4. #4
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default RE: Inverting Table Info

    H.:
    "" Oh-- Each date is in its own field.
    What is your table structure??
    What fields do you have?

    ""I'm aiming specifically for all the dates to end up being in a column on the left and, if it makes for sense, for the activities to be in the next column to the right and persons in the next column to the right of that.""

    That would be a browse table..
    Gabe

  5. #5
    H. Donnell
    Guest

    Default RE: Inverting Table Info

    Oh - The structure looks like this:

    Wash_Car Date 8
    Go_Shopping Date 8
    Go_to Movies Date 8


    And each name (Mary, Jane, Joe) is a just a new Record in a Form.

  6. #6
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default RE: Inverting Table Info

    I would restructure this to look like:(these are the fields of the table)
    Person C 20
    Activity C 20
    Date D

    For activity either through a lookup or other means allow only those activities you want to allow.

    You can then sort on person or date or activity. This could be done most easily with 3 indices.

    In this format you will find it a lot easier to extract the information that you want.

    Russ

  7. #7
    H. Donnell
    Guest

    Default RE: Inverting Table Info

    Hmmm ... OK - I experimented with fields the way you suggested, and yes, it will give me the output I was looking for --- but from here, what if I want to make a chart like the one I started out with in my original question:

    _________Wash Car_____Go Shopping___Go to Movies

    Mary____ 7/7/2005_____ 7/2/2005______ 7/3/2005
    Jane_____7/3/2005______7/3/2005_______7/8/2005
    Bob______7/9/2005______7/8/2005_______7/6/2005

    How would I set up a Report to do that?

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Inverting Table Info

    Crosstab person and activity, choose the date field to summarize.

  9. #9
    H. Donnell
    Guest

    Default RE: Inverting Table Info

    Sorry -- I'm a newb, if you can't tell...

    Step by step please ...

    Do you mean use the Quick Report Genie, choose Summary, move my 3 fields into the "Fields" column, and select Group and group Person and Activity?

    (I tried that, and it gave an odd result.)

  10. #10
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Inverting Table Info

    Operations tab, new, crosstab operation

    see design screenshot attached

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Inverting Table Info

    Then the results

    see attched screenshot

  12. #12
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Inverting Table Info

    Sorry, then you design your report to be based on the resulting table created by the crosstab operation.

  13. #13
    H. Donnell
    Guest

    Default RE: Inverting Table Info

    Hey -- That works!

    Thank you very much!

Similar Threads

  1. TABLE SUMMARY INFO
    By Carol Shelleman in forum Alpha Five Version 5
    Replies: 5
    Last Post: 08-17-2005, 07:31 AM
  2. Any info on pricing for V6
    By Jason Edwards in forum Alpha Five Version 5
    Replies: 5
    Last Post: 07-23-2004, 07:28 PM
  3. selecting detail info from a child table
    By javadad in forum Alpha Five Version 5
    Replies: 2
    Last Post: 05-02-2004, 03:55 PM
  4. set.zip() info???
    By Scott Emerick in forum Alpha Five Version 5
    Replies: 2
    Last Post: 09-27-2002, 09:14 AM
  5. Table-Set Info please
    By David Day in forum Alpha Five Version 5
    Replies: 5
    Last Post: 08-03-2002, 02:01 PM

Bookmarks

Posting Permissions

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