# Table structure question



## pujo

Trying to create a table for entering daily values from a dozen or so devices.
The value will be a simple number ranging from 0 to 500,000

In my table (tblMeter), I have only the list of the meter numbers (Mtr1, Mtr2, Mtr3,...) for entering the daily value
How or what would I need to do so that the table keeps the daily values and create an accumulative for each Mtr?

A little confusing...

Thanks!


----------



## Micron

As a rule, I don't calculate anything in tables, notwithstanding that their capabilities are severely limited anyway.
Enter your data as records and use forms/reports to do calculations. Summing is easy - you group data and sum over the group with a Totals query.
Calculating consumption from meter readings is a bit more complex.


----------



## pujo

Micron said:


> As a rule, I don't calculate anything in tables, notwithstanding that their capabilities are severely limited anyway.
> Enter your data as records and use forms/reports to do calculations. Summing is easy - you group data and sum over the group with a Totals query.
> Calculating consumption from meter readings is a bit more complex.


Yea, I get that but in the actual table, I am thinking that other than the actual value, I would at least need a date/time for each to keep track right?


----------



## Micron

Definitely need a date field if you want to co-relate a reading for any particular meter with the date it was read. Since you're asking that, I'm thinking you may not realize that the meters should be in their own table because they are their own entity. The readings should be in a related table, so no dates in your meters table for readings. For install date, date of manufacture, any date that pertains to the _meter_ then sure. Otherwise, more like

*tblMeters*

MeterIDpkSerialNoOEMLocationETC.1Mtr11HOP123 Main St2Mtr12Siemens14 King Str3Mtr13Square Detc4Mtr14Eatonetc5Mtr15etcetc

*tblReadings*

ReadingIDpkMeterIDfkReadDate116/01/2022236/01/2022326/01/2022456/01/2022


----------



## pujo

Micron said:


> Definitely need a date field if you want to co-relate a reading for any particular meter with the date it was read. Since you're asking that, I'm thinking you may not realize that the meters should be in their own table because they are their own entity. The readings should be in a related table, so no dates in your meters table for readings. For install date, date of manufacture, any date that pertains to the _meter_ then sure. Otherwise, more like
> 
> *tblMeters*
> 
> MeterIDpkSerialNoOEMLocationETC.1Mtr11HOP123 Main St2Mtr12Siemens14 King Str3Mtr13Square Detc4Mtr14Eatonetc5Mtr15etcetc
> 
> *tblReadings*
> 
> ReadingIDpkMeterIDfkReadDate116/01/2022236/01/2022326/01/2022456/01/2022


Correct, this is entity based and each production line containing meters and related data are in their own table.
and, not wanting to duplicate and data as well as calculations are on the form/report and not in the table.


----------



## Micron

Does that mean all the line fields are repeated for each meter on the line? If so, that isn't right.  Anyway, I don't want to hijack your thread by continually commenting on the db design so if you have your answer re: a date field, I suppose you can mark this one as solved, unless you're wanting something else.


----------



## pujo

Micron said:


> Does that mean all the line fields are repeated for each meter on the line? If so, that isn't right.  Anyway, I don't want to hijack your thread by continually commenting on the db design so if you have your answer re: a date field, I suppose you can mark this one as solved, unless you're wanting something else.


By all means, do not stop!
Each system will have the same fields however, the quantity of meters and meter numbers will be different
PK: System1MtrID
      MtrNumber
      MtrSize
      MtrManufacturer
      MtrType
      MtrMin
      MtrMax


----------



## Micron

I think you're saying you have a table for each area/dept/line/whatever. Not a good idea. That thing (whatever it is) is its own entity thus its own table of locations. The meters are another singular entity thus a meter table. The taking of readings is its own entity (it happens to be an activity rather than a tangible thing but that doesn't matter). The readings table would have a fk field related to the area (e.g. AreaIDfk) in addition to the meter id fk that I posted above. IMO the hardest thing to do in db design is to get the table and relationship structure correct. Some are easy if you have experience, which I have lots of, but I can still come across situations where I scratch my head for quite some time. A columbarium was the latest example of that.

*tblReadings*ReadingIDpkMeterIDfkAreaIDfkReadDate1116/01/22These 4 meters are all in the same area (1) which is line12316/01/223216/01/224516/01/22


*tblAreas*AreaIDpkDescriptionETC.1Line12Line23Line3


----------



## pujo

Micron said:


> I think you're saying you have a table for each area/dept/line/whatever. Not a good idea. That thing (whatever it is) is its own entity thus its own table of locations. The meters are another singular entity thus a meter table. The taking of readings is its own entity (it happens to be an activity rather than a tangible thing but that doesn't matter). The readings table would have a fk field related to the area (e.g. AreaIDfk) in addition to the meter id fk that I posted above. IMO the hardest thing to do in db design is to get the table and relationship structure correct. Some are easy if you have experience, which I have lots of, but I can still come across situations where I scratch my head for quite some time. A columbarium was the latest example of that.
> 
> *tblReadings*ReadingIDpkMeterIDfkAreaIDfkReadDate1116/01/22These 4 meters are all in the same area (1) which is line12316/01/223216/01/224516/01/22
> 
> 
> *tblAreas*AreaIDpkDescriptionETC.1Line12Line23Line3


Okay, I see what you are saying. 

Thanks again!


----------

