Reports: textbox to sum a facility's #inches by last date reported

[osmosisgg]

Hello,

I have tried various ways and cannot get through this one.
A [facility] will report [weekly] their [#inches]; this is for tracking measurements only. I am looking to get the sum of #inches for all facilities based only on the [last reported date] of the reported month.

Based on [last reported date] of each [facility] , sum [#inches]

So, facility A reported on 1-3-14 (5 inches), 1-9-14 (10 inches), 1-15-14 (40 inches), and 1-21-14 (2 inches)

1-21-14 = 2 inches for Facility A
1-15-14 = 17 inches for Facility B (following ex above)
...just using 2 for the example

Total Sum = 19 inches for January

I tried incorporating Max with no winners.

Thank you

[Rabbit]

Show us the code where you tried to incorporate max.

[osmosisgg]

In an unbound textbox on the report:

Max([Dec2013_data]![Report_Date]) & " " & [#Inches]

[GKJR]

Have you tried using a text box with the Running Sum property set to Over Group? You could then group your data by either Facility or Date, whichever works best for you. If you grouped your records by date, you can then break them down by either month or week.

[Rabbit]

That would just give you a max date appended with whatever the most current inches is.

You need to modify the record source to join to an aggregate query that returns the max date per facility so you can filter out the unneeded rows.

[osmosisgg]

The report is grouped on the facility and the dates of the reported #inches are most recent.

I managed the below in a query and get the facility and the max date, but if I add the {#inches] to it, I get a listing of all the dates again

SELECT Dec2013_data.MTF_Code, Max(Dec2013_data.Report_Date) AS MaxOfReport_Date
FROM Dec2013_data LEFT JOIN MTF ON Dec2013_data.MTF_Code = MTF.MTF_Code
GROUP BY Dec2013_data.MTF_Code;

I then tried another query but didnt work.

this was the 2nd qry

SELECT [Copy Of qry_dec2013].MTF_Code, [Copy Of qry_dec2013].MaxOfReport_Date, Dec2013_data.[#Inches]
FROM [Copy Of qry_dec2013], Dec2013_data
GROUP BY [Copy Of qry_dec2013].MTF_Code, [Copy Of qry_dec2013].MaxOfReport_Date, Dec2013_data.[#Inches];

[Rabbit]

You need to take the first query and join it back to the source. You can't merely include it as part of the aggregation.

[zmbd]

osmosisgg :

Do you have a table structure like this:

MMMYYYYY_data
[...otherfields. ..]
[MTF_Code]
[Report_Date]
[...other fields...]

and then you have something along these lines:

Table: JanYYYY_data, FebYYYY_data, MarYYYY_data, ... NovYYYY_data, and DecYYYY_data

With YYYY = to 19##, ..., 2000, 2001, 2002 ..., 2012, 2013

If so you should look at: > Database Normalization and Table Structures.

[osmosisgg]

Hello,

I have tables for the facilities, a form that writes to tbl_report, queries to break the tbl_report into data months, and then a report where the data comes from the specified data month table.

So, I would like to have one report(which I have already) that shows what each facility reports and the date reported.

The next report, I would like to have only the facilities' newest date (so the last date reported in the data month)reported with the #inches and most likely a total of that report.

by taking the first query and joining it to the source- the first query as above does give me the latest date and facility name. I didn't see an aggregation on the first query. Is using Max considered aggregating?

Is the next step to take the query that works and join it back to the original table the data came from?

Sorry, I don't know all the lingo/syntax.

[osmosisgg]

ok so I think I am getting it now.

First Query:

SELECT Nov2013_data.MTF_Code, Max(Nov2013_data.Report_Date) AS MaxOfReport_Date
FROM Nov2013_data LEFT JOIN MTF ON Nov2013_data.MTF_Code = MTF.MTF_Code
GROUP BY Nov2013_data.MTF_Code;

Second query

SELECT t1.MTF_Code, Nov2013_data.POC_Name, Nov2013_data.[#Inches], Nov2013_data.Report_Date
FROM Nov2013_data RIGHT JOIN t1 ON (Nov2013_data.report_date = t1.MaxofReport_Date) AND (Nov2013_data.MTF_Code = t1.MTF_Code);

It works!! Holy Moley! Now, I guess I should actually give the test queries real names and not just t1 and t2 :)

Thanks to y'all, one success for the day.

[zmbd]

"" Is using Max considered aggregating? ""
Yes

""Sorry, I don't know all the lingo/syntax""
As long as you get it close, most of us can follow, occationally I'll get lost though - but I'll ask a question in that case. (^_^)

[osmosisgg]

Is there a better way to get the same result as what I just posted?

My job field requires me to simplify complex rules and regulations, but when it comes to access, and with no prior education/training on it, I get frustrated quickly. I embrace "There are no stupid questions" until I read what I write when trying to accomplish something in access LOL :)

[osmosisgg]

After much thought, I went back in the db and cleaned up. I thought about the years issue - what happens next year? I will have to go in and change them ALL. Not something that is done within 5 mins. (yeah, I clock watched myself)

It's pretty now :) but the most important thing- it freaking works! YAY!