db structure help

[Bill Lewis]

I am at my wits end and sending out a SOS. Anyone interested in helping to point me in the right direction give me a shout please.
Thanks in advance

[CraigSchumacker]

RE: db structure help

Bill:

That's just about everyone here. Feel free to post your questions. Someone, if not many, will happily supply information.

Craig

[Robert Adler]

RE: db structure help

Bill:

We are willing to help. What is the problem.


bob adler

[Martin]

RE: db structure help

...it's the waiting I can't stand...

[David Sander]

RE: db structure help

Bill,

Are you talking about how to set up a database table, how to divide the individual columns into normalized sets, or how to work your business process into an activity that a databsse could do? Perhaps your distraction by these sub tasks is why you can't get pointed in any one direction.

Dave

[Bill Lewis]

RE: db structure help

Thanks to all for their replys
Sorry for the criptic message, frustration and late hours are contributing factors.
I'll try to layout as much of it as I can....
A little background first
This system tracks paint usage in a manufacturing environment to fulfill EPA tracking and reporting requirements.
Each product used has a tracking requirement of daily, monthly, annual.
Each product has basically two items that are tracked (VOC volitale organic compounds) and (HAP hazardous air pollutants (could be from 0 to many compounds. Feds & state determine which haps are tracked)) Each are tracked for daily,monthly and annual totals.

Daily usage is enter and calculations performed (where the problem lies)

Structure
1) Transaction Tables
Usage Header
Id number
Date
Usage Detail (linked to header via id number)
Id number
Product Id number
Quantity

2) Reference Tables
Product Reference (a lookup in usage detail)
Product Id Number
Name
Other data such as specific gravity, mfg etc.
Product Hap Reference (defines the haps in our products)
Product Id Number
CAS Number
Percentage
HAP Reference Data (List of HAPS from state / feds)
CAS number
HAP Name
Other data

Example: Product ID: 1DX008 Name: Off-White
Contains these haps
Name CAS Percent
Ethylbenzene 100-41-4 .05
Xylene 1330-20-7 .09
Toluene 108-88-3 .01


I can enter and pull all the data / calculations I need except for one consistant snag.....
The monthly totals (via a report) are used in another report and i cannot figure out a way to save these totals for further use. All calculations are multilayer calculations and grouped by month / by product.

I probably didn't lay it out very well but would appreciate any insights or help
Thinks Bill

[Jay Talbott]

RE: db structure help

Bill,

To quote the (im)famous Barry R., extract, sort and report.

In other words, use a script to write your records to another table. If necessary, use a variable to keep track of specific information in your script, and write that variable to your table. Then base your report on that table.
Also include in the target table the relevant date (perhaps the last date of every month). Then you can later use that data for other reports.

Hope this helps.

Regards,

Jay Talbott

[Bill Lewis]

RE: db structure help

thanks
Thought of that, but the multible HAPS associated with each product has me stumped.
I was trying to write a child table for each product used that would contain the assiciated haps. A child hap transaction table created for each product used.

[Jay Talbott]

RE: db structure help

Instead of child records for each HAPS, write one record for each HAPS of each product to create a flat file. In your example, I would write out three records, with the fields of product num., prod desc, HAPS name, CAS and Percent. Then your report would group on product num, and the monthly totals could be used in another report.

Regards,

Jay Talbott

[David Sander]

RE: db structure help

Bill,

If you create a new child table to hold all the individual VOC and HAP quantities for your product definitions, the total list of pollutants will be there. This would be a new table with a one to many link from Products. For your needed report build a set that makes the HAP/VOC table the parent, link the Transaction and other tables to it and print them out in order by query and filtered for your date range. Report done.

The above conversion would be a fairly typical data normalization procedure. I would also suggest adding start dates and ending dates to the HAP/VOC table so you could change your product formulations and have the historical totals remain accurate. Adding an end date to a HAP record would remove it from the product formulation. If the state or Feds add new HAPs, that's and easy change, just add them to the product listings they are in and they automatically appear on the reports.

You could also run a Summarize operation to create a summary table with all the pollutant releases totaled for the month from this.

Does this sound like what you need?

Dave

[John Guy]

RE: db structure help

5 Aug 2002


David,


What is a "Summarize operation to create a summary ta...."?

Is this feature available in a5v5?

I tried to lookeit up in the manual and I did not see anything.

I am looking for a5 to do the same as a pivot table in spreadsheet. (Maybe I off limits.)

Thanks a miilion,
JG

[whanigsberg]

RE: db structure help

John,

Summary operation, pg 192 of the user guide pdf.

Bill