Query, Form & Report Design & Layout

[kfbeal]

I have an access database with a single table that has 3 fields; Meter, Date/Time & KW. The data has multiple meters with data that is stored in 15 minute intervals.

We need to be able to pull the Max KW for each Meter on a 15 minute interval for a specified date range .

We also need to pull the Max KW of each Meter on a 60 minute average for each hour for a specified (input) date range.

Some of these meters will be totalized so would it be possible to totalize a selected group of Meter's Max KW based on the specified date range.

I've been able to create qryGetMeterNumber query pulling back the DISTINCT meter numbers. I then created a form fmGetKW and added combo boxes for Meter Number and 2 other combo boxes for the Start & End dates.

I then created a qryGetKW query to pull the meter, date/time & kw with the meter number tied to the form combo box of Meter Number.

Is there an easy way to multiply all of this or do I have to run each one separately? I'm used to generating basic queries and getting easy data, but I feel I'm over-thinking this whole process.

Thanks,


Keith

[CJ_London]

would help if you provided some example data and the outcome required. I struggle to understand what this means

We also need to pull the Max KW of each Meter on a 60 minute average for each hour for a specified (input) date range.

[ssanfu]

Welcome to the forum, Keith.

I agree with Ajax- we need more info...

I know you are already using the dB, but "Date/Time" is a POOR field name.

How is the data (MeterNumber, KW, ReadingDateTime) being input into the dB?

When you say "We need to be able to pull ....", do you mean view it on a form or be able to print a report, of both???

[ArviLaanemets]

You need a tables like
tblMeters: MeterID, [MeterNo], MeterInfo; (You can use manually entered MeterNo as PK, and omit autonumeric MeterID, or you can use both of them. In case you use both, MeterNo must be indexed and unique)
tblMeterGroups: MeterGroupID, [MeterGroupNo], MeterGroupInfo; (You can use manually entered MeterGroupNo as PK, and omit autonumeric MeterGroupID, or you can use both of them. In case you use both, MeterGroupNo must be indexed and unique)
tblMeterGroupMeters: MeterGroupMeterID, MeterGroupID/MeterGroupNo, MeterID/MeterNo; (You use PK fields of tblMeteres and tblMeterGroups for links. The pair of them must result as composite unique index)
tblMeterReads: MeterReadID, MeterID/MeterNo, ReadTime, KW (You use PK fields of tblMeteres as link. MeterID/MeterNo and ReadTime must result as composite unique index).

All totals you need, you can get running different queries. You can view results either in form(s) or in report(s) with such quer(y/ies) as source(s).

[kfbeal]

I hope this helps:

[CJ_London]

OK so taking one meter as an example (5012), from the data on the right, what is the calculation that arrives at 45.28 - from your description and the data provided it should be 36.39. And the calculation that returns 38.25? I would expect to see 4 values per hour, but based on the data provided, would this be 29.12 (based on averaging the 3 values) or 21.84 (based on averaging 4 values where one is 0)

[kfbeal]

OK so taking one meter as an example (5012), from the data on the right, what is the calculation that arrives at 45.28 - from your description and the data provided it should be 36.39. And the calculation that returns 38.25? I would expect to see 4 values per hour, but based on the data provided, would this be 29.12 (based on averaging the 3 values) or 21.84 (based on averaging 4 values where one is 0)

Sorry, the database structure was just a snippet to show how the structure was, not all of the data (which was just bogus data for example) was included.

The 45.28 would be the peak value of meter 5012 between 4/27/2020 & 5/26/2020. It's just not listed in the database structure data.

The same goes for 32.38 is the peak value of 5014 between 4/27/2020 & 5/26/2020 and so on. Then the total Peak KW is the SUM of all of the meter's (5012, 5014, 5029) Peak KW.

Hopefully that clears it up.

[ssanfu]

Hmmmm, it would be nice if you were to post the dB.
Since my questions weren't answered, I'll step away and not cause background noise.




Good luck with your project..........

[kfbeal]

Hmmmm, it would be nice if you were to post the dB.
Since my questions weren't answered, I'll step away and not cause background noise.




Good luck with your project..........

I believe I did somewhat in the image, except for how the data actually gets put into the database, which is manually imported via Excel file. The image shows the output requested within the table, if it's at all possible with a button to display & export the data. I had issues attaching it for some reason so I compressed it.

Eventually these meters will dump this data into an SQL database which I would use an Access Query to query that data into this same database.

VLP-Metering.zip

[CarlettoFed]

You should explain these anomalies with respect to the data in the database

[kfbeal]

Sorry, the database structure was just a snippet to show how the structure was, not all of the data (which was just bogus data for example) was included.

I just threw data in there to display it merely as an example, it's not actual database data. I was in a hurry to get something together to provide examples.

[CJ_London]

rather than providing example data that 'doesn't add up' to the result required. Provide example data that does. Or adjust your result to reflect the data actually provide. At the moment we have the equivalent of example data of 1, 2, 3, 7, 9 and the answer is 6.

I was in a hurry to get something together to provide examples.

So your thinking is that 'save me some time, hopefully someone will understand'. In reality it is taking more time (and ours) as we try to understand your requirement.

Since you have not confirmed my understanding of your calculation as expressed in post #6 I have to assume I am wrong. So I also will step away.