Anyone good with Access?

paintb4707

Hopefully someone can help me. This thing is driving me crazy, I know it's possible I just can't wrap my access beginner head around it.

I'm going to copy/paste from my Experts Exchange post.

---

I'm creating an Access database to calculate excipient composition per serving of a finished product.

Here's how it works...

A finished product is made up of several raw materials

In order to calculate excipient composition of a raw material, you need a spec sheet which gives you guaranteed percentages. For example, let's say we have a raw material called Rice Bran and the spec sheet says for every 1mg of this material, 12% of it contains protein. The specifications for a raw material typically don't change, therefor I created a form for the user to create a "raw material template" (to basically mimic spec sheets) which allows you to select excipients and enter their percentage. Screenshot attached.

That's the easy part. Now the difficult part is using these "raw material templates" in calculations. Now I need to create a second form which will allow the user to insert a "raw material template" and enter serving size per raw material (this is a variable) then multiply that with the percentages from the template. Finally I need to total the results for each excipient. I attached a spreadsheet to explain what I'm trying to accomplish. I didn't put formulas in the cells so you can see the percents.

Calculation sample - Sample calculation - Google Docs

Database download - MEGAUPLOAD - The leading online storage and file delivery service

meadIT

I don't have Access 07 installed on this work PC. If you can save it in the 2003 format I can take a look at it, otherwise, I'll have to wait until I get home.

paintb4707

meadIT wrote: »

I don't have Access 07 installed on this work PC. If you can save it in the 2003 format I can take a look at it, otherwise, I'll have to wait until I get home.

I just tried to convert it and it deletes all my forms.

meadIT

Yeah, the new way they do forms in 07 may not be backward compatible. I'll take a look at it this evening if you don't have an answer by then.

paintb4707

Thanks man. I appreciate it.

I think my approach would have be an unbound form to create a select query on my excipient list but I'm still exploring options. I'm trying to build a query off of a multiple select listbox but doesn't want to work.

meadIT

Okay, I can't quite wrap my head around the terminology...excipient, etc. Is your envisioned result sort of like your spreadsheet? If so, could you color code it so I can get an idea of where you want the data to come from? Examples: User Entry, Database Tables, Calculations.

jibbajabba

Access ? Don't you swear at me

paintb4707

meadIT wrote: »

Okay, I can't quite wrap my head around the terminology...excipient, etc. Is your envisioned result sort of like your spreadsheet? If so, could you color code it so I can get an idea of where you want the data to come from? Examples: User Entry, Database Tables, Calculations.

The only thing that will be user input (in the calculation) would be the serving size per raw material, which is highlighted in red on the spreadsheet.

I need to multiply the percentages of each excipient (left hand side) with the serving size for each raw material then finally total them out.

meadIT

I'll take another look at it this evening. A good site for Access help and info is UtterAccess Discussion Forums - Microsoft Access Help Center if you can't wait that long.

paintb4707

meadIT wrote: »

I'll take another look at it this evening. A good site for Access help and info is UtterAccess Discussion Forums - Microsoft Access Help Center if you can't wait that long.

Thanks. I dropped a post there as well.

I made some changed to the spreadsheet as well. Red is user input, blue is from tables.