Solved How to obtain the value of the first column in report? (1 Viewer)

[yunhsuan]

Hello~

There are four field, AutoNo, No, Price1 and Price2, in my table. I order this table according to the "No" field and show them in the report. Then, DFirst Fn was used to calculate the first value of Price1 + the first value of Price2, and it was filled into the cell in header.
I expect the first value is related to the smallest No instead of the smallest AutoNo. For example, I hope the value of Price1+Price2=130+1870=2000 but not 100+900=1000.
How can I obtain the first value in the report? In this case, I hope the results of the first value are 130 and 1870.

Thanks in advance!

 

[theDBguy]

One way is to create a query sorted in the same way as the report and then use it to look up the values for your textbox.

 

[The_Doc_Man]

You have five detail items and wish to select only one of them. It is, of course, your DB and your business rules, but it is unclear why you wanted a particular one of those values vs. another. What would you do if you had only four values? Or if you had six?

In the report, you are seeing things in order of the autonumber because that is the primary key. Your underlying query ALSO orders by the PK, so you are seeing things in PK order, not NO order.

However, PK order doesn't matter here anyway. Your report has specified no particular grouping or sorting order and (as it turns out) Access does not honor the order of the source table or query for reports (due to such things as report-based grouping and sorting).

In your DB, you have an unbound control for this report's value (which is actually OK) but then have nothing at all in the section Event code where you would normally compute this unbound value. You have no code in ANY section, for that matter, so I don't see a DFirst anywhere. Besides which, DFirst is mostly useless because if used on a table, the order of records is TOTALLY unpredictable anyway. Therefore, a DFirst would see prices for the first record of the data source, whatever it is.

You are putting a value in the header based on fields that will be shown in the detail section. Looks like the order-by of the detail section worked - which made the "first" record of the actual data set into the LAST record of the section.

Perhaps you can try to explain the actual business requirements (including but not limited to WHY you wanted the prices from a particular record shown in the group header.)

 

[SHANEMAC51]

How can I obtain the first value in the report? In this case, I hope the results of the first value are 130 and 1870

the report does not use query sorting, so you need to
1- set sorting by data/time field on the grouping button
2 the result in the PP inscription uses the data of the first page entry, the code is in the figure, specially made 2 pages

of the event - formatting the header

 

[Pat Hartman]

create a query sorted the same way as the report and add a Top 1 predicate.

Select Top 1 fld1, fld2, (fld3 + fld4) as tot
From yourtable
Order By fld1;

The query will return the first record from the sorted recordset.

 

[theDBguy]

create a query sorted the same way as the report and add a Top 1 predicate.

Select Top 1 fld1, fld2, (fld3 + fld4) as tot
From yourtable
Order By fld1;

The query will return the first record from the sorted recordset.

That's a better description of what I was thinking. Thanks!

 

[yunhsuan]

Thanks for your suggestions! I got the point!

 

[Pat Hartman]

You're welcome