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]

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

MeterIDpk	SerialNo	OEM	Location	ETC.
1	Mtr11	HOP	123 Main St
2	Mtr12	Siemens	14 King Str
3	Mtr13	Square D	etc
4	Mtr14	Eaton	etc
5	Mtr15	etc	etc

tblReadings

ReadingIDpk	MeterIDfk	ReadDate
1	1	6/01/2022
2	3	6/01/2022
3	2	6/01/2022
4	5	6/01/2022

 

[pujo]

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

MeterIDpk	SerialNo	OEM	Location	ETC.
1	Mtr11	HOP	123 Main St
2	Mtr12	Siemens	14 King Str
3	Mtr13	Square D	etc
4	Mtr14	Eaton	etc
5	Mtr15	etc	etc

tblReadings

ReadingIDpk	MeterIDfk	ReadDate
1	1	6/01/2022
2	3	6/01/2022
3	2	6/01/2022
4	5	6/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]

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
ReadingIDpk	MeterIDfk	AreaIDfk	ReadDate
1	1	1	6/01/22	These 4 meters are all in the same area (1) which is line1
2	3	1	6/01/22
3	2	1	6/01/22
4	5	1	6/01/22

tblAreas
AreaIDpk	Description	ETC.
1	Line1
2	Line2
3	Line3

 

[pujo]

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
ReadingIDpk	MeterIDfk	AreaIDfk	ReadDate
1	1	1	6/01/22	These 4 meters are all in the same area (1) which is line1
2	3	1	6/01/22
3	2	1	6/01/22
4	5	1	6/01/22

tblAreas
AreaIDpk	Description	ETC.
1	Line1
2	Line2
3	Line3

Okay, I see what you are saying.

Thanks again!