By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

Normalizing a Database Table to make Data Queryable

P: 2
I have inherited a database that is built mainly from imported excel spreadsheets. The purpose of the database is to replace a very old program we are using to track utility spending. The main problem right now is one of the excel sheets lists the meters used for each building, but not in a good format. This is the format:
Expand|Select|Wrap|Line Numbers
  1. Bldg____Electric_____Gas___Heat____Cool____Water___Sewer
  2. BLDG1___79E+231E_____40G___1H______1C______86W_____111S
  3. BLDG2___80E+232E_____39G___NULL____NULL____3W+2W___163S
  4. BLDG3___71E-72E+316E_168G__9H______20C+48C_77W_____138S
  5. BLDG4___332E_________187G__53H-52H_50C-49C_162W____183S
  6. **Notice how in bldg3 electric, it's 71E-72E+316E. 
That means the cost for this building is the cost of 71E minus the cost of 72E plus the cost of 316E. I have a separate table that has the costs of each meter every month, which we have been recording for several years.

I am trying to find the total cost for each building every month, by creating a query and doing some VBA, but nothing is working right now. I've been working with just the electric meters to try to get something to work, and I found this function

Expand|Select|Wrap|Line Numbers
  1. Public Function SplitMeters(sPackedValue As Variant, nPos As Long)
  2. Dim sElements() As StringsElements() = Split(Nz(sPackedValue, ""), "E")
  3. If UBound(sElements) < nPos Then
  4. SplitMeters = ""
  5. Else
  6. SplitMeters = sElements(nPos)
  7. End If
  8. End Function
I then grabbed the electric meters and applied this function to them to create a query that looks like

Expand|Select|Wrap|Line Numbers
  1. BLDG___METER1___METER2___METER3
  2. BLDG1__79_______+231_____NULL__
  3. BLDG2__80_______+232_____NULL__
  4. BLDG3__71_______-72______+316__
  5. BLDG4__332______NULL_____NULL__
This makes the data easier to look at, and if I filter the complete meter history table I've been looking at to just grab the electric meters I can match it against the first column. I don't know what to do about the plus and minus signs though, and I know that this isn't normalized data. I haven't really been able to find someone with a problem like mine, or a solution to get the data more normalized.

My idea right now is get all the meters in one field and have the building name repeat, then try to join on meter number and have another field with cost besides that, and then grab all the costs for each building to find the total. However I can't find a way to do this, so I am open to any ideas, especially regarding how to handle the plus and minus signs in the data. I know the data is in horrible shape, and that is a big part of why they have me replacing the old program. I'm just trying to cleanup the best I can. Thank you for all the help and please ask any questions you have!
Apr 28 '15 #1
Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,483
hypetech,

Welcome to Bytes!

As far as the E-meters go, since you have multiple values, but differing numbers of values, I would normalize that portion of your data by using a separate table. I don't see any date fields, but I would also add those. Thus, you would have this:

Expand|Select|Wrap|Line Numbers
  1. tblEMeters
  2.  
  3. Field        Description
  4. EMeterID     AutoNumber, PK -- you may or may not
  5.              need this, but it is a good habit
  6. Bldg         FK to your Various Buildings
  7. MeterDate    The date the meter was read
  8.              (or input into the DB)
  9. MeterValue   Integer; These are determined using
  10.              your SplitMeters Function
I would use the CInt() Function to determine the actual values of your meters, since you are beginning with text values. Thus:

Expand|Select|Wrap|Line Numbers
  1. CInt("+231") = 231
  2. CInt("-72") = -72
Using this setup for your tables, you can aggregate (sum) the values of your meters across buildings and dates, as 71 + (-72) + 316 = 315 when those values are added together.

Hope this makes sense. More importantly, hope this helps you out on your project!
Apr 29 '15 #2

jforbes
Expert 100+
P: 1,107
Nice work Twinnyfo!

hypetech, I'm a bit confused on the data. When you say:
**Notice how in bldg3 electric, it's 71E-72E+316E.
That means the cost for this building is the cost of 71E minus the cost of 72E plus the cost of 316E. I have a separate table that has the costs of each meter every month, which we have been recording for several years.
Does this mean the cost is $71 - $72 + $316? Or are 71E, 73E and 316E references to meters that have a monthly cost associated with them?
Apr 29 '15 #3

P: 2
Hey guys, thanks for replying! Sorry jforbes, 71E, 73E and 316E are references to meters that have a monthly cost associated with them. We find out the monthly cost when we receive all our bills from the meter companies and then input them. The database I'm developing will be the new place we input all our bill readings into. We have a table that has all the meter history going back years, with each individual reading for every meter every month.
Apr 29 '15 #4

jforbes
Expert 100+
P: 1,107
Not trying to take over here Twinnyfo, actually, I'm glad to see you back. =)


hypetech, This is what I understand so far:

This spreadsheet that you are attempting to normalize into database tables defines what Meters are associated with which Buildings.

So there would be two Tables, one for Buildings and one for Meters.

From the looks of the spreadsheet, there is a sign associated with the relationship between Buildings and Meters. My guess is that there may be one electrical cable supplying multiple buildings and so a meter is put between each building and the difference between meters is the consumption of that building. It also looks like there may be multiple feeds to a building, so in the case of:
Expand|Select|Wrap|Line Numbers
  1. BLDG3___71E-72E+316E_168G__9H______20C+48C_77W_____138S
  2.  
the electrical consumption for BLDG3 is the formula of:
((Meter 71) - (Meter 72)) + (Meter 316)

Then you have a history table (that we haven't seen yet) with the Meter Readings that you hope to link back to the Meters and determine usage per building.

Sound about right?
Apr 29 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.