Normalizing a Database Table to make Data Queryable

[hypetech]

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:

Bldg____Electric_____Gas___Heat____Cool____Water___Sewer
BLDG1___79E+231E_____40G___1H______1C______86W_____111S
BLDG2___80E+232E_____39G___NULL____NULL____3W+2W___163S
BLDG3___71E-72E+316E_168G__9H______20C+48C_77W_____138S
BLDG4___332E_________187G__53H-52H_50C-49C_162W____183S
**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

Public Function SplitMeters(sPackedValue As Variant, nPos As Long)
Dim sElements() As StringsElements() = Split(Nz(sPackedValue, ""), "E")
If UBound(sElements) < nPos Then
SplitMeters = ""
Else
SplitMeters = sElements(nPos)
End If
End Function

I then grabbed the electric meters and applied this function to them to create a query that looks like

BLDG___METER1___METER2___METER3
BLDG1__79_______+231_____NULL__
BLDG2__80_______+232_____NULL__
BLDG3__71_______-72______+316__
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!

[twinnyfo]

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:

tblEMeters
 
 Field        Description
EMeterID     AutoNumber, PK -- you may or may not
             need this, but it is a good habit
Bldg         FK to your Various Buildings
MeterDate    The date the meter was read
             (or input into the DB)
MeterValue   Integer; These are determined using
             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:

CInt("+231") = 231
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!

[jforbes]

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?

[hypetech]

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.

[jforbes]

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:

BLDG3___71E-72E+316E_168G__9H______20C+48C_77W_____138S
 

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?