473,320 Members | 1,766 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Calculation involving differing fields and tables Access 2013

5
I am brand new to Access but learn quickly and am taking over a previous person's work. Here is the jist. I have a table that has oil formulas. A formula might involve 3 different base oils from the same field in a table, and several different additives from the same field of table 2. Another field in table 2 will list out the sulfur content of each additive record. I want to have a formulas table that lists out a base oil, it's percentage, the additive, it's percentage, and then tell me the total sulfur content of that formula. I need a query probably right?

So I want to be able to just put in the additive and it's percentage in the table, and it automatically pulls the sulfur field from that additive and uses it in a calculation.
Nov 26 '14 #1
9 2357
twinnyfo
3,653 Expert Mod 2GB
cweb85,

Welcome to Bytes! I hope we will be able to assist you.

Although this link may be a diversion from your question, I do believe it is relevant to proper DB Design (Database Normalization). I include that link because it does not appear that your Database is properly structured. That may not be your fault at all, as you say you inherited this DB from someone else. Your first challenge may be to figure out a way to normalize the data in a way that makes sense and is useable for your purposes. That may ultimately be another thread if you decide to undertake that task but don'[t know where/how to start. We can probably help with that, too.

Second, directly concerning your question, it would be wonderfully helpful if you provided us several examples of your data and your desired result. Your examples should (hopefully) show a breadth of possibilities within your data. This will help us guide you toward a solution.

Keep in mind, that because we don't have your DB, and we all have jobs that keep us busy most of the time, we won't be able to redesign your DB for you, or provide spot-on solutions all the time. However, we prefer to guide posters toward an understanding of the concepts we present so that they can develop their own solutions which will work for them.

Hope this helps!
Dec 1 '14 #2
cweb85
5
Thanks for the response Twinny. Here is a simple but exact example. I have a formula with 50% Base Oil (1), 40% Base Oil (2), and 10% Additive (1). There is a table with Base Oil being the primary key field. Other fields in this table describe characteristics such as sulfur content. There is a second table with Additives being the Primary Key field. It too has fields that describe sulfur content. Lets say that base oil (1) has 0% sulfur, (2) has 5% sulfur, and additive (1) has 30% sulfur. The calculation would look like this.
Total sulfur in formula =
{[% Base oil (1)]X[%sulfur in Base Oil (1)]}+{[% Base oil (2)]X[%sulfur in Base Oil (2)]}+{[% Additive (1)]X[%sulfur in Additive (1)]}. So [0.5*0 + 0.4*0.05 + 0.1*0.3]*100% = 5% total sulfur.

And of course everyone has jobs, I'm thankful for any help! It's just I've been doing this sort of thing in excel for years, but once the amount of data gets too large and cumbersome I'm ready for a change. This is a good time with me taking over the DB.
Dec 3 '14 #3
twinnyfo
3,653 Expert Mod 2GB
cweb85,

Your example is only slightly helpful. From a scientist's perspective (perhaps that is where you are beginning), it makes sense, and from a mathematician's perspective, we can understand the formula.

However, from a database administrator's perspective, it has not told us anything. This is not meant as a slight or slam, but just remember, I have to approach this problem from a DBA perspective.....

For example, in the original post you say you
have a table that has oil formulas
However, your example is theoretical and generic--not specific (which is what data guys like me love).

So, what I really need to know is what does your starting data look like--that is, if I were to open your "table that has oil formulas", what would a sample of that data look like. Then, what do your other tables look like (Base Oils, Additives, etc.) This is the starting point.

Second, is your data presented in the table ALWAYS look the same? If it does, there may be some ways to look at your data without restructuring your tables (although my recommendation is to always normalize your data as best you can). If your data is presented in different ways (such as three oils and five additives) you must figure out ways to manage that (which can be an absolute nightmare from a data perspective), which, again, lends itself toward normalization.

Once we figure out the data, the calculations should be pretty simple.

Also, I am going to send a link of this thread to one of the sharper Experts on this forum (ZMBD), who is also a chemist, as his insight into this may also be helpful, not just from a DB perspective, but as a chemist who works with DBs.

I think with a elbow-sulfur-base-oil, and a little time, we should be able to get you pointed in the right direction!

:-)
Dec 4 '14 #4
cweb85
5
Wow I really appreciate all the help. I wish I could attach the file so you could just look at it. Here are some important points;

1. I'm trying to normalize the database like you've suggested, I'm just slow!! So for instance I changed the primary key back to the ID field like your link suggested.
2. Here is the tblBaseoil {ID, Base Oil, API, Density, Sulfur Content}
Here is the tblAdditives {ID, Additive Package, Sulflur Content}
Here is the tblFormulas{ID, Base oil 1, Base Oil 1 % Base Oil 2, Base Oil 2%, Additive 1, Additive 1%, Additive 2, Additive 2%}
Now in reality, I might have a formula that has 10-14 different ingredients so this is a simple example.

Now after looking at your link, I think my formula table should be broken up into different tables since I constantly use different amounts of each base oil. So my formulas can have many oils, many additives, and each type also has a percentage.
Dec 4 '14 #5
twinnyfo
3,653 Expert Mod 2GB
[quote]Now after looking at your link, I think my formula table should be broken up into different tables since I constantly use different amounts of each base oil. So my formulas can have many oils, many additives, and each type also has a percentage.{/quote]

I agree. Again, please post what several of your actual records look like, because if you are trying to normalize, you may also not want to do that manually. Seeing the data may help us help you to automate that process.
Dec 4 '14 #6
cweb85
5
O, and btw, I don't care about reworking the data tables because I actually want to start from scratch. This guys DB is on access 2000....that's right....access 2000...
Dec 4 '14 #7
twinnyfo
3,653 Expert Mod 2GB
Rebuilding from scratch sounds like a good plan--but what if you could automate 90% of that rebuilding....?

;-)
Dec 4 '14 #8
Rabbit
12,516 Expert Mod 8TB
I suspect you can combine the oil and additive table into a single ingredient table, assuming they share most of the fields in common. And you would add a field to indicate whether it is an oil or additive.

Then the formula_ingredients table would have 4 fields, id, formula id, ingredient id, percentage.

You would also want formula table that has one row per formula id.
Dec 4 '14 #9
zmbd
5,501 Expert Mod 4TB
Rabbit has the right idea here.
This is a variation on a cookbook/recipe type database.

[tbl_reagent]
[tbl_reagent][reagent_pk] autonumber
[tbl_reagent][reagent_name]text(20)
[tbl_reagent][reagent_API]text(15)
[tbl_reagent][reagent_D20]numeric(decimal)
[tbl_reagent][reagent_S]numeric(decimal)
[tbl_reagent][tbl_additive]numeric(byte)
((Why I stopped using Yes/No field-AllenBrowne)If you move to SQL-Server, nulls in Boolean (yes/no) fields can cause issues )


[tbl_formulation]
[tbl_formulation][formulation_pk] autonumber
[tbl_formulation][formulation_name]text(20)

[tbl_recipe]
[tbl_recipe][recipe_pk] autonumber
[tbl_recipe][fk_formulation]numeric(long)
[tbl_recipe][fk_reagent]numeric(long)
[tbl_recipe][recipe_amt]numeric(decimal)

Query would have your calculation.
You would need to have a method validation for the %makeup=100% for each formula
Dec 5 '14 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: CJM | last post by:
I have a query which produces different results in the Access query builder and in an ASP page (via ADO) An example of the query is: ----------------------------------------------------------...
3
by: alex_peri | last post by:
Hello All, I am having problems with sorting a recordset by fields in Access. I have a table with three columns called ID, SNo and Time and would like to sort the records by Time. I would like to...
1
by: DeanL | last post by:
Hi everyone, Does anyone know if it's possible to link fields from a SQL Server db into an Access db and if so then how? I have a number of users with an Access db and they need a small amount...
2
by: steph | last post by:
I have a table with 250 fields. Of course you are wondering why 250 fields... what could I possibly be storing in so many fields? I am using this table as a general import table for files that...
3
by: sstidham | last post by:
I don't even think this is possible, but I wanted to make sure before I told my boss we need another solution. Essentially he wants to do a Query by Form. He would like to be able to type the...
2
by: Medaron | last post by:
I am trying to wite a vba code to read all my outlook task fields in Access database. So far I have following code: Sub OutlookTasks() Dim ol As Object Dim olns As Object Dim objFolder As...
0
by: steveradaza | last post by:
Sir,Good Day..I am a newbie here and i am just learning the usage of microsoft access..can you help me solve my problem in making a running balance in a query of microsoft access 2013 of my In and...
4
by: nomeepk | last post by:
hi, i am using access 2013, Is there a way i can make this Access Database online, so i can access and use it from anywhere in a Browser? Regards.
5
by: jimatqsi | last post by:
I'm looking for information about compatibility between Access 2013 and Access 2003. I've been approached by a firm that wants to begin upgrading their systems. I want to know if it is reasonable...
0
by: LeoVBNET | last post by:
Hi Because VS 2013 dropped SQL COMPACT databases, I need to convert Access 2013 databases into SQL COMPACT in order to be able to use Linq to SQL in VB 2013. Anybody can help me? Thanks
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.