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.
A few years back I saw a program which helped with database changes. I want to change names of fields and tables, queries etc. in a rather complex database. Does anybody know where I can get this tool or program to run through the database and change it in all corners and crevices on a search and replace basis?
hello, i am super duper new... and am working on a school assignment for weeks! its due tomorrow but i cant get this one rule to work... please help if you can!! Basically I am trying to add a validation rule to a field refering to a different field in a different table. Both fields are Date/Time type I am new.. and not as advanced as some of you.... so maybe walk me through it?? i have spend many hours trying to figure it out~ thanks
Hello, I am completely new to Access, so thanks to anyone who does not think my questions are dumb :) Ok, say for example I have a table that has my income information and my tax rate and I want to compute the income tax I need to pay by simply modifying total income with tax rate, how should I do it? there does not seem to be a function like formular bar in Excel in Access.
Recently I have started working for one of the company where I have to deal with one of the access file. this file has lots of tables containing many fields.
My question is
How can I get all the tables name, their fields and attributes in Microsoft Word file. I have tried opening table > design view and copy text but it doesn't work. also tries coping table and paste in in word file but it takes ages
Hi I have a forrm (Orders) , with a subform (Order Details)
Depending on a selection of a list field it makes certain field visible in the subform.
If I go the next record and select the list field it updates fields in the subform ( Visible ( True or False )
But if I go back to the previousl record it doesnt update the fields that are relavent to the option picked in the List Filed ( Which I have set to after update ). It works fine if I re-select the option in the list field
How do I set it to update the subform field automatically as I scroll through records?
I tried OnCurrent property but I dont think this is the correct one.
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
Public Function getTradeMarks(varProdName As Variant) As String Dim rs As DAO.Recordset Dim intRecords As Integer Dim strTradeMarks As String Dim strSql As String
'strSql = "Select distinct [trademark] from tblTrademarks where [productName] = " & varProdName strSql = "Select distinct [trademark] from tblTrademarks where [productName] = '" & varProdName & "'" Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do While Not rs.EOF intRecords = intRecords + 1 If rs.AbsolutePosition = 0 Then strTradeMarks = rs.Fields("trademark") Else strTradeMarks = strTradeMarks & ", " & rs.Fields("trademark") End If rs.MoveNext Loop If intRecords = 1 Then strTradeMarks = strTradeMarks & " is a registered trademark of company " & varProdName Else strTradeMarks = Left(strTradeMarks, InStrRev(strTradeMarks, ",") - 1) & " and" & Mid(strTradeMarks, InStrRev(strTradeMarks, ",") + 1) & " are registered trademarks of " & varProdName End If getTradeMarks = strTradeMarks End Function
productName TradeMarks 123 T1, T2, T3 and T6 are registered trademarks of 123 234 T1 and T4 are registered trademarks of 234 456 T7 is a registered trademark of company 456 789 T8 is a registered trademark of company 789
The above code works Excellent.
I am looking for some code like this , my situation is similar but it has another column
For example In reference to the same example above I have one more column
Dose anyone know how to adjust the above code to get the following output
productName TradeMarks 123 T1, T6 and T9 are registered trademarks of C1. T2 and T12 are registered trademarks of C5. T4 is registered trademark of C6.
234 T1 is registered trademark of C1. T4 is registered trademark of C2.
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).
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.
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.
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?
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?
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.
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.
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
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!
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
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.
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