Report showing min, max and avg (1 Viewer)

tmyers

Well-known member
Local time
Today, 10:13
Joined
Sep 8, 2020
Messages
1,090
I am currently reading up on this, but wanted others input on it as well.
I am trying to create a report for pricing history for an item. I am going to have a form with a text box, then use the value entered in the text box passed into some VBA as a string to search my product table to return all products matching the entered value. So a user could enter "LCA" an all items containing (I will probably do starting with) "LCA" will be returned. I then want to show all pricing for that item to populate the report. So every match of "LCA" will appear on the report along with that records price.

At the very top of the report, I would like to have a lowest, highest and average then the details would be all instances of the item and its price. This report will almost never be printed, so an argument could be made to just make this a form, but I could foresee someone wanting to print just the first page. So with that in mind, I think a report would be a better fit.

Would I just use text boxes and put formulas in them? Such as Min, Max, Avg?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:13
Joined
Oct 29, 2018
Messages
21,358
Yes, you can use DMin(), DMax(), and Davg().
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:13
Joined
Feb 19, 2013
Messages
16,555
or have it as a subform with a recordsource of an aggregate query to show Min, Max, Avg
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:13
Joined
Feb 19, 2002
Messages
42,983
There is no need to use domain functions for this. You can simply use Min(), Max(), and Avg() as long as you can summarize the data.

Select ProductID, Avg(Price) as AvgPrice, Min(Price) as MinPrice, Max(Price) as MaxPrice
From YourTable
Where SaleDate Between Forms!frmyourform!StartDate AND Forms!frmyourform!EndDate
Group by ProductID

= just one example. The query selects sales within a specific time frame but doesn't include the date in the selected record.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:13
Joined
Jan 20, 2009
Messages
12,849
Would I just use text boxes and put formulas in them? Such as Min, Max, Avg?

Yes.

There is no need to use domain functions, separate queries, subforms or subreports.

Simply put textboxes in the header with the ControlSource like this:
Code:
=Max(fieldname)

This returns the Max() of whatever values are in that field of the SourceObject's Recordset.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:13
Joined
May 7, 2009
Messages
19,175
sample form.
 

Attachments

  • sampleLCA.accdb
    456 KB · Views: 115

Users who are viewing this thread

Top Bottom