Alpha Video Training
Results 1 to 12 of 12

Thread: db structure help

  1. #1
    Member
    Real Name
    Bill Lewis
    Join Date
    Mar 2002
    Posts
    15

    Default db structure help

    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

  2. #2
    Member
    Real Name
    Craig Schumacker
    Join Date
    May 2000
    Posts
    687

    Default 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

  3. #3
    Member
    Real Name
    robert adler
    Join Date
    Apr 2000
    Location
    Boynton Beach, BL
    Posts
    591

    Default RE: db structure help

    Bill:

    We are willing to help. What is the problem.


    bob adler

  4. #4
    Member Martin's Avatar
    Real Name
    Martin Wolff
    Join Date
    Sep 2001
    Location
    England
    Posts
    376

    Default RE: db structure help

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

  5. #5
    David Sander
    Guest

    Default 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

  6. #6
    Member
    Real Name
    Bill Lewis
    Join Date
    Mar 2002
    Posts
    15

    Default 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

  7. #7
    Volunteer Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Versailles, KY
    Posts
    1,443

    Default 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

  8. #8
    Member
    Real Name
    Bill Lewis
    Join Date
    Mar 2002
    Posts
    15

    Default 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.

  9. #9
    Volunteer Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Versailles, KY
    Posts
    1,443

    Default 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

  10. #10
    David Sander
    Guest

    Default 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

  11. #11
    John Guy
    Guest

    Default 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

  12. #12
    "Certified" Alphaholic
    Real Name
    William Hanigsberg
    Join Date
    Apr 2000
    Location
    Toronto, ON
    Posts
    4,018

    Default RE: db structure help

    John,

    Summary operation, pg 192 of the user guide pdf.

    Bill

Similar Threads

  1. Structure Changes
    By David Montalbano in forum Alpha Five Version 5
    Replies: 2
    Last Post: 01-31-2004, 03:53 PM
  2. Edit Structure Bug?
    By scottly in forum Alpha Five Version 5
    Replies: 2
    Last Post: 07-27-2003, 04:30 AM
  3. help with set structure
    By MIKKI BREAUX in forum Alpha Five Version 5
    Replies: 3
    Last Post: 12-12-2002, 06:13 PM
  4. Help with DB Structure
    By Themis Skiathitis in forum Alpha Five Version 4
    Replies: 7
    Last Post: 07-07-2002, 12:09 AM
  5. Using Database Structure for Import Structure?
    By Ken Wargon in forum Alpha Five Version 4
    Replies: 9
    Last Post: 01-25-2002, 07:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •