How To Select The Maximum Value
Jul 17, 2007
Hi all
I have an expression in a query which is checking a list box on a form. If the list box is empty (is null), then I would like the query to search on the highest (maximum) value in the specified field (CourseFK), otherwise it will look for the value in the list box.
Expr1: IIf([Forms]![frmEditDeleteStudentDetails]![lstEditCourseFK] Is Null,[CourseFK]=Max([CourseFK]),[CourseFK]=[Forms]![frmEditDeleteStudentDetails]![lstEditCourseFK])
However it seems Access will not allow me to do this - I get an error about an aggregate function in a where clause.
Can someone please suggest to me how to get around this? Eg, is there an alternative way to search for the maximum (still needs to be determined dependant on the list box).
Thanks
View Replies
ADVERTISEMENT
Jun 5, 2005
I have a Table where the are several entries all with Fields A, B and C
the same, and varying values for Field D.
There are several different values of A, B and C.
I want have a query, pick out each combination of A, B and C and just the
Max value of D for that combination.
It looks as if it should be simple but I can't figure out how to do it?
Anybody able to help?
View 1 Replies
View Related
Jul 10, 2005
Hi to all;
I am not familiar with access function, in Excel its very easy to find the max value at row level, what's the formula in access query to get the max of this example
Co1co2 co3….co60max(co1:co60)
200150300 500 ?
212542321 222 ?
thanks in advance for respond
Majed
View 3 Replies
View Related
Dec 10, 2007
Hi,
Can some1 tell me how to create a query that shows me the ProcessID for max time and date (date and time are in separate fields) for every PersonID. I need to run a query once in 5 minutes to check the job that every person is doing. They are entering the processID every time they get a new work to do.
View 2 Replies
View Related
Apr 21, 2008
How do you find the maximum of four fields in each record of a query. Say (for example) you have daily records of the rainfall across four cities, where the cities are the fields in the query. how do you write an extra calculated field to the query that shows the max. rainfall across the fields on a paticular day.
Many thanks if you can help
Nifty
View 3 Replies
View Related
Jul 12, 2007
I am a beginner and have a very basic query. I would like to create a field which returns the maximum value across a number of fields for each record. Is there a function that would do this?
To explain in more detail, each record is a customer and the fields are their purchases by month. I would like to get the maximum monthly purchase amount for each customer over the past year.
View 2 Replies
View Related
May 4, 2005
Hi,
we use access for localisation in our application. The business object which accessess the access uses DAO connectivity and we often land to
"Too many client tasks" problem.
is it wise to change the DAO to ADO? if so the above problem will go off?
what are the chances that the problem still persists?
is that access has any limits on the number of connections? like 64, 255? or is that ADO/DAO has any limitaions on the number of concurrent connections to access DB?
Pls help, your help will be highly appreciated.
Thanks,
Venkat.
View 3 Replies
View Related
Sep 26, 2005
How many users can an access database support? I'm setting up an app accross 3 workstations but each has multiple users. There may be as many as 1000 records a day entered. Can an access database handle this or do I need to switch to sql server?
View 6 Replies
View Related
May 3, 2006
hi all,
This is general question only. How many number of records that Access can handle as a maximum records?
Or it only depend on size limit (4GB)???
thanks
View 2 Replies
View Related
Oct 3, 2006
We have a database in Access 97 which is 0.8GB.
Can anyone tell me:
a) The maximum storage capacity.
b) What will happen when it reaches that size!
View 3 Replies
View Related
Aug 17, 2007
I understand that the maximum file size for an Access db is 2GB but can you essentially have as many fields as you like as long as you do not exceed the 2GB threshold?
I am doing some planning for creating a database with ~90 fields and want to know before I start it whether it will be able to handle that many.
View 3 Replies
View Related
Jun 3, 2005
My issue:
Does anyone know the maximum number of (one to one) relationships with cascading deletes a table can have?
Background:
I have a split database peer-to-peer application with ten simultaneous users, 250+ linked tables running on A2K, AXP and A03. MSDE and SQL Server are presently not viable options.
I'm unable to manually or programmatically relate one table to 73 others with referential cascade deletes; some of the 73 are related to still other tables. The one table is used as a recordsource on a subform on each of 73 different main forms, each of the latter having a different recordsource.
In other words, I want to delete a record in any of 73 different main forms, each with a unique table recordsource, and then cascade delete a subform record (which has the same table recordsource, regardless of the main form).
If I combined the 73 into a single table, I'd have a monster record with 500+ fields, assuming the latter is even possible, and in which event perfomance would suffer from excessive network traffic.
What I've tried:
I haven't worked this issue for some time and have forgotten the number of relationships for a single table I've been able to create. As I recall, it's on the order of 20 relationships maximum for one table.
Of course, I can do it programmatically delete the subform record on the OnDelete event of the main form, but, for elegance, I'd like to use a cascade delete.
Peer-to-peer network performance is not presently a problem.
View 4 Replies
View Related
Feb 11, 2008
I would like to know if there is a maximum nb of columns to use in a table or at least a prefered max nb. For example i have a table "customers" and inside i have "name" "last name" "age" "gender"..and so on is there a limit?
By the way i'm using access 2007
Thx for the explanation in advance.
View 3 Replies
View Related
Sep 20, 2005
My quiery is supposed to search through a table of names, and find the latest timesheet entry date for each name, as follows:
ID: 1
Name: John
Timesheetdate: 1/1/2005
ID is specific to each name.
I want the result to be a single row for each ID/name, and to return ONLY the latest timesheetdate (this could either be the maximum value or the latest entry).
I have a feeling that this is a simple problem, but i just dont have the knowledge to do it, any help would be greatly appreciated!
View 4 Replies
View Related
Sep 29, 2005
Can someone tell me what the maximum amount of fields that you can have in a query is please?
View 1 Replies
View Related
Nov 3, 2004
What is the maximum no. of characters allowed in a combobox?
View 5 Replies
View Related
Mar 17, 2008
I have a database of song track data with track length as a field.
I want to produce play lists up to specific lengths e.g 15 minutes and just want the query to show me enough songs to fill up this time period.
Any help appreciated - especially simple solutions!
Jim
View 1 Replies
View Related
Dec 14, 2005
I need to create a query that will return for the latest document, the greatest document number. If I do select max(doc_date), max(doc_nbr), I end up getting the max doc_nbr, but it has an earlier doc date. How would I structure the query to do this?
An example is for these values:
1/30 1
1/30 3
1/30 5
2/28 2
2/28 4
The result should be:
2/28 4
I will then need to see if this returned value is different than the value that was returned the day before, but I am pretty sure I know how to do that.
Thanks,
David
View 6 Replies
View Related
Jan 10, 2006
Hi
What is the maximum number of users who can be connected to one single database at one time? Is there a web site that has info on this?
Thanx for the info
View 1 Replies
View Related
Feb 4, 2006
Hi!
I have a database with a lot of chemical data. My ASP page allows the user to search out all data from a specific location during a specific time period. It then creates a normal html table with the results.
The problem is when I have a large time period and a lot of parameters. For example: data from location 1, 1959 to 2005 returns a HTTP 500 error. When using "on error resume" only the code and data from the last three years are printed (all html tags etc missing from the beginning of the page).
When I choose the period 1959-2002 it works, and also if I use 1970-2005 (which shows that it's not corrupt data in the 2003 year).
My question is: is there a size limit for a recordset? If so, how do I solve this? If not, what could be my problem? Size limit of html pages?
Thanks
Martin
View 3 Replies
View Related
Nov 1, 2014
I have created a db for my warehouse, now what I want is to set a maximum no of items to be issued to one person = 20 (means 20 rows only) in a subform. If the number exceeds it prompt user that you reached to the maximum no of issues.
View 2 Replies
View Related
Jan 21, 2005
Can anyone please tell me the maximum number of records a table can accomodate? Also what happens if the number of records exceeds this maximim value? Awaiting reply
View 2 Replies
View Related
Dec 27, 2006
I have run into a problem with an ODBC table connection. The table I am connected to exceeds the number of fields allowed in an Access table. I am using Access 2003. When I looked on Microsoft help site I think the max number of fields in a recordset is 242 so I am unable to see all of the fields in the original table I am linking to. Does anyone know if this limitation has changed in the newest version of Access? I checked the Filemaker www site and that program does not have the same limitations. Thanks in advance for any help!
View 4 Replies
View Related
Feb 8, 2008
Hello there.
Is it better to have many fields in one table or to split the data between a number of tables and link them?
Thanks.
# gingette
View 2 Replies
View Related
Apr 3, 2008
Hai all,
Let me know the maximum capacity of access database..how many tables can include in the database..is their any limitations?
Example: 2GB, 3GB Like Thir any Limitaions of the Capacity of Database
Any one Help
Thanks & Regards
Narasareddy
View 4 Replies
View Related
Aug 21, 2007
hi there
how many columns is the maximum in a query?, because i have one that has 26 columns , but i need some more... what can i do?? is there a way to add some empty columns to a query??
thanks
View 2 Replies
View Related
