Unanswered: Access Table with a lot of numbers HELP!!
I have a access table with 31 columns and 42000 lines I need to find the min number in the line and all of the duplicate min numbers and place them in a new column but I need to write out what column name they are in.
DESTZIP,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4<----ECT for 31 columns
DESTZIP,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4<----ECT for 32 columns,Optimal
05514,2,3,2,5<----33 column 2-ORIGIN1 ORIGIN3
hey, you're ahead of schedule. I didn't expect this thread for another week...
Remember when i told you not to treat Access like Excel and your refusal to normalize the database would result in major headaches down the road requiring bizarre work arounds for simple problems? This is one of those headaches.
It worked in Excel but only with 7 fields you can only nest 7 functions in 1 formula. I wrote a program that can pull the min but it will only pull the first one but I need all of them in the given row. I can't make the file any smaller becuse I need to check every zipcode in the US 41,763 and compare it with the origin(Column name) to give me the quickest del.
I suppose one can only lead a horse to water so many times...
Once last time, consider normalizing your database. This will be very, very easy to do with a normalized design. It will be very, very difficult and processor intensive to do with your current backend. This EXACT problem is what I was trying to make you aware of last time you got help in the excel/access forum. Everytime you change the number of columns, you will have to recode your entire database.
Set your perception of how this should be done by the curb for a minute and explore what I'm suggesting, you may be pleasantly surprised...