473,320 Members | 1,853 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
12 1249
Rabbit
12,516 Expert Mod 8TB
Show us the code where you tried to incorporate max.
Jan 29 '14 #2
In an unbound textbox on the report:

Expand|Select|Wrap|Line Numbers
  1. Max([Dec2013_data]![Report_Date]) & " " & [#Inches]
Jan 29 '14 #3
GKJR
108 64KB
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
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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
5,501 Expert Mod 4TB
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
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
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
5,501 Expert Mod 4TB
"" 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
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
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

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

Similar topics

1
by: samotek | last post by:
Filter the last date only I am trying t0 filter only the last date of the query,but i cannot manage that. My query is the following : SELECT Sum(.liters) AS SumOfLiters, orders.invoicedate...
2
by: solar | last post by:
I have a problem with building a totals query showing data for the last date only.Under the last date i understand the last date of field invoicedate.This invoice date is in the table orders....
5
by: Hardy Wang | last post by:
Hi: Are there any algorithms I can use, that based on a given date and a culture code (different cultures may have different beginning of week), I can get first and last date of the current week?...
0
by: Lee Harr | last post by:
I wrote a function to return the first date of a given week (and a few related functions) : -- return the first date in the given week CREATE or REPLACE FUNCTION week_start(integer, integer)...
9
by: ice | last post by:
Hello, I have a couple of tables. The client tables and the contacted tables. I am not sure how to start on this, what I need is a way to query all my clients then show any client that the...
6
lobstah24
by: lobstah24 | last post by:
OK...I'm new to MS Access but I am progressing pretty well. I've made a small and simple D Base for our church to track member donations. The form used to enter donations has 4 entry...
0
by: shihab | last post by:
Hai all, How to keep a multiline + vertical scrollbar textbox on the LAST line in Asp.net with c# ,when refreshing page? thanx, sihab
2
oszapped
by: oszapped | last post by:
I am new to creating SQL queries and need some help. I am trying to query from three tables. 1 - A table with a list of Drawings (DRAWINGS) 2 - A table with the Revision history of the Drawings...
3
reginaldmerritt
by: reginaldmerritt | last post by:
Hello I'm have a form displaying records from a table holding details of events. I'm using DFirst and DLast to work out what is the First and Last date. I have the following code which...
10
by: mfarley | last post by:
Hello there, I'm new to access and I have a simple question. I'm writing a form in MS Access 2007 with a date field, I'd like the field to be populated by the last date entered instead of today's...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.