Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    starting spot for db (2003)

    I am looking for some opinions on a starting place for a db that i am about to create. We collect data from 175 natural gas wells. There are 4 for us that do this on a daily basis. Right now we all have an Excel sheet that we fill out and email in to the office. Our sheets get transferred to a different workbook that keeps the daily information per that month, a different workbook is used each month. Each well is a worksheet.

    What i am wondering is should i make a table for each well or have 1 table that collects all the data?

    thanks,
    jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: starting spot for db (2003)

    I'd create a single table for the data about all wells, with a field that identifies the well.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: starting spot for db (2003)

    attached is what i have so far...

    should i use an autonumber field and make it the primary key or just just it like i have it?

    thanks,
    jackal
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: starting spot for db (2003)

    An AutoNumber field is always handy.
    Will the combination of the Date and Well fields be unique? If so, you could make that a unique key too.

    Some tips:
    - Set the Subdatasheet Name property of the table to No (and for all future tables too).
    - In the General tab of Tools | Options, clear the check box "Track Name AutoCorrect Info".

    If the fields SW1Ft, SW2Ft, SW3Ft and SW4Ft contain similar information, as well as SW1In, SW2In, SW3In and SW4In, you could create a separate table with only SWFt field and one SWIn field, and multiple records per well. Similar for Oil1Ft and Oil1In etc.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: starting spot for db (2003)

    The combo of the Date and Well will be unique, the ReportCenter is just as unique as the well name so I will use the Date and ReportCenter as the pk.

    The SW* fields are for tank volumes, so for example: if gage on the tank 1 is 5'4", i would put 5 in SW1Ft and 4 in SW1In. The SW stands for saltwater. These fields will be compared to the previous days reading to get the number of barrels produced. If you have any other suggestions for collecting these amounts I am all ears.

    thanks,
    jackal

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: starting spot for db (2003)

    It depends. If the SW1 to SW4 readings are basically equivalent, I woudl probably create a separate table for them, as in the attached database. The structure I created there makes it easy to perform calculations across the 4 readings. But if each has a very distinctive meaning, it might be better to keep them in the tblProd table.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: starting spot for db (2003)

    Each SW or Oil is its own and independent of each other. I did not know if there was an easier way to input the foot and inches for each well or do it the way i have it. I will be looking at the previous days reading and subtracting it from current reading, then multiply it times the barrels per inch conversion to get to get the barrels for that day.

    thanks,
    jackal

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: starting spot for db (2003)

    If they are independent of each other, you can keep them in the main table, instead of splitting them off into a separate table.

    If the gauges display feet and inches, using two separate fields is a good idea. You can always convert to inches or centimetres or whatever in a query. If you'd use a single field (inches, presumably), the user would have to perform mental calculations before entering a value, increasing the probability of mistakes.

Posting Permissions

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