Convert Excel to Access MIN() function help

[larrynew]

I am converting a large Excel spreadsheet (128MB) to Access 2003 where it should have been from the beginning. I'm good with Excel Functions but a beginner with Access. My spreadsheet includes data like this:

master_id vender1price vender2price vender3price lowestprice
ABC123 $10 $9 $11 $9
XYZ789 $20 $21 $19 $19

I am trying to get the MIN value for each row in my query as shown above by the lowestprice column. The simple Excel fx for lowestprice would be something like =MIN(B2,C2,D2).

What is the equivalent in Access? All the other conversions from Excel to Access I've understood like iif and & and the arithmetic functions.

Thanks for putting up with a newbie.

Larry

 

[georgedwilkinson]

Min(columnname).

You would normally use it in a query with a group by clause.

 

[larrynew]

Thanks for quick reply, George.

Is the "group by clause" where I reference the multiple columns? I know if I just have an example or can find one in my "Excel Bible", I can use the same syntax for all the places where I currently use =MIN(B2,C2,D2) in Excel.

Thanks,
Larry

 

[Brianwarnock]

I don't think that is going to help. Reading between the lines I think larry is retaining the spreadsheet layout for his database, perhaps he can confirm what his DB tables look like, mind you I wont be around now till the weekend but more experienced people than I will be able to help.

Brian

 

[georgedwilkinson]

You need to be careful about the way you think about things. Access is nothing like Excel.

You store data in normalized tables, not flat spreadsheets like you do in Excel. You manipulate and report on that data via Forms and Reports. You get access to the data via queries upon which you can base Forms and Reports.

In a query, you "select" all the of column names "from" all of the table names those columns come from "where" your conditions are met. When you need to use an aggregate function (like Min()) in a query, you will need to "group by" all the other columns you reference within your query.

I'm not sure how basic I need to be here. You should start out with the handy-dandy visual query building tool in Access. From there, look up "group by" in help. You'll be amazed at how much of the work Access will do for you.

 

[georgedwilkinson]

Larry,

If you are indeed doing what Brian suggests, I ask you to reconsider even looking at the table, it will only lead you to trouble. Always go through a query or, preferably, a form.

 

[larrynew]

Brian, George,

Yes, I am retaining the layout of my Excel file...for now, just to keep everything simple during the transition. It is a very large flat file with many attributes of my products like weight, cube, etc. I also have fields for the prices from my different suppliers and several price level margins for each product.

From this flat file data, I use Excel formulas to calculate and format my data that I copy and paste into a new file to use to import into Quickbooks and my website and two other web advertising sites.

I update my prices that I get from my suppliers in Excel format with a VB program that merges the data into my big Excel file.

Access progress to date:
I've copied the raw data for the products (25,000+) into a table.
I've linked the Excel price files from my suppliers to my table and that's working great. Much easier than the VB Excel merge program.
I've created queries using iif and conconcenate and arithmetic calculations to pull and format the data from the table that I need to create reports that I can import into Quickbooks, website, etc.
I'm somehow stumped by the MIN() function. If I can just figure out how to do the equivalent of Excel's =MIN(B2,C2,D2) to find the lowest price from multiple columns I should be able to finish the switchover to Access.

Thanks,
Larry

 

[georgedwilkinson]

OK, my advice will work. Just work with a query.

Please beware that, since Access and Excel are very different, you will not be able to update data in this query because Access doesn't know which "row" you want to modify.

The correct option is to normalize, use forms, etc.

 

[pbaldy]

I definitely agree with normalizing the data, but this may help if you don't:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

 

[larrynew]

Thanks George, Paul,

I've got some homework to do! At least now I have a direction. I was really feeling confident with the switch from Excel to Access until I got to MIN(). Everything else (iff, &, +-/*) was straight forward. Paul, the microsoft link may be what I need. I'll work on it for a couple of days and let you know what I found.

Thanks for all the help,
Larry

 

[georgedwilkinson]

Just remember, if you put your data in a form, you can create an unbound control that you can do DMin() in. You'll still be able to manipulate your data but will be able to see the aggregated field, too.

Forms are the way to go, even without normalized data. But absolutely required with normalized data.

Good luck. Yell if you get stuck.

 

[pbaldy]

Well, as has been mentioned, Access requires different design than Excel. The Min() in Access is designed to find the minimum value of a particular field over a range of records (rows). In a normalized design your price fields would be separate records, not fields, and Min() would work (given your example, each master_id would have 3 records in a table). Your design is "spreadsheet thinking", which Access doesn't handle without jumping through some hoops, hence the need for a custom function to find the minimum value over multiple fields in a single record.

I understand the desire to keep it simple and keep the same design, but I would really recommend rethinking it. The first step in creating a good database application is proper table design. Without it, you'll be stumbling over this type of thing again.

 

[larrynew]

Paul,

Thanks to the advice here and more reading I've done, I think I'll put off switching to Access for another month while I study more of the basics of table design. I can see now that I need to break-up my flat earth spreadsheet into smaller tables. If I don't have time to do it right now, I'll never find time to fix it later.

It's been hard to wrap my brain around database concepts and a little frustrating as I've always been able to read a book and play with a program for a while and pick it up fairly easily. Access hasn't been that easy. I think I need to look over someones shoulder for about an hour while they show me how to do what I need.

Thanks for the help,
Larry

 

[pbaldy]

No problem, Larry. I didn't mean to throw water on your fire there, but I think you'll be happier in the long run with a proper design. Another reason occurred to me just now. Your design has 3 price fields. You would have designed forms and reports that all accommodate those 3 fields. Sooner or later somebody's going to say "get 4 prices instead of 3", and not only would you have to change the table, but every form and report. With a proper design it wouldn't matter if they got 1 price or 100; the application would "flex" with it.

If you want to post a sample of your spreadsheet and maybe explain the "business problem" you're trying to solve with this application, there are any number of very qualified people here that would suggest an appropriate table layout (think of them as shoulders to look over!).

 

[ericwfrost]

I'm trying to convert this nasty Excel formula into an Access query...

="<font color=white>"&REPT("n",MIN(AU2,10))&"</font><font color=red>"&REPT("n",MAX(10-AU2,0))&"</font><font color=black>n</font><font color="&CHAR(34)&"#0097d8"&CHAR(34)&">"&REPT("n",MAX(AU2-9,0))&"</font><font color=white>"&REPT("n",IF(MIN(19-AU2,10)<0,0,MIN(19-AU2,10)))&"</font>"

Is there no Access equivalent to the Excel min function? Maybe a custom function?

Eric

__
http://www.mp2kmag.com/a39--word.access.excel.mappoint.html

 

[gemma-the-husky]

Is there no Access equivalent to the Excel min function? Maybe a custom function?

its not that there is no equivalent - its that access min works on a column of figures, not a row of figures.

the point is that each column should be distinct from every other coulmn, and not related to them. therefore to try and assert a relationship is not a valid database function.

As others have said you can force a comparison,. but its awkward, wont scale and will involve you in a lot of effort.

BUT if the columns you are comparing actually contain similar data, then the database way, is to show then one after the other, all in the same column - eg producing three rows of homogeneous data with fewer columns, where before you had 1 row with 3 variant (sets of) columns.

And now the min will work fine, because all the data is in 1 column.

AND you get loads of serendiptious benefits also, because now your data is stored properly

AND it works ultra-quick

AND a database doesnt run out of rows.

 

[ericwfrost]

I think I just need to do an iif(,,), I am always only comparing two numbers.

Actually that formula is a little too long and nasty, I will just do a custom function in code I think.

Eric

__
http://www.mp2kmag.com/a39--word.access.excel.mappoint.html

 

[ericwfrost]

AND you get loads of serendiptious benefits also, because now your data is stored properly

It's really just html formatting, nothing to do with database design or storing it "properly".

Attached is a screenshot of what it creates.. it is actually an html popup inside a kml file.

Eric

 

[pbaldy]

I think this thread has already answered both questions.

 

[pbaldy]

Sorry, didn't realize the thread had gone to a second page. I linked to a replacement function earlier in the thread.