Transpose data in a report-Field values broken down to separate co

  • Thread starter Thread starter Tammy
  • Start date Start date
T

Tammy

Hello,

I started this question in the queries group, and someone suggested using a
report, however, after trying a lot of various things, I can't figure out how
to display the data as needed. Here is the need:

My user has table data as follows:
ID Name Gift date Gift amt Gift acct
0001 John Doe 1/1/2007 $50.00 Annual Fund
0001 John Doe 1/1/2008 $75.00 Dean’s Fund
0002 Joe Smith 1/1/2008 $50.00 Annual Fund

She would like to create a report (or query) that displays the data in
columns labeled:
ID, Name, Gift date1, Gift amt1, Gift acct1, Gift date2, Gift amt2, Gift acct2

Some people have more than one record, some don't - some have >2 records. I
do not know how to get each individual record to display in a column with the
new label. Here was the suggestion from Duane, when I first thought I needed
a query, along with my response, that explains the need further:

"Is a report a possibility? If so, you can create a multi-column report to
display the results. If you really think you need a query, you would first
need to create a ranking query and then create a multi-value crosstab from
the ranking query."

I'm going to paste my response back:
"I think a report would work, as well. I'm not sure how to get the individual
records to display in fields with "new names": example, the field in the
table is called "Gift amt" - in a report, how would I then display record 1
as "Gift amt 1", record 2 for the same name to display as "Gift amt 2",
etc...

Same thing for the "Gift date" and "Gift acct"...

Each "name" can have more than one gift date, amt, and acct.

For each record, display the date, amt and acct under new lables - if
someone gave three gifts, I would need Access to create columns called "gift
date 1", "gift date 2", "gift date 3" (and "gift amt 1", gift amt 2", gift
amt 3" - as well as the gift accts).

I hope I'm explaining this well...

I wasn't sure what you meant by "ranking query" - if that sounds like what
I'll need, can you explain that one?"

I have tried a lot of queries and crosstab query variations, and have tried
getting creative with reports, but just can't figure this one out.

I'd appreciate any suggestions. I'm not VBA savvy, but my user is, so if
programming is the solution, I'd be happy to pass that info along to her.

We are using Access 2007.

Truly appreciate your help - thanks.
 
I would create a multi-column subreport of the Date, Amt and Acct fields.
Then create a main report based on only the unique values of ID and Name.
Drop the subreport into the detail section of the main report and set the
Link master/child properties to the ID or name.

BTW: name is not a good name for stuff since every object has a name property.
 
Hi Duane,
Thank you very much for your excellent advice! I am getting *real* close.

I have the columns going across for my gifts, but can't figure out how to
"break" a row for the next ID. For example, I have a donor who has only two
gift records. Therefore, the next ID's first record shows up on the same row
as the previous donor (to complete three columns of data). How can I force
Access to start a new row for each new ID? Grouping doesn't seem to work well
- it throws the whole layout off.

Also, my user wanted labels indicating the "number" of the gift: "Gift Date
1, Gift Date 2, etc. - anyway to work that little piece of magic? :)

Thanks again, for taking the time to answer my post, I really appreciate it!
 
How did you set up your main and subreport? Did you set the link master child
properties like I suggested?
 
Hi Duane,

Thanks for double checking that with me. I did forget to link based on the
"ID Number" for each account. It was currently set to "ID"- which is just an
autonumber. I'm not sure if I had linked on "ID", or if that was something
Access did automatically.

However, when I chose the "ID number" (which is each donor's unique ID, and
how to link the main report to the subreport) this did not fix the problem.
It actually created a new problem.

The subreport is set up to display rows as follows:
GiftDate/GiftAmount/GiftAccount - this repeats three times in the row (three
columns of data).

After changing my Link Master Child properties to "ID Number", when
previewing, at the start of the second column of data, the date label and
date field for the first record is overlapping with the second and third
column "GiftDate" data. Plus, a fourth column begins, which repeats the
GiftDate info for the first record in the row. Somewhere along the way, I
have the first record's gift data info repeating - but only the date field
repeats - not the amount or account(??). (and, again, it looks like I have
two fields on top of each other for the second and third column date date.)

I still have the same problem with the rows not breaking at each new ID
Number. If an ID Number does not have three columns of data, the next ID
Number begins on that same row.

I know this must be really confusing when you can't see the results, so I
hope I'm explaining this well enough. Please let me know what other
information I can provide. Any additional suggestions on where to check is
greatly appreciate.

Thanks!
 
Let's start from the beginning. Create a report with a record source of:
SELECT ID, [Name]
FROM tblGifts
GROUP BY ID, [Name];
This main report has only one column.

Create a multi-column subreport based on your table with a record source of:
SELECT *
FROM tblGifts;

It should look like:
Date Amt Acct
1/1/2007 $50.00 Annual Fund
1/1/2008 $75.00 Dean's Fund
1/1/2008 $50.00 Annual Fund

Set the Page Layout to the number of columns (three data fields) that will
fit across your page. Make sure the layout displays across then down.

Add this subreport to the detail section of your main report. Set the Link
Master/Child properties to ID.
 
Hi Duane,
Thanks for hanging in there with me.

Based on your helpful suggestions, I went about this a little differently. I
realized that my user had her ID's and AccountNames all listed in one table
with the gifts. She also has another table listing just the account names and
their ID's (tblProspects). I was having a lot of problems when it came to
grouping using only the table that included her donations. So, instead, I
created the main report based on her "Prospects" table, used IDNumber and
AccountName from that table, and grouped on IDNumber.

I then created the subreport using the Gifts table, and linked that report
to the main report on IDNumber.

I now have the report grouping properly - all rows are breaking at a new ID
regardless of how many "gifts" they have donated. yea!

So, two more questions:
1. There was an account name in the Prospects table that has no "gift"
records. His name appears on the report with no data displayed under his
account name. How do I stop records from displaying on the main report that
do not have a match in the subreport? I've looked throught the properties,
but nothing is jumping out at me. I was thinking about changing join
properties, but since I didn't use a query, not sure how to go about that.

2. Is there a way to go about changing the labels for each donatation to
include a number after it? (GiftDate1, GiftAmount1, GiftAlloc1; GiftDate2,
GiftAmount2, GiftAlloc2, etc.)

Thanks so much for all of your help on this!

Duane Hookom said:
Let's start from the beginning. Create a report with a record source of:
SELECT ID, [Name]
FROM tblGifts
GROUP BY ID, [Name];
This main report has only one column.

Create a multi-column subreport based on your table with a record source of:
SELECT *
FROM tblGifts;

It should look like:
Date Amt Acct
1/1/2007 $50.00 Annual Fund
1/1/2008 $75.00 Dean's Fund
1/1/2008 $50.00 Annual Fund

Set the Page Layout to the number of columns (three data fields) that will
fit across your page. Make sure the layout displays across then down.

Add this subreport to the detail section of your main report. Set the Link
Master/Child properties to ID.

--
Duane Hookom
Microsoft Access MVP


Tammy said:
Hi Duane,

Thanks for double checking that with me. I did forget to link based on the
"ID Number" for each account. It was currently set to "ID"- which is just an
autonumber. I'm not sure if I had linked on "ID", or if that was something
Access did automatically.

However, when I chose the "ID number" (which is each donor's unique ID, and
how to link the main report to the subreport) this did not fix the problem.
It actually created a new problem.

The subreport is set up to display rows as follows:
GiftDate/GiftAmount/GiftAccount - this repeats three times in the row (three
columns of data).

After changing my Link Master Child properties to "ID Number", when
previewing, at the start of the second column of data, the date label and
date field for the first record is overlapping with the second and third
column "GiftDate" data. Plus, a fourth column begins, which repeats the
GiftDate info for the first record in the row. Somewhere along the way, I
have the first record's gift data info repeating - but only the date field
repeats - not the amount or account(??). (and, again, it looks like I have
two fields on top of each other for the second and third column date date.)

I still have the same problem with the rows not breaking at each new ID
Number. If an ID Number does not have three columns of data, the next ID
Number begins on that same row.

I know this must be really confusing when you can't see the results, so I
hope I'm explaining this well enough. Please let me know what other
information I can provide. Any additional suggestions on where to check is
greatly appreciate.

Thanks!
Click to expand...
Click to expand...
 
Hi Duane,
I just spoke to my user, and she would like to keep the person who doesn't
have any gift records displayed in the report. She also is okay with the gift
labels not including a number after them. So, I think we are set!

Again, thank you sooo much for your time and patience. It has been truly
appreciated. Have a wonderful week.

Thanks!

Duane Hookom said:
Let's start from the beginning. Create a report with a record source of:
SELECT ID, [Name]
FROM tblGifts
GROUP BY ID, [Name];
This main report has only one column.

Create a multi-column subreport based on your table with a record source of:
SELECT *
FROM tblGifts;

It should look like:
Date Amt Acct
1/1/2007 $50.00 Annual Fund
1/1/2008 $75.00 Dean's Fund
1/1/2008 $50.00 Annual Fund

Set the Page Layout to the number of columns (three data fields) that will
fit across your page. Make sure the layout displays across then down.

Add this subreport to the detail section of your main report. Set the Link
Master/Child properties to ID.

--
Duane Hookom
Microsoft Access MVP


Tammy said:
Hi Duane,

Thanks for double checking that with me. I did forget to link based on the
"ID Number" for each account. It was currently set to "ID"- which is just an
autonumber. I'm not sure if I had linked on "ID", or if that was something
Access did automatically.

However, when I chose the "ID number" (which is each donor's unique ID, and
how to link the main report to the subreport) this did not fix the problem.
It actually created a new problem.

The subreport is set up to display rows as follows:
GiftDate/GiftAmount/GiftAccount - this repeats three times in the row (three
columns of data).

After changing my Link Master Child properties to "ID Number", when
previewing, at the start of the second column of data, the date label and
date field for the first record is overlapping with the second and third
column "GiftDate" data. Plus, a fourth column begins, which repeats the
GiftDate info for the first record in the row. Somewhere along the way, I
have the first record's gift data info repeating - but only the date field
repeats - not the amount or account(??). (and, again, it looks like I have
two fields on top of each other for the second and third column date date.)

I still have the same problem with the rows not breaking at each new ID
Number. If an ID Number does not have three columns of data, the next ID
Number begins on that same row.

I know this must be really confusing when you can't see the results, so I
hope I'm explaining this well enough. Please let me know what other
information I can provide. Any additional suggestions on where to check is
greatly appreciate.

Thanks!
Click to expand...
Click to expand...
 
Tammy,
Glad to hear you got all of this sorted out.

The method for numbering the donations in the subreport would involve using
a text box with a running sum set over group.

The main report record source I suggested earlier would limit the report to
only persons with donations.
--
Duane Hookom
Microsoft Access MVP


Tammy said:
Hi Duane,
I just spoke to my user, and she would like to keep the person who doesn't
have any gift records displayed in the report. She also is okay with the gift
labels not including a number after them. So, I think we are set!

Again, thank you sooo much for your time and patience. It has been truly
appreciated. Have a wonderful week.

Thanks!

Duane Hookom said:
Let's start from the beginning. Create a report with a record source of:
SELECT ID, [Name]
FROM tblGifts
GROUP BY ID, [Name];
This main report has only one column.

Create a multi-column subreport based on your table with a record source of:
SELECT *
FROM tblGifts;

It should look like:
Date Amt Acct
1/1/2007 $50.00 Annual Fund
1/1/2008 $75.00 Dean's Fund
1/1/2008 $50.00 Annual Fund

Set the Page Layout to the number of columns (three data fields) that will
fit across your page. Make sure the layout displays across then down.

Add this subreport to the detail section of your main report. Set the Link
Master/Child properties to ID.

--
Duane Hookom
Microsoft Access MVP


Tammy said:
Hi Duane,

Thanks for double checking that with me. I did forget to link based on the
"ID Number" for each account. It was currently set to "ID"- which is just an
autonumber. I'm not sure if I had linked on "ID", or if that was something
Access did automatically.

However, when I chose the "ID number" (which is each donor's unique ID, and
how to link the main report to the subreport) this did not fix the problem.
It actually created a new problem.

The subreport is set up to display rows as follows:
GiftDate/GiftAmount/GiftAccount - this repeats three times in the row (three
columns of data).

After changing my Link Master Child properties to "ID Number", when
previewing, at the start of the second column of data, the date label and
date field for the first record is overlapping with the second and third
column "GiftDate" data. Plus, a fourth column begins, which repeats the
GiftDate info for the first record in the row. Somewhere along the way, I
have the first record's gift data info repeating - but only the date field
repeats - not the amount or account(??). (and, again, it looks like I have
two fields on top of each other for the second and third column date date.)

I still have the same problem with the rows not breaking at each new ID
Number. If an ID Number does not have three columns of data, the next ID
Number begins on that same row.

I know this must be really confusing when you can't see the results, so I
hope I'm explaining this well enough. Please let me know what other
information I can provide. Any additional suggestions on where to check is
greatly appreciate.

Thanks!

:

How did you set up your main and subreport? Did you set the link master child
properties like I suggested?

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,
Thank you very much for your excellent advice! I am getting *real* close.

I have the columns going across for my gifts, but can't figure out how to
"break" a row for the next ID. For example, I have a donor who has only two
gift records. Therefore, the next ID's first record shows up on the same row
as the previous donor (to complete three columns of data). How can I force
Access to start a new row for each new ID? Grouping doesn't seem to work well
- it throws the whole layout off.

Also, my user wanted labels indicating the "number" of the gift: "Gift Date
1, Gift Date 2, etc. - anyway to work that little piece of magic? :)

Thanks again, for taking the time to answer my post, I really appreciate it!

:

I would create a multi-column subreport of the Date, Amt and Acct fields.
Then create a main report based on only the unique values of ID and Name.
Drop the subreport into the detail section of the main report and set the
Link master/child properties to the ID or name.

BTW: name is not a good name for stuff since every object has a name property.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I started this question in the queries group, and someone suggested using a
report, however, after trying a lot of various things, I can't figure out how
to display the data as needed. Here is the need:

My user has table data as follows:
ID Name Gift date Gift amt Gift acct
0001 John Doe 1/1/2007 $50.00 Annual Fund
0001 John Doe 1/1/2008 $75.00 Dean’s Fund
0002 Joe Smith 1/1/2008 $50.00 Annual Fund

She would like to create a report (or query) that displays the data in
columns labeled:
ID, Name, Gift date1, Gift amt1, Gift acct1, Gift date2, Gift amt2, Gift acct2

Some people have more than one record, some don't - some have >2 records. I
do not know how to get each individual record to display in a column with the
new label. Here was the suggestion from Duane, when I first thought I needed
a query, along with my response, that explains the need further:

"Is a report a possibility? If so, you can create a multi-column report to
display the results. If you really think you need a query, you would first
need to create a ranking query and then create a multi-value crosstab from
the ranking query."

I'm going to paste my response back:
"I think a report would work, as well. I'm not sure how to get the individual
records to display in fields with "new names": example, the field in the
table is called "Gift amt" - in a report, how would I then display record 1
as "Gift amt 1", record 2 for the same name to display as "Gift amt 2",
etc...

Same thing for the "Gift date" and "Gift acct"...

Each "name" can have more than one gift date, amt, and acct.

For each record, display the date, amt and acct under new lables - if
someone gave three gifts, I would need Access to create columns called "gift
date 1", "gift date 2", "gift date 3" (and "gift amt 1", gift amt 2", gift
amt 3" - as well as the gift accts).

I hope I'm explaining this well...

I wasn't sure what you meant by "ranking query" - if that sounds like what
I'll need, can you explain that one?"

I have tried a lot of queries and crosstab query variations, and have tried
getting creative with reports, but just can't figure this one out.

I'd appreciate any suggestions. I'm not VBA savvy, but my user is, so if
programming is the solution, I'd be happy to pass that info along to her.

We are using Access 2007.

Truly appreciate your help - thanks.
Click to expand...
Click to expand...
Click to expand...
 
Thanks so much for the tip about using a running sum. I'll keep that in mind
for next time! :)
Thanks!

Duane Hookom said:
Tammy,
Glad to hear you got all of this sorted out.

The method for numbering the donations in the subreport would involve using
a text box with a running sum set over group.

The main report record source I suggested earlier would limit the report to
only persons with donations.
--
Duane Hookom
Microsoft Access MVP


Tammy said:
Hi Duane,
I just spoke to my user, and she would like to keep the person who doesn't
have any gift records displayed in the report. She also is okay with the gift
labels not including a number after them. So, I think we are set!

Again, thank you sooo much for your time and patience. It has been truly
appreciated. Have a wonderful week.

Thanks!

Duane Hookom said:
Let's start from the beginning. Create a report with a record source of:
SELECT ID, [Name]
FROM tblGifts
GROUP BY ID, [Name];
This main report has only one column.

Create a multi-column subreport based on your table with a record source of:
SELECT *
FROM tblGifts;

It should look like:
Date Amt Acct
1/1/2007 $50.00 Annual Fund
1/1/2008 $75.00 Dean's Fund
1/1/2008 $50.00 Annual Fund

Set the Page Layout to the number of columns (three data fields) that will
fit across your page. Make sure the layout displays across then down.

Add this subreport to the detail section of your main report. Set the Link
Master/Child properties to ID.

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,

Thanks for double checking that with me. I did forget to link based on the
"ID Number" for each account. It was currently set to "ID"- which is just an
autonumber. I'm not sure if I had linked on "ID", or if that was something
Access did automatically.

However, when I chose the "ID number" (which is each donor's unique ID, and
how to link the main report to the subreport) this did not fix the problem.
It actually created a new problem.

The subreport is set up to display rows as follows:
GiftDate/GiftAmount/GiftAccount - this repeats three times in the row (three
columns of data).

After changing my Link Master Child properties to "ID Number", when
previewing, at the start of the second column of data, the date label and
date field for the first record is overlapping with the second and third
column "GiftDate" data. Plus, a fourth column begins, which repeats the
GiftDate info for the first record in the row. Somewhere along the way, I
have the first record's gift data info repeating - but only the date field
repeats - not the amount or account(??). (and, again, it looks like I have
two fields on top of each other for the second and third column date date.)

I still have the same problem with the rows not breaking at each new ID
Number. If an ID Number does not have three columns of data, the next ID
Number begins on that same row.

I know this must be really confusing when you can't see the results, so I
hope I'm explaining this well enough. Please let me know what other
information I can provide. Any additional suggestions on where to check is
greatly appreciate.

Thanks!

:

How did you set up your main and subreport? Did you set the link master child
properties like I suggested?

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane,
Thank you very much for your excellent advice! I am getting *real* close.

I have the columns going across for my gifts, but can't figure out how to
"break" a row for the next ID. For example, I have a donor who has only two
gift records. Therefore, the next ID's first record shows up on the same row
as the previous donor (to complete three columns of data). How can I force
Access to start a new row for each new ID? Grouping doesn't seem to work well
- it throws the whole layout off.

Also, my user wanted labels indicating the "number" of the gift: "Gift Date
1, Gift Date 2, etc. - anyway to work that little piece of magic? :)

Thanks again, for taking the time to answer my post, I really appreciate it!

:

I would create a multi-column subreport of the Date, Amt and Acct fields.
Then create a main report based on only the unique values of ID and Name.
Drop the subreport into the detail section of the main report and set the
Link master/child properties to the ID or name.

BTW: name is not a good name for stuff since every object has a name property.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I started this question in the queries group, and someone suggested using a
report, however, after trying a lot of various things, I can't figure out how
to display the data as needed. Here is the need:

My user has table data as follows:
ID Name Gift date Gift amt Gift acct
0001 John Doe 1/1/2007 $50.00 Annual Fund
0001 John Doe 1/1/2008 $75.00 Dean’s Fund
0002 Joe Smith 1/1/2008 $50.00 Annual Fund

She would like to create a report (or query) that displays the data in
columns labeled:
ID, Name, Gift date1, Gift amt1, Gift acct1, Gift date2, Gift amt2, Gift acct2

Some people have more than one record, some don't - some have >2 records. I
do not know how to get each individual record to display in a column with the
new label. Here was the suggestion from Duane, when I first thought I needed
a query, along with my response, that explains the need further:

"Is a report a possibility? If so, you can create a multi-column report to
display the results. If you really think you need a query, you would first
need to create a ranking query and then create a multi-value crosstab from
the ranking query."

I'm going to paste my response back:
"I think a report would work, as well. I'm not sure how to get the individual
records to display in fields with "new names": example, the field in the
table is called "Gift amt" - in a report, how would I then display record 1
as "Gift amt 1", record 2 for the same name to display as "Gift amt 2",
etc...

Same thing for the "Gift date" and "Gift acct"...

Each "name" can have more than one gift date, amt, and acct.

For each record, display the date, amt and acct under new lables - if
someone gave three gifts, I would need Access to create columns called "gift
date 1", "gift date 2", "gift date 3" (and "gift amt 1", gift amt 2", gift
amt 3" - as well as the gift accts).

I hope I'm explaining this well...

I wasn't sure what you meant by "ranking query" - if that sounds like what
I'll need, can you explain that one?"

I have tried a lot of queries and crosstab query variations, and have tried
getting creative with reports, but just can't figure this one out.

I'd appreciate any suggestions. I'm not VBA savvy, but my user is, so if
programming is the solution, I'd be happy to pass that info along to her.

We are using Access 2007.

Truly appreciate your help - thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top