By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

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

P: 51
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
Jan 29 '14 #1
Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,342
Show us the code where you tried to incorporate max.
Jan 29 '14 #2

P: 51
In an unbound textbox on the report:

Expand|Select|Wrap|Line Numbers
  1. Max([Dec2013_data]![Report_Date]) & " " & [#Inches]
Jan 29 '14 #3

100+
P: 104
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.
Jan 29 '14 #4

Rabbit
Expert Mod 10K+
P: 12,342
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.
Jan 29 '14 #5

P: 51
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Dec2013_data.MTF_Code, Max(Dec2013_data.Report_Date) AS MaxOfReport_Date
  2. FROM Dec2013_data LEFT JOIN MTF ON Dec2013_data.MTF_Code = MTF.MTF_Code
  3. GROUP BY Dec2013_data.MTF_Code;
I then tried another query but didnt work.

this was the 2nd qry

Expand|Select|Wrap|Line Numbers
  1. SELECT [Copy Of qry_dec2013].MTF_Code, [Copy Of qry_dec2013].MaxOfReport_Date, Dec2013_data.[#Inches]
  2. FROM [Copy Of qry_dec2013], Dec2013_data
  3. GROUP BY [Copy Of qry_dec2013].MTF_Code, [Copy Of qry_dec2013].MaxOfReport_Date, Dec2013_data.[#Inches];
  4.  
Jan 30 '14 #6

Rabbit
Expert Mod 10K+
P: 12,342
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.
Jan 30 '14 #7

zmbd
Expert Mod 5K+
P: 5,332
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.
Jan 30 '14 #8

P: 51
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.
Jan 30 '14 #9

P: 51
ok so I think I am getting it now.

First Query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Nov2013_data.MTF_Code, Max(Nov2013_data.Report_Date) AS MaxOfReport_Date
  2. FROM Nov2013_data LEFT JOIN MTF ON Nov2013_data.MTF_Code = MTF.MTF_Code
  3. GROUP BY Nov2013_data.MTF_Code;
  4.  
Second query
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.MTF_Code, Nov2013_data.POC_Name, Nov2013_data.[#Inches], Nov2013_data.Report_Date
  2. FROM Nov2013_data RIGHT JOIN t1 ON (Nov2013_data.report_date = t1.MaxofReport_Date) AND (Nov2013_data.MTF_Code = t1.MTF_Code);
  3.  
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.
Jan 30 '14 #10

zmbd
Expert Mod 5K+
P: 5,332
"" 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. (^_^)
Jan 30 '14 #11

P: 51
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 :)
Jan 30 '14 #12

P: 51
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!
Feb 2 '14 #13

Post your reply

Sign in to post your reply or Sign up for a free account.