How To Retrieve Column Name That Contain Maximum Number
Apr 9, 2013
I have an Access database with a table that contains 11 fields. The first is a unique ID and the other 10 fields are various numbers. I need to know which of the 10 columns contains the highest number for each row and return the 2 rightmost characters of that column name. I started with the "maximum" function that I found on-line and it works to give me the maximum number in each row, but I need it modified to return the 2 rightmost characters of the column name that contains that number.
Code:
Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
' Set the variable currentVal equal to the array of values.
I'm trying to get the maximum number in a table field to increase it by one depending on the member that is selected in a drop down in a field.
I have three tables: members, programs and times. Each member can have N programs and each program can be broadcast N times.
Each member has a three digit code, like XXX. Each program has the three digit code of the member + three numbers that are supposed to auto increment. That is, the first program of member X with the member code XXX is called XXX001.
What I'm trying to do is that when a new program is filled in and I select the member, then the program code should update automatically, adding one to the latest program by that member.
That is, if the last program by member X that was inserted in the database is XXX010, then if a new program is inserted it should automatically be XXX011, even though programs by other members have been added in between.
This is the code I use now, for the AfterUpdate when selecting the member in a dropdown in the form. But although I've played around a bit, I just get error messages...
Private Sub medlemsruta_AfterUpdate() Dim medlemskod medlemskod = Me![medlemsruta].Column(2)
Dim strMax As String
strMax = DMax("programs_kod", "table_programs", "Left$(programs_kod, 3) = medlemskod") Me!program_kod = Left$(strMax, 3) & Format$(Val(Right$(strMax, 3)) + 1, "000") End Sub
Medlemsruta is a dropdown where one selects the member from the members table, where the three digit code is in the third column (Column(2)).
I'm trying to use DMax to get the maximum number for the particular member and after that adding 1 to that for the new program code.
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?
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 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.
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!
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
I'm retrieving all **Basecode** column data starts with only letters other than 'W', 'N' by this query
Code: SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query
Code: SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this
Code: SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE (IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N")) AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
I am trying to create a training matrix in which i need a form that if I put or assign maximum of 20 attendees then 20 comboxes will appear in form.for example, i enter in textbox1 6 or 15 depends but max is 20 then if hit enter comboxes will appear but quantity will based on value that i enter in textbox1.
When I attempt to use the me.field = me.combobox.column() method to retrieve info from another query,it only works if I use column 0 or 1... it doesn't work with higher columns number.
- field name: cbofi - row source: listado-unico-fi <-- it is a query with unique values and 7 fields in total - event procedure, change:
Private Sub cbofi_Change() Me.txtDOCTPT = Me.cboFI.Column(1) Me.campovia = Me.cboFI.Column(5) End Sub
- txtDOCTPT is the field that indeed works - campovia is the one that does not work
I tried using column(0) and column(1) and it worked fine... but higher column numbers do not work. Just mentioning because the field names are all right.
I am working on a database for a Skip Delivery Company, I have all relevant tables and Forms done and can add customers and so on. I have a field name in my Skips Delivered table called Delivery Docket which contains a long list of customer dockets. What i want to do is, be able to retrieve all customer information (such as Customer Name, County, Location, Phone Number and so on ...) on my Skips Delivered form by just entering the docket number on that form. I'm thinking i need an SQL Statement somewhere?
I have groups and members tables in which each group is formed from no. of members, each member can take loan and will return this loan in a no. of installments so here my problem is when i run the query to retrieve no. of loans she has taken and no. of repayments she has given, this query retrieves same loan amount duplicate ... How to group by this query so that when i run this query it should only retrieves that no. of loans (no duplicate) she has taken and no. of repayments (no duplicate) she has given...
For ur more information I attached the db here ...
I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name. Example: Starting file DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC 05512,3,2,4,2
And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query
Code: SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this
Code: SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE (IsNumeric(Left(Base,1)) AND Left(Base,1) Not In ("W","N")) AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
I want to make a calculated column that adds 1 for every new record that isn't marked as being 'deleted' and will possibly subtract one if a record is marked as deleted. I'm not sure where to start other than calculated column.
So, I have some complicated calculations I am aliasing in my SQL query. My end goal would be to sort by my final calculation from all the other calculations I have performed but I am running in to some issues. I know I can't sort by referencing the alias itself without saying order by (column number). My calculations are usually pretty small (around .001 and up). Whenever I order by the column number I would like to use (select... from... where...order by 73) I get a "Division by zero" error in access. I figured this was an issue with how small the calculations were, so I multiplied by 1000000, then ordered by that column number. Still division by zero...I went ahead and changed all 0 values to 1, still division by zero. There are no zero values in the column itself, it's either 1 or something in the thousands. I can order by other columns, just not the one I want to use. I don't want to have to re-do my entire calculations in my order by clause.
hello all I am trying to set column values to a random number between 3 and 5
PMRatingTokenID: textH1: doubleYearID: textCode:UPDATE PMRating SET PMRating.H1 = (5+3-3)*Rnd()+3 This shows type mismatch error . What is the problem here..
