Format detail section with two records of information...

[lamarw]

(Sorry, this got attached to an already existing thread...)
Hi everyone,
I've got a question that I can find no usable answer for. I am creating a report that has two records worth of information in the same detail section.
In the table there records for regions 'North' and 'South'. In my report I want to display North's information in the left half of the detail section and South's information in the same detail section but on the right half. I want to display two records worth of information before the report CRLF's. A CRLF will occur only when the MyDate field changes closing the format for that section and printing that part of the report (MyDate is one of the field names on the report in both halves of the detail section).
When I select the data I am Ordering it by Date then Region.
When I'm done printing It will "appear" to be in columns, North on the left and South on the right (it will be more like spreadsheet columns rather than word processor columns, spreadsheet output is not neccessarily what I'm looking for though). Where one or the other is missing a record then the area will just remain unused(blank).
So, my question is: Can I force the read of another record (i.e. movenext) before I leave the formatting of the detail section? Or, can I keep the format of the detail section open while the report gets the next record?
I plan on going outside the report to a function during report detail format to individually load the textboxes based on the region in the record.
(I hope this is clear...)

...So far the responses are:

dhookom (Programmer) 24 Nov 06 2:49
Is there more than one record per date per region? Is there always at least one record per date per region?

If there are multiples, I would use two of the same subreport in the detail section of a report that contains only dates and regions.
Duane MS Access MVP
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

lamarw (MIS) 24 Nov 06 12:56
Q:Is there more than one record per date per region? Is there always at least one record per date per region?
A: One record per Date/Region. However, there will be two records per date but the differentiating factor is the region, therefore, always one record per date/REGION.

 

[dhookom]

From the other thread:
I would create a report based on a query like:

SELECT MyDate
FROM tblNoNameGiven
GROUP BY MyDate;

Add two text boxes to your detailed section:
Name: txtNorth
Control Source: ="North"

Name: txtSouth
Control Source: ="South"

Then create a subreport based on your table and place two copies of this subreport side-by-side in the detail section of your report and Set the Link Master/Child of one to:
Master:txtNorth,[MyDate]
Child: [Region],[MyDate]
and the other
Master:txtSouth,[MyDate]
Child: [Region],[MyDate]
The records in the subreports should then be filter as required.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lamarw]

Hi dhookom!
I attempted to use your method above as follows:
Created a report with a group heading for 'MyDate' (a field in the table). It's formatted to display as Month/Year only.
However, I can get the date from each record that will fall into the group. I use the group date as a guide to getting the record for each subreport, that's all.

In the 'Detail' section I have two identical subreports side-by-side. The 'RecordSource' for each subreport uses MyDate(in a text field, described above) from the group heading and the RegionID(hard coded in the SQL):
-------------------
SELECT *
FROM SCHED
WHERE (((SCHED.DATE)=[reports]![qrysched4]![text45]) AND ((SCHED.RegionID)=1));
-------------------
for the other region I use 2

Since there are two regions there will usually be two records 1 for region 1 and 1 for region 2 per date (sometimes there will be only one).

I am always printing two regions(records) in one detail section side-by-side. The report is formatting and printing both subreports for each record. Since I'm printing two records(subreports) per detail section I only need one detail section to print per two records *head is swimming*

In other words If I had one record for the east region for date 12/31/2006 and one record for the west region for date 12/31/2006(typical) I would want them to print side-by-side in the same detail section of the report, which they do, but then they print again due to the report processing two records within the group, undesireable end result, I only really need to print one detail section as both records will print there.

Is there a way to have the report stop the processing of the unneeded duplicate detail secdtion? Or is there a different way of going about this that gets to the desired end result?

 

[dhookom]

If I understand correctly, you only want one detail section per date. If this is correct, change the record source of the main report to a group by/totals query that only includes one record per date. This is what you should have gotten with the query:
SELECT MyDate
FROM tblNoNameGiven
GROUP BY MyDate;
Apparently you didn't use the link master/child properties for the RegionID...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]