Report Formatting

[Guest]

I have a client that is very demanding and want me to create a report to her
exact specifications; however, I have not been able to fix one of the
formating items she requested.

I collect counts or records which are "Total", "Open", and "Closed" along
with the "Office" and a "Reporting Period".

I created 3 queries to get my specific counts based upon "Office" and "
Reporting Period.

The client wish for me to add a "Comment" for the "Office" as a whole. I
created a table and store the "Office" name value and a memo field for
comments.

I can't seem to get the one comment to display on the screen over all of the
records for the office.

Example(What I want):
Office Reporting Period Total Closed Open Comment
Office1 3rd QTR FY2007 6 3 3 This is my comment it
should
Office1 2nd QTR FY2007 6 2 4 wrap to the next line and
not
Office1 1st QTR FY2007 6 0 6 repeat the comment for
each row.

Example(What I get):
Office Reporting Period Total Closed Open Comment
Office1 3rd QTR FY2007 6 3 3 This is my comment repeats.
Office1 2nd QTR FY2007 6 2 4 This is my comment repeats.
Office1 1st QTR FY2007 6 0 6 This is my comment
repeats.


Any suggestions would be greatly appreciated.

 

[Guest]

Try this --
Use Sorting and Grouing on Office. Set Hide Duplicates to yes for the
Comment textbox.

 

[Guest]

I tried that before posting, but it doesn't do what I want.

The output doing as you suggest looks like this:

Office Reporting Period Total Closed Open Comment
Office1 3rd QTR FY2007 6 3 3 This is my comment it
should
wrap to the next line and
repeat the
comment for not
repeat the
comment for
each row.
Office1 2nd QTR FY2007 6 2 4
Office1 1st QTR FY2007 6 0 6

I want the comment value to display across the three rows; otherwise, it
appears that the comments are for one "Reporting Period" and not a generic
comment for the "Office".

 

[Guest]

Try this --
Use Sorting and Grouing on Office. Put the Comment in the Group Footer.

If this does not work then post the SQL for your query.

 

[Guest]

The comments are stored in a separate table, so when I put the query together
it places the same comment in each of the records created in the query for
the same "Office".

SELECT [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period], [MIC Count Summary].Total, [MIC Count
Summary].[Completed/Closed], [MIC Count Summary].Remaining, [SA Initial
Completion Date Collection].[Original Estimated Completion Date], [SA Current
Completion Date Collection].[Current Estimated Completion Date], [SA
Monitoring Report Comments].Comment
FROM (([MIC Count Summary] LEFT JOIN [SA Initial Completion Date Collection]
ON ([MIC Count Summary].Office = [SA Initial Completion Date
Collection].Office) AND ([MIC Count Summary].FY = [SA Initial Completion Date
Collection].FY)) LEFT JOIN [SA Current Completion Date Collection] ON ([MIC
Count Summary].Office = [SA Current Completion Date Collection].Office) AND
([MIC Count Summary].FY = [SA Current Completion Date Collection].FY)) LEFT
JOIN [SA Monitoring Report Comments] ON [MIC Count Summary].Office = [SA
Monitoring Report Comments].Office
ORDER BY [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period] DESC;

 

[Guest]

What was the results when you put the Comment in the Group Footer?

--
KARL DEWEY
Build a little - Test a little

The comments are stored in a separate table, so when I put the query together
it places the same comment in each of the records created in the query for
the same "Office".

SELECT [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period], [MIC Count Summary].Total, [MIC Count
Summary].[Completed/Closed], [MIC Count Summary].Remaining, [SA Initial
Completion Date Collection].[Original Estimated Completion Date], [SA Current
Completion Date Collection].[Current Estimated Completion Date], [SA
Monitoring Report Comments].Comment
FROM (([MIC Count Summary] LEFT JOIN [SA Initial Completion Date Collection]
ON ([MIC Count Summary].Office = [SA Initial Completion Date
Collection].Office) AND ([MIC Count Summary].FY = [SA Initial Completion Date
Collection].FY)) LEFT JOIN [SA Current Completion Date Collection] ON ([MIC
Count Summary].Office = [SA Current Completion Date Collection].Office) AND
([MIC Count Summary].FY = [SA Current Completion Date Collection].FY)) LEFT
JOIN [SA Monitoring Report Comments] ON [MIC Count Summary].Office = [SA
Monitoring Report Comments].Office
ORDER BY [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period] DESC;

Try this --
Use Sorting and Grouing on Office. Put the Comment in the Group Footer.

If this does not work then post the SQL for your query.

 

[Guest]

The comments were displayed in the Office footer...

Office Reporting Period Total Open Closed
Office1 1st QTR FY06 10 6 4
Office1 2nd QTR FY06 10 6 4
This is what I don't want to have.

not

Office Reporting Period Total Open Closed
Office1 1st QTR FY06 10 6 4 This is what
Office1 2nd QTR FY06 10 6 4 I want.

What was the results when you put the Comment in the Group Footer?

--
KARL DEWEY
Build a little - Test a little

The comments are stored in a separate table, so when I put the query together
it places the same comment in each of the records created in the query for
the same "Office".

SELECT [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period], [MIC Count Summary].Total, [MIC Count
Summary].[Completed/Closed], [MIC Count Summary].Remaining, [SA Initial
Completion Date Collection].[Original Estimated Completion Date], [SA Current
Completion Date Collection].[Current Estimated Completion Date], [SA
Monitoring Report Comments].Comment
FROM (([MIC Count Summary] LEFT JOIN [SA Initial Completion Date Collection]
ON ([MIC Count Summary].Office = [SA Initial Completion Date
Collection].Office) AND ([MIC Count Summary].FY = [SA Initial Completion Date
Collection].FY)) LEFT JOIN [SA Current Completion Date Collection] ON ([MIC
Count Summary].Office = [SA Current Completion Date Collection].Office) AND
([MIC Count Summary].FY = [SA Current Completion Date Collection].FY)) LEFT
JOIN [SA Monitoring Report Comments] ON [MIC Count Summary].Office = [SA
Monitoring Report Comments].Office
ORDER BY [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period] DESC;

Try this --
Use Sorting and Grouing on Office. Put the Comment in the Group Footer.

If this does not work then post the SQL for your query.

 

[Guest]

I have not ever done it but try using two columns.
1. Create a report and open it in Design view.
2. On the File menu, click Page Setup.
3. In the Page Setup dialog box, click the Columns tab.
Under Grid Settings, in the Number Of Columns box, type the number of
columns you want on each page.
4. In the Row Spacing box, type the amount of vertical space you want
between each record in the detail section.
--
KARL DEWEY
Build a little - Test a little

The comments were displayed in the Office footer...

Office Reporting Period Total Open Closed
Office1 1st QTR FY06 10 6 4
Office1 2nd QTR FY06 10 6 4
This is what I don't want to have.

not

Office Reporting Period Total Open Closed
Office1 1st QTR FY06 10 6 4 This is what
Office1 2nd QTR FY06 10 6 4 I want.

What was the results when you put the Comment in the Group Footer?

--
KARL DEWEY
Build a little - Test a little

The comments are stored in a separate table, so when I put the query together
it places the same comment in each of the records created in the query for
the same "Office".

SELECT [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period], [MIC Count Summary].Total, [MIC Count
Summary].[Completed/Closed], [MIC Count Summary].Remaining, [SA Initial
Completion Date Collection].[Original Estimated Completion Date], [SA Current
Completion Date Collection].[Current Estimated Completion Date], [SA
Monitoring Report Comments].Comment
FROM (([MIC Count Summary] LEFT JOIN [SA Initial Completion Date Collection]
ON ([MIC Count Summary].Office = [SA Initial Completion Date
Collection].Office) AND ([MIC Count Summary].FY = [SA Initial Completion Date
Collection].FY)) LEFT JOIN [SA Current Completion Date Collection] ON ([MIC
Count Summary].Office = [SA Current Completion Date Collection].Office) AND
([MIC Count Summary].FY = [SA Current Completion Date Collection].FY)) LEFT
JOIN [SA Monitoring Report Comments] ON [MIC Count Summary].Office = [SA
Monitoring Report Comments].Office
ORDER BY [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period] DESC;




:

Try this --
Use Sorting and Grouing on Office. Put the Comment in the Group Footer.

If this does not work then post the SQL for your query.

 

[Guest]

I played with it for a while, but it didn't even come close.

It sees the entire row of data and wraps the data string. Meaning...Since
the Comments are returned as a repeating value for the same "Office", it sees
the comments as part of a single record. The result was like:

Office Reporting Period Total Open Closed
Office1 1st QTR FY06 10 6 4 Office1 2nd QTR FY07
10 6 4
Office1 2nd QTR FY06 10 6 4 Office1 3rd QTR FY07
10 6 4
Office1 1st QTR FY07 10 6 4 place
With all my Comments all over the

I have not ever done it but try using two columns.
1. Create a report and open it in Design view.
2. On the File menu, click Page Setup.
3. In the Page Setup dialog box, click the Columns tab.
Under Grid Settings, in the Number Of Columns box, type the number of
columns you want on each page.
4. In the Row Spacing box, type the amount of vertical space you want
between each record in the detail section.
--
KARL DEWEY
Build a little - Test a little

The comments were displayed in the Office footer...

Office Reporting Period Total Open Closed
Office1 1st QTR FY06 10 6 4
Office1 2nd QTR FY06 10 6 4
This is what I don't want to have.

not

Office Reporting Period Total Open Closed
Office1 1st QTR FY06 10 6 4 This is what
Office1 2nd QTR FY06 10 6 4 I want.

What was the results when you put the Comment in the Group Footer?

--
KARL DEWEY
Build a little - Test a little


:

The comments are stored in a separate table, so when I put the query together
it places the same comment in each of the records created in the query for
the same "Office".

SELECT [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period], [MIC Count Summary].Total, [MIC Count
Summary].[Completed/Closed], [MIC Count Summary].Remaining, [SA Initial
Completion Date Collection].[Original Estimated Completion Date], [SA Current
Completion Date Collection].[Current Estimated Completion Date], [SA
Monitoring Report Comments].Comment
FROM (([MIC Count Summary] LEFT JOIN [SA Initial Completion Date Collection]
ON ([MIC Count Summary].Office = [SA Initial Completion Date
Collection].Office) AND ([MIC Count Summary].FY = [SA Initial Completion Date
Collection].FY)) LEFT JOIN [SA Current Completion Date Collection] ON ([MIC
Count Summary].Office = [SA Current Completion Date Collection].Office) AND
([MIC Count Summary].FY = [SA Current Completion Date Collection].FY)) LEFT
JOIN [SA Monitoring Report Comments] ON [MIC Count Summary].Office = [SA
Monitoring Report Comments].Office
ORDER BY [MIC Count Summary].FY, [MIC Count Summary].Office, [MIC Count
Summary].[Reporting Period] DESC;




:

Try this --
Use Sorting and Grouing on Office. Put the Comment in the Group Footer.

If this does not work then post the SQL for your query.