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
12 1249
Show us the code where you tried to incorporate max.
In an unbound textbox on the report: - Max([Dec2013_data]![Report_Date]) & " " & [#Inches]
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.
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.
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];
-
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 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.
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.
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 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. (^_^)
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 :)
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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....
|
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?...
|
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)...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |