1. ## 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. ## 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. ## 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. ## RE: Inverting Table Info

H.:
"" Oh-- Each date is in its own field.
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. ## 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. ## 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. ## 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. ## RE: Inverting Table Info

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

9. ## RE: Inverting Table Info

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

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. ## RE: Inverting Table Info

Operations tab, new, crosstab operation

see design screenshot attached

11. ## RE: Inverting Table Info

Then the results

see attched screenshot

12. ## RE: Inverting Table Info

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

13. ## RE: Inverting Table Info

Hey -- That works!

Thank you very much!

#### Posting Permissions

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