Combine Records on Report

[Guest]

I would like to try and combine the contents of 20 controls into one record. The report currently may have 10 or more records; (20 controls each) spread horizontally (20 controls) and the 10 or more records down the page vertically.

Is there someway to reduce the 10 or more records down to one record which contains the most recently entered information for each of the 20 controls.

Any assistance greatly appreciated - Pete

 

[Duane Hookom]

Could you provide about 10 sample records and how you would expect to
display them on a report. This request is very unusual and not very clear.

--
Duane Hookom
MS Access MVP

I would like to try and combine the contents of 20 controls into one

record. The report currently may have 10 or more records; (20 controls
each) spread horizontally (20 controls) and the 10 or more records down the
page vertically.

Is there someway to reduce the 10 or more records down to one record which

contains the most recently entered information for each of the 20 controls.

 

[Duane Hookom]

The only ugly method I can think of is to use several lookups
=DLookup("FieldA","tblYourTable", "ID=" & DMax("ID","tblYourTable","FieldA
Is Not Null"))
=DLookup("FieldB","tblYourTable", "ID=" & DMax("ID","tblYourTable","FieldB
Is Not Null"))
--
Duane Hookom
MS Access MVP

Duane

Will attempt to show 5 records (DOWN - in the order the records were

created) with only 5 controls (ACROSS) (vice 20) for simplicity:

1 C NA 'NULL' F D
2 'NULL' 'NULL' N(1) C P
3 'NULL' 'NULL' UA 'NULL' UA
4 'NULL' 'NULL' C 'NULL' P(1)
5 'NULL' 'NULL' 'NULL' 'NULL' D

Trying to combine the above 5 records into one record on a report that would appear as:

1 C NA C C D

The desired result shown as just 1 above contains the most recent

information for each record. Note the 'NULL' means that the control
contains no data for that particular record. This leaves the most recent
information for each control as shown: C NA C C D

If this can be done, it will permit us to show the most recent data in one

horizontal line vice including all vertical records, where your eyes must
travel up and down (records) and across 20 controls to see the most recent
status of each control.

 

[Duane Hookom]

You would use these expressions as control sources in text boxes on the
report. They would be horrendously slow and ugly and would depend on having
the unique sequential number you show in the leftmost column.

--
Duane Hookom
MS Access MVP
--

Duane,

Sounds like I would need to make up 20 of the "=DLookup...etc..." to cover

all 20 controls in each record. Where do I create these
"=DLookup..etc...s"? Will they be created right in the report? Will they
return only the most recent data in the particular control? Some controls
only have data (no Nulls) and I only want the most recent data.