If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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... |
#2
|
|||
|
|||
Business Utilities design
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... |
#3
|
|||
|
|||
Business Utilities design
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... |
#4
|
|||
|
|||
Business Utilities design
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... |
Thread Tools | |
Display Modes | |
|
|