I have two tables with this structure

Table 1
ID
Name
Weight
2000 Usage
2001 Usage

Table 2
ID
Name
Part X
…(all the IDs have differing numbers of parts, so for each part, there is an corresponding ID Number and Name)

I want to have a query do this.

Take Part each part from each ID multiply it by weight and then 2001 usage and show and then show sum for each part.

How do I do this?

Your structure is not normalized, Cosmos, that is part of why you are having trouble with your data.
Does ID refer to a Part or a <machine> made up of Parts? For which table is it the primary key? What will you do when you get to 2002 data, change your structure? 2003?

You probably need at least 4 tables:
tableMachines: ID (PK), Name, Weight (maybe...see below), other Machine-specific information.

tableParts: A different PK (Autonumber probably), PartName, Weight (if you can reliably calculate a machine weight from the sum of its parts, great. Put it here instead of above), other Part-specific-information.

tableAssemblies: Its own PK, a field of the same type as tableMachine.ID, a field of the same type as tableParts.ID, and a Quantity field. This tells you what makes up a particular field. You can even store machine types here and just refer to that in your tableMachines.

tableUsage: Its own PK (again), a field of the same type as tableMachines.ID, a Usage field (probably Numeric?), and a Year field.

Now you've built your tables ONCE and don't ever have to change the structure again (thereby possibly breaking years of data/coding that's come before). This is the nice thing about relational databases.

If I've misunderstood (for example, if you want to count how much a part has been used to make machines, not how much a particular machine has been used), then things will change slightly. But I _strongly_ encourage you to read up on database normalization before you get much farther into this project.

If you're getting this data from someone else, you need to have THEM read up on database normalization and revamp their structure. There isn't really any excuse for a database manager to make these sort of errors.

If you still have no choice on how you get your data, post back with more information about what you're trying to do and someone can probably help you find a kludge to get your data. But it will not be pretty, I can virtually guarantee you that.

Ok, I’ll try to be more specific here on what I am trying to accomplish.

I am trying to create a database that calculates emissions from paint coating operations. Here’s how it goes (bear with me)

A coating can be made up of three parts (Base, Catalyst, Thinner).

Say we have a Coating A (Two-part coating)

[Note: The number of constituents varies from coating to coating]

Coating A – Base
Density = 5.0 lb/gallon
VOC Content = 2.5 lb/gallon
Constituent Name – CAS # - Weight % - HAP
Xylene – 1330207 – 25% - Y
Toluene – 108883 – 15% - Y
Carbon Black – 1333864 – 40% - N

Coating A – Catalyst
Density = 4.8 lb/gallon
VOC Content = 4.5 lb/gallon
Constituent Name – CAS # - Weight %
Xylene – 1330207 – 60% - Y
Toluene – 108883 – 10% - Y

Now I have to figure out the as-applied coating mixture. They are mixed in a ratio of 2:1 (Base:Catalyst)

So for Xylene its [(2/3)*.25*5.0] + [(1/3)*.60*4.8] lb/gallon and so on for the other constituents.

Result
Coating A – As-applied
Density = 4.93
VOC Content = 3.44 lb/gallon
Constituent Name – CAS # - lb/gallon - HAP
Xylene – 1330207 – 1.79 - Y
Toluene – 108883 – 0.66 - Y
Carbon Black – 1333864 – 1.33 - N

Next, I need to figure out another calculation

PM Content for each coating = Density – VOC Content
e.g PM = 4.93 - 3.44 = 1.49


What I want is

1) a summary Report showing Coating (As-applied), Density, VOC Content and PM Content.

2) A Report to show emissions of each constituent based upon each coatings annual usage, that is a sum of ALL xylene (and the others constituents) emissions. (lb/gal * gal/yr / 2000 = Tons per year). ALSO, if HAP = Y sum ALL emissions that fall under HAP = Y.

3) To be able to show each coating’s as-applied makeup on one page per coating (as-applied) report

4) Have a list of the highest lb/gallon values for each constituent (Report).

Sorry about the lengthy question but I am at a lost as to how to set this up in ACCESS.

Thanks in advance!


[This message has been edited by Cosmos75 (edited 04-22-2002).]

[This message has been edited by Cosmos75 (edited 04-22-2002).]

No problem. That's what we're here for, to learn and figure it out. Here's what I got out of your more complete description of the pieces:

Because of the variable number of constituents AND parts, you're going to be much happier with a properly structured database. You're probably used to Excel where everything has to be flat. Access has the ability to build 'deep' trees of structure that use the data you have more efficiently.

I'll use some abbreviations throughout because this is going to be so long. If anything doesn't make sense, let me know.
---------------------------------------
tableApplications: This is a REFERENCE table, for each type of Application you do. Make a new entry if you change a formulation, so as not to screw up your old records.
Primary Key: Autonumber. Name: Text. Any other fields specific to an Application as a whole, i.e. price, Special Equipment needed, etc.
**Note this assumes you have 'standardized' formulations.**

tableCoatings: This is a reference table as well. See above.
PK: AutoNum. Name: Text. CoatingType: Text, either Catalyst, Base, or Thinner (you can put these into a 3-entry lookup table if you want, but it's less crucial here). Density: Number, Double, VOC_Content: Number, Double.

tableIngredients: Another reference table. The data here theoretically shouldn't change since it looks like chemical categorizations and hazardous classifications.
PK: AutoNum. Name: Text. CAS: Text (since you'll do no math with it). HAP: Yes/No field.

This may seem overly broken down, but you're going to see the results soon. Now you have to build some tables to 'assemble' your coatings.

tableCoatingIngredients: Here you define what makes up any Coating.
PK: AutoNum. CoatingPK: Long Integer, Lookup to tableCoatings. IngredientPK: Long Integer, Lookup to tableIngredients. Density, Num, Single, Format as percent.
**Essentially this will say:
Coating A - Base (#34) is made up of ingredients 45 at 60%, 67 at 25%, and 89 at 15%. Access can go look up the specs on the ingredients any time it wants, that's why these PK fields are Long Integer, to match the Autonumbers in the reference tables.**

tableApplicationCoatings: Similar to above, but we need to specify the ratios.
PK: AutoNum. ApplicationPK: Num, Long Int, Lookup to tableApplications. CoatingPK: Num, Long Int. Lookup to tableCoatings. Ratio: Num, Integer.
**So: Application 5, Coating 3 at ratio 2, Coating 7 at ratio 1. We can calculate the Applied figures from this.**

We'll need some queries to get our answers.
queryApplicationCoatings: Totals Query (click the sigma button on the toolbar).
Source: tableApplicationCoatings
ApplicationPK, Group By
Ratio, Sum

Question_1: Totals Query
Source: tableApplicationCoatings, queryApplicationCoatings, tableCoatings
ApplicationPK, Group By
(blank field)AppliedDensity: Sum([tableCoatings].[Density]*[tableApplicationCoatings].[Ratio]/[queryApplicationCoatings].[SumOfRatio]), Expression
(blank field)AppliedVOC: Sum([tableCoatings].[VOC]*[tableApplicationCoatings].[Ratio]/[queryApplicationCoatings].[SumOfRatio])
etc. Hopefully I got the calculations right, it's sort of like giant algebra.

Your other questions follow somewhat from this trend so hopefully you can put together what the variables need to be. Go slow, diagram out what you need and find the fields/calculations you need to use. Please post back if there are other things that crop up. I hope I haven't confused you.

David R (haven't done Organic Chem in a while, thanks)

[This message has been edited by David R (edited 04-25-2002).]

David,

Thanks! It'll be awhile before I can find the time to set up the database. When I have that done, I'll post back here to let you know how it went.

Am not sure what you mean by putting Catalyst, Base, or Thinner into a 3-entry lookup table.

What about setting up relationships between the tables? Or is that already accomplished with the primary keys? (Am a newbie to ACCESS so bear with me)

Very Grateful for your help!!


[This message has been edited by Cosmos75 (edited 04-23-2002).]

David,

Created the database, haven't populated it yet. Not quite sure I did the lookups correctly (e.g. Lookup to tableIngredients).

Also, not quite sure what tableApplications and tableApplicationCoatings is.

Is tableApplicationCoatings for putting in the ratio of base:catalyst:thinner?

And tableApplications is a list of the coating (As-Applied); Name in tableApplications is Coating (As-applied) name?

Is tableIngredients going to list every constituent, its Cas Number, whether its a HAP and a weight percentage (which differs from coating to coating)? So I could have xylene couple of times in this list and each with a unique weight percentage and a ID number identifying it to a particular coating? How does it do that?

I can't figure out how ACCESS will do that when they constituents and the Coating Parts are entered seperately? DO I need a form with the coating part name, ratio that has a subform linked to the constituent table?

I ask all this because I am not sure how to enter the data I have. What do I start with?

Here's the data I have.
- Coating (As-applied) Names
-Names of the coating parts that make make up the Coating (as-applied)
-a list of constituents and weight percentages for each coating
-whether a not a particular constituent is a HAP
-Ratio of how parts are mixed
-Density for each coating part
-VOC content for each coating part
-Coating Type

Sorry, these must be dumb questions. But I need to ask them so I can learn. Probably will have more once you reply.

THANKS in advance! I truly do appreciate this!

[This message has been edited by Cosmos75 (edited 04-24-2002).]

Sorry, I left out a step and knew I was missing something. Yes, defining relationships in Tools>Relationships is a good step here because it allows you to enforce Referential Integrity between your tables, as well as making your queries easier to build (any relationships already saved as permanent will show up in your Query Builder automatically, then you just have to add special cases).

Let's see if I can clarify:
I screwed up initially thinking weight was an Ingredient-specific value, but it varies from coating to coating, so it would go in tableCoatingIngredients. My mistake, sorry. I corrected my above post.

tableApplications is a description and any data you have on an as-applied Application. Example: Name of the application, cost as applied, hazardous materials gear needed, hours to apply (maybe), etc.

tableApplicationCoatings describes how you build each entry in tableApplications. So all it should have as fields are ApplicationPK (so you know what Application you're making), CoatingPK (so you know what coating you're adding), and the Ratio you add that coating in.

Hopefully that helps. I have the table structure worked out in a sample database in Access 2000 so I could get my brain wrapped around it, so if you like I can send you that. Actually it's just tables and queries so I can export it to any format you have.

David,

Thank you for your speedy reply.

Sure, a copy of the sample database would help me out tremendously. My email address is azli@gbpackersfan.com. Could you make it a ACCESS database file and then zip it?

Also, could you maybe run me through how to do the lookups?

Do I just make relationships between the Table IDs and the corresponding IDs on other tables? Any particular kind of relationship? I've seen rerlationships with a 1 or an infinity sign and don't know what those are or how do make those kinds of relationships.

Thank you for all your help!!

Edit: Wht is Density is tablCoatingIngredients formated as percent? Shouldn't it be Number? Or am I missing something?

[This message has been edited by Cosmos75 (edited 04-25-2002).]

I made the lookups in the tables themselves. When I went to make the "Foreign Key" xxxxxxxxPK fields (in tableCoatingIngredients, for example), as I was building the table I chose Lookup Wizard for the type, then chose the table I needed and the column. When I finish the column is actually a "Number" type but it shows the word column I selected.

Pat Hartman has commented previously about getting into conflict sometimes when you design lookups directly in tables (rather than putting a lookup in a form and then storing the number there), but I've never run into it so I don't know what the snag is there.

The relationships (should) be clear when you get the database file - I just sent it off a few minutes ago. Open Tools>Relationships, there should be five tables. A 1/infinity relationship is One-to-Many, a 1/1 relationships is One-to-One, and one without either is probably either Indeterminate (bad), or does not have RI checked (almost as bad, if you can avoid it).

Density is a number field but I formatted it as Percent so the results would be 40%, not .4

HTH, post back here if you have further problems.

David,

Got the file. Matches what I had done except for the relationships.

And I figured that what you labeled Density was to me Weight Percentage of the constituent in that coating. I think I can work out the calculations based upon your example.

I made forms to help me enter data, but there is something that I would like to add that I haven't been able to figure out.

I have a Coating (part of an as applied coating) form that has a subform of ingredients (Ingredient Name, Cas Number, HAP) and Weight Percentage. Is there a way to have a pull-down menu to select from constituents already entered? That way the Ingredient Name, Cas Number, and HAP info would be entered and I'd only have to enter the wieght percentage. I have another database to ACCESS automatically normalized the data and when I created a for, it had a Consituent_ID that looked-up Ingredient Name, Cas Number, and HAP info. So in the form there is a column for Ingredient Name, Cas Number, HAP, Constituent_ID (Lookup) and Weight %. When I choose something from the COnstituent combo-box it fills in the Ingredient Name, Cas Number, and HAP columns. Tried to duplicate that, but no such luck. A combo-box appears but I can't select anything. This would make sure that ingredients would be entered consistently (Name and Cas number) so that an emissions report (by constituents from all coatings) can be generated without duplicates.

I hope that was clear enough an explanation.

Once again, THANK! It looks GREAT!

I made the IngredientPK from tableCoatingIngredients lookup the Ingredient PK, name, cas # and HAP info. It will fill out the form now. Don't know if it screws up anything else though!

David (or anybody else willing to help me out),

OK, I’ve got the database working. Took awhile before I could figure out how to get the calculations I wanted.

Now, I need help with another part of the database.

I now need to add to the database the ability to enter usage data for EACH coating by month and be able to calculate actual emissions from each coating.

Actual Coating Emissions Calculations are as follows. There are TWO types of calculations, one for VOLATILE constituents and one SOLID constituents.

VOLATILE Calc:
[ Usage (gal) * Constituent (lb/gal) ] / 2000

SOLID Calc:

[ Usage (gal) * Constituent (lb/gal) * (100% - Control Efficiency %) * (100% - Transfer Efficiency %) ] / 2000

where - Control Efficiency is amount of solids that the control prevents from being emitted
where - Transfer Efficiency is how much of the solids are transferred to the surface to be paints, i.e. a Transfer Efficiency of 75% means 75% of the solids are transferred to the surface while the other 35% are emitted.

Can I have the query choose which formula to use based upon the YES/NO value for each constutient? If YES, use SOLID.. If NO, use VOLATILE.

If the Control Efficiency and the Transfer Efficiency are the same for all coatings, can I just have a table where I can enter them in as constants instead of in the formula? That way I can change them easily anytime I want.

To throw another curveball at you, what if there are different Control Efficiencies and the Transfer Efficiencies for each coating?

For the Actual Emissions reports, I want to be able to choose the start month and end month of the report? Can I break it down by month and also have it sum up the total of the months selected? Can I choose before the report is generated whether I want it broken down by month, or just total, or both? OR do I need a separate report for each type that I have described?

THANKS!!!


[This message has been edited by Cosmos75 (edited 04-29-2002).]

Hi Cosmos! Glad you got it working, I wasn't sure at the time that my advice was fully logical. Organic Chem wrapped by brain in knots enough when I was in school, now that I've been out, it's an interesting challenge to say the least...

You can use IIF() statements in a query to provide different alternatives (is HAP the Yes/No field in question?):
Emissions: =IIF(tableWhatever.HAP = -1, [VolatileEquation],[SolidEquation])
(Since -1 = Yes, this assumes that Yes means Volatile)

You can put this equation into your structure at whatever level the HAP determinant is (tableIngredients?). If you put it higher up the chain than that then you may be forced to separate data out based on data that is already calculated. Hope that made sense.

If they will be the same for all components but you want to be able to change them in a table, yes you can make a reference table for the ControlEfficiency and TransferEfficiency. If they can be different for EVERY type of coating I would store them at the same level as the HAP variable, or make a table of the different rates and refer to that if there are a limited number of options.

Lots of options at this point, since you're designing the structure from the ground up. Did you manage to find anything on database normalization that made sense?

For your final question, yes you can define start and end parameters for a report. In the query behind it, put this in the Criteria for the date field in question:
Between [Enter start date:] And [Enter end date:]. You'll have to do two different reports if you want to give them the option, BUT you can insert one in the other as a subreport if you want the aggregate data and monthly breakdown together.

[This message has been edited by David R (edited 04-29-2002).]

Based upon examples of normalized data, I've come to the conclusion that normaliaed data is data where data that is not unique to a record can and should be placed in a seperate table that is referenced by the record. Something like that, I think...

What would you suggest as to a usage table? The hard thing for me to figure out is, Applications may get added to this database so at first it seems to me like I should put the usages together with the Applications, but that adds a lot of fields? 12 for each month of the year?!?! Any ideas?

"For your final question, yes you can define start and end parameters for a report. In the query behind it, put this in the Criteria for the date field in question:
Between [Enter start date:] And [Enter end date:]. You'll have to do two different reports if you want to give them the option, BUT you can insert one in the other as a subreport if you want the aggregate data and monthly breakdown together."

So, I have to use a parameter query. (Did some research, but I only know what its called!) How do I get the date formated as only Month and Year?

I am not sure I understand your answer to my report question.

IF I want BOTH total by constituent constituent emissions for the period specified AND a breakdown by month of constituent emissions I have to create TWO reports, one TOTAL and one MONTHLY and have the MONTHLY as a subreport?

Does using a paramter query mean that I have to choose the Start and End dates and then go and open the report? Is there any way to automatically open the report after I've choosen the start and end dates?

Please do not be afriad to include a lot of detail. This is my first time using database and I am not very knowledgeable. I just play around with it till I get it right, but ACCESS seems to need a well formed plan in order to tackle things which inevitable requires knowledge og ACCESS. Especially on these harder things I am trying to accomplish!!

A step-by-step plan along the lines of how you helped me set up the data would be of a big help to me! But I understand if you don't have the time or the energy to do that again.

David, THANKS FOR ALL YOU HELP! I hope that I haven't taken you way from enjoying whatever free time you have with my pet project!

[This message has been edited by Cosmos75 (edited 04-29-2002).]

[This message has been edited by Cosmos75 (edited 04-29-2002).]

[This message has been edited by Cosmos75 (edited 04-29-2002).]

No problem. I think we're getting somewhere, which is a whole lot better than talking to a brick wall and/or empty space. (and/or? oh well...need more coffee)

My new favorite way to open reports with multiple parameters is to make a small popup form (discussed in another topic you made) with XX text fields in it: one for each "parameter", plus a "Open Report" button (and maybe a Cancel button too).

Then in the query behind the report, instead of making parameters (Between [Enter start date:] And [Enter end date:]), you refer to those fields on the popup form. Actually I think I talked about this in our other topic: http://www.access-programmers.co.uk/ubb/Forum3/HTML/000261.html
so I'll stop there. But the nice thing about that is you can pull out for your query just the month and year.
Examples of new fields in your query: MonthYear: Format([DateField],"mm/yyyy"), Criteria: Format([DateBoxOnControl],"mm/yyyy")
So now you're matching the month and year from your data, but don't care about the Day.

I'm not quite sure what you mean by a "usage table". Is this a record of how much usage/emissions/etc there were for each month of the year for a report or something? Or is this actually inputting how much of CoatingX/ApplicationX was used per month? The answers will differ based on what you need.

Post back when you get a chance.

The usage table is how much of the Coating (As-Applied) [I think you used the term application] was used that month. Based on hot much was used for each Application, I need to calculate emissions (Solids & Volatiles) from the coating and summarize them by constituent (One report) and maybe by coating (another report).

I would build a separate table then with just a Datefield, (format mm/yyyy if you want to make things easier on yourself), Coating (As Applied) (Lookup to the list you've got, whatever table that was), and Usage (Number in whatever unit you're using...). Make datefield&Coating (As Applied) the PK, or just an Autonumber is fine as well.

You can use that table in a query with your 'reference' tables to find out how much volatile material was emitted that month.

[This message has been edited by David R (edited 04-30-2002).]

OK, I've got the application usages working with the data formated as mm/yyyy.

I am having problems with the Transfer Efficiency and Control Efficiency table. What kind of relationship does it have with the other tables, assuming that both Transfer Efficiency and Control Efficiency are constants? I've tried to use the values in a formula but access keeps asking for the value. What format should they be (single, percent)?

I also have two more constants. Where in my last posting I was trying to find actual emissions based on actual usage, I also have to find what the worse-case scenario is.

So, one of my previous queries was to find what the highest lb/gal value for each constituent was. I now want to take those value from the query and multiply them by a maximum annual and hourly usage to figure out what the lb/year and lb/hr (worse-case) emissions would be.

THANKS!

I didn't put an Primary Key for the table with the eff %(Transfer & Control), gal/hr and gal/year constants and have been able to use them that way.

1) Will try a scenario when most of them have the same Transfer and Control Eff % but where a few have different % values.

2) And then add on a scenario where some constituents need to be there twice, with the largest lb/gal constituent value using a different gal/yr and the second largest lb/gal value uses the same gal/year value as the rest. (Kinda long to explain why I need to do this, but I'll do it if you're curious enough)

Any ideas on how to accomplish 1) & 2)?

I know I can have a reference table for the different Transfer/Control Efficiency % to help accomplish 1).

But I am at a lost on how to do 2). Some constituents need to have the own unique gal/hr and gal/year usage to calculate worse-case using the maximum lb/gal for that constituent AND the second largest lb/gal value for the same constituent needs to be calculated using the same gal/hr & gal/year as the others. Guess it’s better to explain…

Each constituent has emission threshold (lb/hr and lb/yr) that it must stay under. So there may be a few constituents that exceed that threshold at the “standard” gal/hr & gal/year used for the others. SO, to keep that constituent in compliance, a separate gal/hr & gal/year is used for a constituent that is over the threshold, but if there are other applications that have that constituent, then those are still emitted with the “standard” gal/hr & gal/year. That’s why the second largest value is then used.

IF the second one is still not in compliance, then there are two ways to go.

A) Use the third highest value at the standard gal/hr & gal/year AND still use highest value in the unique gal/hr & gal/year, which may now increase without exceeding the emission threshold since the “standard” gal/hr & gal/year is calculated with the third highest. The unique gal/hr & gal/year is now the usage limit for both applications that contain the largest and second largest lb/gal for that constituent.

B) Use the third highest value at the “standard” gal/hr & gal/year. And have both the second largest and largest lb/gal value have their own gal/hr & gal/year value. This can get tedious since you might have to go to the fourth highest.

The problem with A is that the unique limit used with the largest may be too limiting so B is a better choice sometimes even though B can become more tedious.

Hopefully, I haven’t caused your head to spin too much. I know how that feels trying to get this database to do what I want without any prior experience, but my head would’ve exploded had it not been for your (David R) help.

THANKS!, David R and anybody else who is willing to help me out!

[This message has been edited by Cosmos75 (edited 05-01-2002).]

Sorry Cosmos, I think you exceeded my brain capacity on that one. Maybe post it to a new topic with a brief summation of your data structure and someone can help you out (especially if you're trying to Reports, switch to that Forum and get more specialized help...)

David R,

Sorry about that! Will find time to summarize all my tables and queries and re-post the question.

THANK YOU FOR ALL YOUR HELP!!!