Business Utilities design

This has been a bad day for dbs - second big problem today. Oh well.
Turning a spreadsheet into a database for managing 3 utilities
(Oil,Power,water). Measurements are made each month that include use details
and current cost. This is for two locations. I have:
Date table
-ID, MoYear value
Locations table
- ID, two or more city names
Table for each utility measu
-ie, Oil inlcudes BldgNo (25 buildings), EOMInventoryAmt, DeliveredAmt,
DeliveredPrice, ID
Table for OilBuildings
- ID, BldgNo (or site)
This is so simple to do in a spreadsheet, but I'm trying to relate tables to
each other... each month has many locations with 3 utilities for each
location and many sites to measure. Thinking of just selecting the mo/yr and
using combo lookups for the location and type of utility, then have that with
many measurements subsheet with combo lookups for each site, but I would like
to view all 25 buildings to enter monthly amts for...maybe i'll just do a
mostly flat file thing with 100 sites/locations field names in a table linked
to a month table? At least the db won't have 50 worksheets like in a ssheet
and I can query it to aggregate data. Don't mind me, i ramble...

I think you only need the following tables --

Buildings ---
BldgID – autonumber – primary key
BldgNo – text
Addr1 – text
Addr2 – text
City – text
State – text
ZIP – text
Plus – text

Contacts ---
ContID – autonumber – primary key
BldgID – number – long integer – foreign key
Type –
Phone –
FAX –
Cell –

Delivery ---
BldgID – number – long integer – foreign key
DelDate – DateTime
Utility – text – Oil, water, power
EOMInventoryAmt – number – N/A for power and water
DeliveredAmt – number
Measure – Text – GL – gallons, CF – cubic feet, KW - KWh
DeliveredPrice – number – single

--
KARL DEWEY
Build a little - Test a little


"Maarkr" wrote:

This has been a bad day for dbs - second big problem today. Oh well.
Turning a spreadsheet into a database for managing 3 utilities
(Oil,Power,water). Measurements are made each month that include use details
and current cost. This is for two locations. I have:
Date table
-ID, MoYear value
Locations table
- ID, two or more city names
Table for each utility measu
-ie, Oil inlcudes BldgNo (25 buildings), EOMInventoryAmt, DeliveredAmt,
DeliveredPrice, ID
Table for OilBuildings
- ID, BldgNo (or site)
This is so simple to do in a spreadsheet, but I'm trying to relate tables to
each other... each month has many locations with 3 utilities for each
location and many sites to measure. Thinking of just selecting the mo/yr and
using combo lookups for the location and type of utility, then have that with
many measurements subsheet with combo lookups for each site, but I would like
to view all 25 buildings to enter monthly amts for...maybe i'll just do a
mostly flat file thing with 100 sites/locations field names in a table linked
to a month table? At least the db won't have 50 worksheets like in a ssheet
and I can query it to aggregate data. Don't mind me, i ramble...

I loved the way you grouped the buildings, and I built a nice relational
entry form grouping bldgno, locationID, and utility(oil,kwh,water) into one
table (Site) of about 75 rows. It is fine, but you have to enter the date in
every month for each measurement (scroll thru each site to view the datasheet
listing measures by month). What they want is a form showing the month/year
(data is only input once monthly for each site) as the header, and view as a
subsheet ALL sites so they can fill in the associated measures each month.
So I'm thinking a fk in tblmeasures (delivery) to link to a tblDate showing
the first of each month, and a 1:1 from tblSite to tblMeasures (different
tables so I can change sites if necessary)??? But then I still can't view all
sites and measures on the form for that particular month?

"KARL DEWEY" wrote:

I think you only need the following tables --

Buildings ---
BldgID – autonumber – primary key
BldgNo – text
Addr1 – text
Addr2 – text
City – text
State – text
ZIP – text
Plus – text

Contacts ---
ContID – autonumber – primary key
BldgID – number – long integer – foreign key
Type –
Phone –
FAX –
Cell –

Delivery ---
BldgID – number – long integer – foreign key
DelDate – DateTime
Utility – text – Oil, water, power
EOMInventoryAmt – number – N/A for power and water
DeliveredAmt – number
Measure – Text – GL – gallons, CF – cubic feet, KW - KWh
DeliveredPrice – number – single

--
KARL DEWEY
Build a little - Test a little


"Maarkr" wrote:

This has been a bad day for dbs - second big problem today. Oh well.
Turning a spreadsheet into a database for managing 3 utilities
(Oil,Power,water). Measurements are made each month that include use details
and current cost. This is for two locations. I have:
Date table
-ID, MoYear value
Locations table
- ID, two or more city names
Table for each utility measu
-ie, Oil inlcudes BldgNo (25 buildings), EOMInventoryAmt, DeliveredAmt,
DeliveredPrice, ID
Table for OilBuildings
- ID, BldgNo (or site)
This is so simple to do in a spreadsheet, but I'm trying to relate tables to
each other... each month has many locations with 3 utilities for each
location and many sites to measure. Thinking of just selecting the mo/yr and
using combo lookups for the location and type of utility, then have that with
many measurements subsheet with combo lookups for each site, but I would like
to view all 25 buildings to enter monthly amts for...maybe i'll just do a
mostly flat file thing with 100 sites/locations field names in a table linked
to a month table? At least the db won't have 50 worksheets like in a ssheet
and I can query it to aggregate data. Don't mind me, i ramble...

a 1:1 from tblSite to tblMeasures
Are you going to have new tables every month? It seems like you are
thinking spreadsheet.
Use a form/subform.
You can have a Utility form with Bldg subform to enter deliveries and
default current date.
You can have a query that creates the monthly records to be filled out. You
can print this out for the field person to note the data and then use the
hard copy for the data entry person.

--
KARL DEWEY
Build a little - Test a little


"Maarkr" wrote:

I loved the way you grouped the buildings, and I built a nice relational
entry form grouping bldgno, locationID, and utility(oil,kwh,water) into one
table (Site) of about 75 rows. It is fine, but you have to enter the date in
every month for each measurement (scroll thru each site to view the datasheet
listing measures by month). What they want is a form showing the month/year
(data is only input once monthly for each site) as the header, and view as a
subsheet ALL sites so they can fill in the associated measures each month.
So I'm thinking a fk in tblmeasures (delivery) to link to a tblDate showing
the first of each month, and a 1:1 from tblSite to tblMeasures (different
tables so I can change sites if necessary)??? But then I still can't view all
sites and measures on the form for that particular month?

"KARL DEWEY" wrote:

I think you only need the following tables --

Buildings ---
BldgID – autonumber – primary key
BldgNo – text
Addr1 – text
Addr2 – text
City – text
State – text
ZIP – text
Plus – text

Contacts ---
ContID – autonumber – primary key
BldgID – number – long integer – foreign key
Type –
Phone –
FAX –
Cell –

Delivery ---
BldgID – number – long integer – foreign key
DelDate – DateTime
Utility – text – Oil, water, power
EOMInventoryAmt – number – N/A for power and water
DeliveredAmt – number
Measure – Text – GL – gallons, CF – cubic feet, KW - KWh
DeliveredPrice – number – single

--
KARL DEWEY
Build a little - Test a little


"Maarkr" wrote:

This has been a bad day for dbs - second big problem today. Oh well.
Turning a spreadsheet into a database for managing 3 utilities
(Oil,Power,water). Measurements are made each month that include use details
and current cost. This is for two locations. I have:
Date table
-ID, MoYear value
Locations table
- ID, two or more city names
Table for each utility measu
-ie, Oil inlcudes BldgNo (25 buildings), EOMInventoryAmt, DeliveredAmt,
DeliveredPrice, ID
Table for OilBuildings
- ID, BldgNo (or site)
This is so simple to do in a spreadsheet, but I'm trying to relate tables to
each other... each month has many locations with 3 utilities for each
location and many sites to measure. Thinking of just selecting the mo/yr and
using combo lookups for the location and type of utility, then have that with
many measurements subsheet with combo lookups for each site, but I would like
to view all 25 buildings to enter monthly amts for...maybe i'll just do a
mostly flat file thing with 100 sites/locations field names in a table linked
to a month table? At least the db won't have 50 worksheets like in a ssheet
and I can query it to aggregate data. Don't mind me, i ramble...