Redesigning tables (1 Viewer)

[DJBummy]

Working on redesigning a previously created database.
The existing database has a table to store pump station data.
The table looks something like this, with no related tables associated with it.
(50 Fields) Wasn't me

StatinID pk
StationGals (16 Pump Stations)
StationPump1Hrs (16 PumpStations)
StationPump2Hrs (16 Pump Stations)
StationDate

I thought that there should be two seperate tables. One to store the pump station name and the other to store the gallons and pump hrs.
The data is entered every day so the original table would have 365 records for each year.
With the new design it seems that there will be 16 records for each day or 5840 records per year.
My question is even though the new design would be a better normalization the rows would expand quite a bit over the passing years. Would this create a problem with the database size after so many years.

Thank you in advance

 

[pbaldy]

I would agree with the new design. 5840 records/year is trivial. Access will handle a few centuries worth of data easily.

 

[DJBummy]

Thank you for your reply.
Now all I have to do is to extrapulate the existing 4,000 records to 62,000 records into the new table. Guess what i will be doing this weekend! Besides drinking heavily.

 

[pbaldy]

You can use a UNION query, like:

SELECT StationDate, StationPump1Hrs AS Hours, 1 As PumpNum
FROM TableName
UNION ALL
SELECT StationDate, StationPump2Hrs AS Hours, 2 As PumpNum
FROM TableName
UNION ALL

And append based on that.

 

[Galaxiom]

Now all I have to do is to extrapulate the existing 4,000 records to 62,000 records into the new table. Guess what i will be doing this weekend! Besides drinking heavily.

That sounds awefully like you are planning to do some hard work.
That whole job can be done in a reasonably small amount of code.

 

[DJBummy]

pbaldy

Does this mean i have to create 16 different select queries to include in the union query as there are 16 different station fields in the original table.

Galaxiom

Im all ears if you have something. Because I really like drinking heavily

 

[Galaxiom]

Basically my solution would generate the union query that David has suggested using a loop through the fields collection of the table.

There is an example of something similar I posted in this thread but choosing fields on different criteria from what you need. Unless you are fluent in VBA you would probably find it easier to just type out the query as David showed.

Essentially it does mean something like 16 different queries joined together with UNION ALL.

 

[pbaldy]

Who's this David character?!?

 

[Galaxiom]

Who's this David character?!?

Sorry Paul.

Probably the ghost of David Crake.
I am sure he would still be visiting the site occasionally.

 

[pbaldy]

I'd be honored to be compared with David, and I suspect he is still keeping an eye on us.

 

[DJBummy]

Thank you for all of your help. I have a bit of a dilema. I created the SQL below as you suggested and it works fine. My problem is that included in the Shift table there are 5 more pump stations but some have only one pump and some have none and the gallons are totalized using a flow meter only. The SQL will not let me include those other 5 stations in this sql. Am I missing something or do I need to create 2 more sql's and somehow combine them.

SELECT Date, FrankGals AS PumpGals, "FrankGals" AS PumpName, Frank1 AS PumpHrs1, Frank2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, MorrGals AS PumpGals , "MorrGals" AS PumpName, Morr1 AS PumpHrs1, Morr2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, DaleGals AS PumpGals , "DaleGals" AS PumpName, Dale1 AS PumpHrs1, Dale2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, MillGals AS PumpGals , "MillGals" AS PumpName, Mill1 AS PumpHrs1, Mill2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, ProsGals AS PumpGals , "ProsGals" AS PumpName, Pros1 AS PumpHrs1, Pros2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, MTGals AS PumpGals , "MTGals" AS PumpName, MT1 AS PumpHrs1, MT2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, DeckGals AS PumpGals , "DeckGals" AS PumpName, Morr1 AS PumpHrs1, Morr2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, UYGals AS PumpGals , "UYGals" AS PumpName, UY1 AS PumpHrs1, UY2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, SadieGals AS PumpGals , "SadieGals" AS PumpName, Sadie1 AS PumpHrs1, Sadie2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, CroylesGals AS PumpGals , "CroylesGals" AS PumpName, Croyles1 AS PumpHrs1, Croyles2 AS PumpHrs2 FROM Shift
UNION ALL SELECT Date, ViewGals AS PumpGals , "ViewGals" AS PumpName, View1 AS PumpHrs1, View2 AS PumpHrs2 FROM Shift
ORDER BY Date;

 

[Galaxiom]

All subqueries of the Union query must have the same number of fields.

Fields that are not used can be entered as Null.
eg:
SELECT Date, ViewGals, "ViewGals", View1, Null FROM Shift

Date is not a good name for a field because it is a reserved word. You should change it in your new table.
Ultimately you will probably also convert the PumpName field to a number and use a lookup table for the actual name.

BTW. It is not necessary to repeat the new name on subsequent lines. The first subquery defines the names and the other follow on.
You may also run into the maximum number of characters in a query. Removing these unnecessary fieldnames will help with that.

 

[DJBummy]

Thanks again. I did as you suggested and renamed the Date to StDate. I realize about the reserved word thing, but I inherited this database and am trying to fix it up (15 years of data).
I have a feeling as I delve into the db deeper I will be using several union queries. Thanks to Galaxiom and pbaldy for saving me a weekend of frustration and also helping me learn something new.
I will raise a glass to the both of you.
The following sql worked great. Here it is in case someone else wants to use as example.

SELECT StDate, FrankGals AS PumpGals, "FrankGals" AS PumpName, Frank1 AS PumpHrs1, Frank2 AS PumpHrs2 FROM Shift
UNION ALL SELECT StDate, MorrGals , "MorrGals", Morr1, Morr2 FROM Shift
UNION ALL SELECT StDate, DaleGals, "DaleGals", Dale1, Dale2 FROM Shift
UNION ALL SELECT StDate, MillGals, "MillGals", Mill1, Mill2 FROM Shift
UNION ALL SELECT StDate, ProsGals, "ProsGals", Pros1, Pros2 FROM Shift
UNION ALL SELECT StDate, MTGals , "MTGals" , MT1, MT2 FROM Shift
UNION ALL SELECT StDate, DeckGals , "DeckGals" , Morr1, Morr2 FROM Shift
UNION ALL SELECT StDate, UYGals , "UYGals", UY1, UY2 FROM Shift
UNION ALL SELECT StDate, SadieGals , "SadieGals" , Sadie1, Sadie2 FROM Shift
UNION ALL SELECT StDate, CroylesGals , "CroylesGals", Croyles1, Croyles2 FROM Shift
UNION ALL SELECT StDate, ViewGals , "ViewGals", View1, View2 FROM Shift
UNION ALL SELECT StDate, RRGals, "RRGals", RRPump, Null FROM SHIFT
UNION ALL SELECT StDate, TWGals, "TWGals", TW1, Null FROM SHIFT
UNION ALL SELECT StDate, GovierGals, "GovGals", Null, Null FROM SHIFT
UNION ALL SELECT StDate, BelmontGals, "BMGals", Null, Null FROM SHIFT
UNION ALL SELECT StDate, WildcatGals, "WCGals", Null, Null FROM SHIFT
ORDER BY StDate;

 

[Galaxiom]

No worries. If you are ever contemplating some seemingly horrendous restructuring you can (almost?) always assume that there is an easier way to do it.

Most of us who are good with code learnt at least half of it trying to find ways to avoid work.

 

[DJBummy]

Galaxiom
Always like things easier

Found another table that was designed like a spreadsheet. Bad idea i agree.
Problem is that the data from this table has 25 tank levels. Each tank level is entered every day 12 times a day.
If I create a table that stores this data over a ten year period of time that will create over a million records in the storage table. That seems like alot to me. Maybe Im wrong. Your thoughts?

 

[Galaxiom]

A million records is no problem for Access. The largest table I have in an Access database has 15 fields, (several with indexes), and reaches over ten million records before it hits the 2GB file size limitation of Access. It still quickly retrieves records in queries.

If that isn't enough MS SQL Server 2008 R2 Express (the free version) has a limit of 8GB.

There are ways to optimise storage. As I hinted before the PumpName field in the big table should be stored as an integer. Another table stores the text names of the pump against those integers and can be looked up to get the name as required.

It is also important to index fields used in queries as joins, ordering and criteria. This becomes critical as the database beomes larger.

 

[DJBummy]

Sounds good. I will certainly take your advice in the future. Thanks again.