Results 1 to 6 of 6
  1. #1
    salisbut is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    52

    Designing Load Database

    I work for an electrical company and my current job is to create a load database which keeps track of the amount of watts used at each meter in our commercial electrical meters. I have attached the spreadsheet that will import the data and am curious to know how you think the database should be designed. I am going to take out the fields Src and scale because they are not needed. I am also going to take out the max and average fields because I can calculate them myself in Access. My idea is to have 2 (3 counting the linked table that I will import into and then append from.) tables:
    tblMeterPoint:
    pkMeterPointID
    MP Nbr
    Meter Point

    tblDay:
    pkDayID
    fkMeterPointID (from tblMeterPoint)
    Day
    HE1 (Hour ending 1)
    HE2
    HE3
    HE4
    HE5
    HE6
    ....
    all the way to HE25 so we can account for daylight savings time.
    Then I will have a select query where you can pick the time frame and what dates to grab. The only thing I am scared of is if you can have 25 or more fields in a query because it seems there are limitations.


    One way to make another table is to split the tblDay into 2 tables: tblLightLoadHours and tblHighLoadHours, where the first six hours of the day and the last two are light load hours and everything else is high load hours. Not sure if this is neccessary though... Anyways, just checking the architecture of this before I delve into it. Thanks.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The HE1...HE25 fields in one table are an example of a repeating group which indicates that your structure is not normalized. The HE1...HE25 should be records in a table. Also, as a general rule, it is best not to have spaces or special characters in your table and field names.


    This table is fine:
    tblMeterPoint
    -pkMeterPointID
    -MPNbr
    -MeterPoint



    Now for any given date, you will have a number of hourly (HE) intervals for which readings need to be taken; this describes a one(date)-to-many(hours) relationship. Additionally, an hourly (HE) interval occurs on more than one date which is a one(HE)-to-many(Date) relationship. When you have 2 one-to-many relationships between 2 tables, you need a junction table

    tblDates
    -pkDateID primary key, autonumber
    -YourdateFields

    tblHE (you'll have 25 records in this table)
    -pkHEID primary key, autonumber
    -longHE

    tblDatesHE (the junction table between dates and HE's)
    -pkDateHEID primary key, autonumber
    -fkDateID foreign key to tblDates
    -fkHEID foreign key to tblHE

    Now you can relate the date/HE to the meter and the reading

    tblMeterReadings
    -pkMeterReadingID primary key, autonumber
    -fkMeterPointID foreign key to tblMeterPoint
    -fkDateHEID foreign key to tblDatesHE
    -meterreading

  3. #3
    salisbut is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    52
    Is there a way to append records to the junction table or does this have to be done manually? I have the append query set up for tblDatesHE but it does not want to work... Also, not sure how I will append from tblLinkedTable to the meterreadings because access does not allow me to have duplicate output destinations. Is there a key detail I am missing about junction tables? Database is attached.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Since you already have the data in both tables, you can create a query that gets the Cartesian product of the two tables and append that. The query would look like this:

    INSERT INTO tblDatesHE ( HEID, DateID )
    SELECT tblHE.HEID, tblDates.DateID
    FROM tblHE, tblDates;


    Regarding the linked table you may have to do an intermediate query or some special joins, but it is hard to say since you did not include the spreadsheet to which the table is linked.

  5. #5
    salisbut is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2010
    Posts
    52
    Ok this append query works, here is the attached spreadsheet with the updated database.

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Once you move the data over to Access are you going to abandon the spreadsheet altogether or do you get your raw data in via the spreadsheet for every time period?

    With the design of the spreadsheet, it is going to be messy. You will have to run an append query for every meter reading field (F6...F29).

    I've done the first one for you:

    INSERT INTO tblMeterReadings ( Meterreading, DateHEID, MeterPointID )
    SELECT tblLinkedTable.F6, (SELECT DateHEID from tblDatesHE where tblDatesHE.DateID=tblDates.DateID and tblDatesHE.HEID=2) AS pkDateHEID, tblMeterPoint.MeterPointID
    FROM (tblDates INNER JOIN tblLinkedTable ON tblDates.YourDateFields = tblLinkedTable.Day) INNER JOIN tblMeterPoint ON tblLinkedTable.[MP Nbr] = tblMeterPoint.MPNbr;

    As noted above I have set the HEID=2 since that is the primary key value for the first hour recording interval (HE=1). To append the data from field F7, you will have to modify the F6 to F7 and the HEID from 2 to 3- and similarly for the remaining fields.

    INSERT INTO tblMeterReadings ( Meterreading, DateHEID, MeterPointID )
    SELECT tblLinkedTable.F6, (SELECT DateHEID from tblDatesHE where tblDatesHE.DateID=tblDates.DateID and tblDatesHE.HEID=2) AS pkDateHEID, tblMeterPoint.MeterPointID
    FROM (tblDates INNER JOIN tblLinkedTable ON tblDates.YourDateFields = tblLinkedTable.Day) INNER JOIN tblMeterPoint ON tblLinkedTable.[MP Nbr] = tblMeterPoint.MPNbr;

    I also noticed that you have some data issues in your spreadsheet; several records have a day value of #Num! . You will need to fix those records if you want them (the query ignores them since there is no matching date in the date table). There are also some records that don't have an MP number or meter point name.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Load excel to access database
    By Rajesh in forum Programming
    Replies: 7
    Last Post: 01-03-2014, 11:44 AM
  2. Help designing sheet music database
    By kingy75 in forum Database Design
    Replies: 1
    Last Post: 02-04-2010, 08:12 AM
  3. Replies: 2
    Last Post: 08-28-2009, 05:16 AM
  4. questions on designing database
    By schultzy in forum Access
    Replies: 1
    Last Post: 07-18-2009, 03:44 PM
  5. Help Designing Tables
    By sakthivels in forum Database Design
    Replies: 7
    Last Post: 06-09-2009, 07:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums