min/max functions for unbounded field without query (1 Viewer)

[saqassemi]

Hello to every one,
This is a simple file and I want to find maximum of the total field (unbound field). I know it is possible in query to get total and max and use them in a form, but is it possible in a form without using a query?

 

[JHB]

Use the below instead:

=Max([score1]+[score2]+[score3])

 

[saqassemi]

Oh, very thanks. May you share a link that learn combination of access functions with samples.

 

[missinglinq]

Here's a comprehensive listing:

http://www.techonthenet.com/access/functions/

Double-Click on any Function to go to an explanation of it with examples.

Linq ;0)>

 

[Galaxiom]

Here's a comprehensive listing:
http://www.techonthenet.com/access/functions/

Not quite comprehensive. They didn't include Partition().

Double-Click on any Function to go to an explanation of it with examples.

It is displayed in a browser. Just a single click is required.

 

[The_Doc_Man]

JHB, linq, and Galaxiom -

Re-read saqasemmi's first post more carefully. He wants min/max WITHOUT a query based on something unbound. Min and Max are SQL Aggregates, not VBA functions. This is what MSDN says about MIN and MAX:

https://msdn.microsoft.com/en-us/vba/access-vba/articles/min-max-functions-microsoft-access-sql

The article expressly states: "You can use Min and Max in a query expression and in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query." Nowhere does it imply applicability to VBA expressions.

saqasemmi, read the thread found through this hyperlink:

https://stackoverflow.com/questions/1355521/no-maxx-y-function-in-access

In languages like FORTRAN and PASCAL, you have a MAX( a1, a2, a3, ...) that returns the largest value out of the arbitrary comma-delimited list in parentheses. Ditto, MIN. It is possible to build a VBA version of the MIN or MAX function with a little bit of ingenuity and some optional argument coding, but there is no extant function built into VBA to do this. The stackoverflow article provides for passage of the arbitrary list of arguments by using an array with no declared boundaries.

 

[JHB]

..
Re-read saqasemmi's first post more carefully. He wants min/max WITHOUT a query based on something unbound. Min and Max are SQL Aggregates, not VBA functions..

Exactly - without a query!

 

[missinglinq]

JHB, linq, and Galaxiom -

Re-read saqasemmi's first post more carefully.

I didn't need to re-read the post...just didn't have time to address the primary question!

As you noted, Max/Min is a SQL function...not a VBA function. To do what the OP apparently wants, he needs to use DMax/DMin. And because you cannot use Aggregate Functions directly against a Calculated Field, you have to use it against the expression that populates the Calculated Field, like this:

=DMin("[Score1] + [Score2] + [Score3]","Scores")

or

=DMax("[Score1] + [Score2] + [Score3]","Scores")


And to get this to update as Records are added...something like this:

Private Sub Form_Current()
 Me.Recalc
End Sub

Linq ;0)>

 

[saqassemi]

Mr JHB and other friend, thanks a lot for your attention.
I have another problem like this thread you solved for me.
I want to count fields with values for each record and then find max of them. Here I attached a file. If you open the query it finds the count and max of entered scores for students. But I want do it in the report without query but it shows ERROR.

 

[JHB]

When you've the value in the query, and your report is based on the query, why not use the value then?

 

[isladogs]

Looking at your database and the earlier example, you need to change the table structure so it is normalised.
What you have is a spreadsheet. Perfect if using Excel. Inappropriate for use in Access.

Use 2 tables:
TblStudents -StudentID (PK), FirstName, LastName ....
TblMarks- MarkID (PK), StudentID, (FK), Grade, TestName, Mark

Link the 2 tables using the StudentID field in a one to many relationship

This means each record contains a single mark for each student for one test only.
There will be a lot of records but that's fine.

A crosstab query is then used to get the layout you need for your report and it becomes a trivial matter to count test marks and find max values or averages.

 

[saqassemi]

When you've the value in the query, and your report is based on the query, why not use the value then?

Thanks for your attention.
Here I attached two files. in one the function works good but in the other one it shows ERROR.
Thanks for your advice but I must do it in a report in some reason.

 

[JHB]

I think you reach some limit in MS-Access.
But as ridders mention your table structure is not correct.

Thanks for your advice but I must do it in a report in some reason.

What is that reason, (is it a secret? )?

 

[isladogs]

Whatever your reason for doing this in a report, you can use a crosstab query as your report record source.

If you continue with your current table structure you will be fighting to do many tasks in Access that would be easy with a normalised table structure

 

[saqassemi]

Thanks all:
Dear JHB
no it is not secret. I summarized my posts.
I know it is possible to hide access tabs, navigation pane and disabling bypass but As you know tables and queries aren't secure and every one can import them (even they are hidden by using right click and navigation option) in a new blank database and change them and worst, export changed tables and queries in place of your database tables and queries. I found a code to really hide a table and doesn't delete my data after compacting:
Public sub hidetable(ByVal tblUserInformation As String)
Application.currentdb.tableDefs(tblUserInformation).Properties("Attributes").value = dbHiddenObject
But I didn't find any way to really hide a query like above code. So I am trying to use fewer from queries.

 

[saqassemi]

Dear Colin and Dock man:
I know the file I attached has one table and it is not possible to use for several years and grades and ... And I used relationships in my database. The file I attached is a basic sample for you to help and solve my problem. I attached two file in post NO 12 please download it and check to find a solution for the thread . Thanks for your attentions.

 

[The_Doc_Man]

saqasemmi -

The problem is that someone who can import tables from the database can import hidden tables from the database. Hiding tables is more of a "navigation screen" issue to reduce clutter and is less of a security issue.

You are going through a LOT of work to get very limited benefit. If you are in such a hostile environment that you are seriously worried about people getting to your data, Access is NOT the most secure solution available. In a truly hostile user situation, you should not be using Access as a back-end solution. Can't say it any simpler than that.

Consider SQL Server or some other product that can actually impose role-based security that would prevent direct copying of data out of the product's data store.

At my office, when we put up our primary security-action tracking database, we had safeguards in place to audit everything that someone did. Then we published a simple statement: "Tampering with or altering the content of this database in any unauthorized manner is a job termination offense on first occurrence. No reprimands, second chances, or forgiveness will be issued so don't even ask."

If your management will not go along with that kind of statement, then you perhaps should rethink how much THEY care about the data and ask yourself why YOU care so much.

And as to downloading the file, I NEVER EVER download anybody's file to my personal machine. I've been burned often enough that I just don't do that. Which is one reason why clear problem explanations are so important here. I'm not alone in being reluctant to invite potentially code-bearing entities on my personal system.

 

[isladogs]

I just lost my previous reply but Doc has made many of my points so I won't repeat them.

As for your report, I downloaded your database a few hours ago before responding with comments about its structure.

If you change your table(s) to create a properly normalised structure, the solution will be simple and you may not need further help. But I'll be happy to assist if you d make suitable changes.

However, not with its current table design. Sorry.
The reason for that being partly because if you persist with the current design, you will be back repeatedly asking for solutions to complex situations that could have been completely avoided.

As I think I've mentioned before, I originally built my business almost 15 years ago developing databases for schools and until recently these were my main focus. Over the years, I made plenty of design errors and some of those caused hundreds of hours of work fixing the issues those caused.
I'm trying to help you avoid making similar mistakes

 

[saqassemi]

Thanks all friends for your times, solutions and advices.
I attached two access files in post NO 12. There is a report in each one and there is a text box in each report with same function to find max. One works normal but another one shows ERROR. Now I want to find cause of error, if one of you solve the error without using query like another file that work normal and attach it again is very helpful for me.
Forgive me for repeatedly posts I am not fluent in English.