Group By Month Or Develop Monthly Average?
Sep 2, 2006
Hello, all.
I posted this about a month ago, but at that time I was running myself ragged and through too many problems at once. I stepped back and made some good progess. I put this in the General forum because it could encompass VBA, queries, and reports
I have a main report (Percentage Report) that has 4 subreports in it. Each subreport is based on a query that's run from three other queries. Its a neatly tangled mess, but it works fine.
The queries all count and calculate percentages for a pass rate of inspections on maintenance. There's an over-all/basic percentage that simply totals everything and divides for a percentage. There's also a "maintenance" percentage that only takes into account inspections done on maintenance (as opposed to various programs and processes.) Those both work fine for any given time period.
The third (and final) percentage deducts 0.5 points for each of a specific list of inspections (safety and other violations.) This works fine so long as you're only looking at a month's worth of data. The problem comes when you want to view any time period larger than that (quarter, semi-annual, annual.)
Basically, you end up subtracting a sum from an average and you end up w/ totally inaccurate numbers. I just can't quite figure out how to effectively either group by month or how to average the deductions based on the months covered.
I just finished completing this whole thing, and I'm pretty much done for tonight. Any help would be great.
----------------------------------------
Key words: sum totals, report grouping, report conditional format, alternate row colors (greenbar), count, calculate, percent
View Replies
ADVERTISEMENT
Feb 28, 2015
On the below data i am trying to get average monthly BALANCE, based on the following query ....but i only getting all records with actual balance and not month wise average balance of the customers......
-----------------------------------
SELECT [customer profit].[Customer Number], AVG ([customer profit].[VAL_BAL]) AS average, Month ([customer profit].[Balance Date])
FROM [customer profit]
GROUP BY [customer profit].[customer Number], [customer profit].[VAL_BAL], [customer profit].[Balance Date];
[Code] .....
View 4 Replies
View Related
Jul 29, 2014
My database has a crosstab query that counts the number of hits by month. I have two columns that are named "Count of Material"(Frequency) and "Sum of ordered Qty"(Total ordered). I need add a colum to find the Average Monthly Demand by dividing the Total ordered/Frequency/number of months in the query. I have tried everything to make it work but can't.
View 8 Replies
View Related
Nov 14, 2013
I am currently working on a small database to track my own investment records.
I would like to create the function which enable to count the number of records that has exceeds the average value during that period.
Following is the SQL that I have managed to produce:
SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1
FROM [ED 2013]
WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End]
GROUP BY [ED 2013].Symbol;
This SQL does work. However, when I try to add the Avg function. The Query stop working.
Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?
View 1 Replies
View Related
Jul 7, 2014
I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer.
My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal.
This is in the DateFooter section of the report.
I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]).
Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal].
I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn't ask me for an input, there was nothing in the ubound control in the report footer.
I have search for an answer, but all I find is using a query. Is what I am attempting to do possible? If so, how?
View 5 Replies
View Related
Jan 29, 2008
I have a file with hundreds of home builders. It has three fields for this problem.
Table = Permit
field = Per_date (Date Field) Date BLD record appears in table.
field = BLD (Char) Builder number or Name
field = SQFTArea (N) Size of home.
1. I need to be able to get the average for (SQFTArea) for all records greater than 950 (SQFTArea).
2. For year 2007.
3. Grouped by Builder (BLD).
Example: Per_date - BLD - SQFTArea
Record: 2006 - 012 - 0500 Not becouse of 2006 and 0500
Record: 2007 - 012 - 2500
Record: 2007 - 012 - 3500
Record: 2007 - 058 - 2000 Not becose of BLD=058
Answer is for 2007 BLD 012 has an average of 3000
BLD 058 would be figured with it's self at average=2000 if the only record with this number or used with any other records that are BLD 058.
I have asked for help in the past but must likely my examples not that great. Here’s hopping. Hopping?? I hope I got that right being as I'm not a rabbit. Spell checker no help on this one.
Bob
View 2 Replies
View Related
Sep 9, 2013
I'm creating an accounts package..I've used access chart wizard to create a chart that shows total gross income per month.This displays correctly but the months start at January and end in December. It would be more useful if the months could start and end for the financial year. The syntax generated by access for the current implementation is:
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
How do I edit this to make say September my start date?
View 2 Replies
View Related
Aug 13, 2013
May be VBA is a better solution for this:
Say for Material A: I need Access to see that the 1st date is 05/01/2013 and say 365 days out or 12 months (05/01/2013-04/01/2014) the average is 158.6 then 06/2013-05/2014 the average is 146.667 and so on.
Every month is not going to be listed in the results, if there is no month then assume 0.
Sample data attached.
View 7 Replies
View Related
Sep 26, 2013
I am trying to get the average of a select group of records within a query. It appears the davg function should give me what I need, however my query returns no results. Here is a sample of my data.
Item Cost Basis Group Cost
1HF20812 1HF208 6.17
1HF20816 1HF208 8.63
1HF20820 1HF208 9.44
Here is the davg string I am trying to use.
Group: davg("Cost","Cost Basis Group")
View 2 Replies
View Related
Jun 1, 2007
I'm not sure why I can't think of how to fix this, but I'm missing something
I have a table with a column for 'birthdays' The format is mm/dd, and a form for the users to put in their birthday.
When you look at the table, it may look like 05/12. But if you click on the cell it's shows it as 05/12/2007.
The problem I'm running into, I have a Data Access Page that displays all the birthdays grouped by month. But for some reason all the entries from 2006 are grouped by month, but then all the entries we've made in 2007 are grouped together by themselves.
so we have
(all the entries we made in 2006)
June
July
etc
but then at the end of the list
we have again
June
July
etc (these are the ones we entered in 2007)
Is there a way to store a date as only mm/dd?
Or is there a way to make the DAP group these by month, and ignore the year?
View 7 Replies
View Related
Aug 1, 2007
Hi guys.
Can anyone tell me how to group the results of a query by month in a date field?
MonthName(DatePart("m",[Date])) ... this gives me the month-name but I cannot order the results. I'd like smth like 01 (for Jan), 02 (for Feb) ect.
so from a date I need to get only the month.
Thanks in advance.
View 2 Replies
View Related
Aug 21, 2007
I have a table column with dates like
02/05/06
02/06/06
...........
I need to calculate the next date[Due Date] in 6 months, which is simple
[Patient's file].[Last Appointment Date]+180 as [Due Date]
and I have to group the Due Date by the next month. Lets say the due date is 08/25/07, so it has to be grouped as September group at the top or the list. It should be displayed as "September, 2007"
How should I assing let say 08/01/07 - 08/31/07 dates to September groups. I mean the statement should be universal for any day and any month of the year.
Thank you.
View 1 Replies
View Related
Jul 5, 2005
Hi All.
In a query I grouped DATE by month Date:Format([Date],"yyyy mmm") to calculate visits in each month. My problem is: How to create dialog where user can enter date based only on YEAR and MONTH and enter in this dialog start_date and end_date?
Thanks.
View 1 Replies
View Related
Aug 3, 2012
I would just like to know if it is possible to group Months together: this is what i have did so far :
Code:
SELECT Count(Source.ID) AS CountOfID, Clusters.Cluster_Desc, Department.Dept_Desc, DatePart('m',Source.Day_Month_Year) AS [Month], Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action
FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID
GROUP BY Clusters.Cluster_Desc, Department.Dept_Desc, Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action, Source.Day_Month_Year;
View 1 Replies
View Related
May 9, 2013
I try this code
Code:
SELECT Format(ReportTbl.lot_date,"mm-yyyy") AS Lot,
Round(Avg(ReportTbl.avg_dat),2) AS DataAvg,
Round(Avg(ReportTbl.avg_len),2) AS LenAvg,
Round(Avg(ReportTbl.avg_in),2) AS InAvg,
Round(Avg(ReportTbl.avg_out),2) AS OutAvg,
Round(Avg(ReportTbl.avg_thi),2) AS ThiAvg,
Round(Avg(ReportTbl.avg_moi),2) AS MoiAvg
FROM ReportTbl
GROUP BY Format(ReportTbl.lot_date,"mm-yyyy")
ORDER BY ReportTbl.lot_date;
But it not working and have warning box with "you tried to execute a query that does not include the specified expression 'ReportTbl.lot_date' as part of an aggregate function"
and when I try
GROUP BY Month(ReportTbl.lot_date),Year(ReportTbl.lot_date) or
GROUP BY datepart("m",ReportTbl.lot_date),datepart("yyyy",R eportTbl.lot_date)
or other code that I can search in google
I get the warning message that resemble with above message
and when I try to GROUP BY ReportTbl.lot_date it work but the result does not meet the requirements.
View 3 Replies
View Related
Nov 14, 2012
my database was working fine until a new month was added in the attendance now each staff member has about 20 records when they should only have two. if i remove the second month it corrects its self but i can't do that permanently also i have office 2010 but the office has 2003 so i saved it to 2002-2003 file format and it was working fine up until today when it has started to give and error message Which Reads: Your Microsoft Office Access database or project contains a missing or broken reference to the file 'ACEDAO.DLL' version 12.0
View 6 Replies
View Related
Jul 21, 2015
I am trying to make a query that counts the orders on a day of an specific month selected from a form.
The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.
Any way of making a query that adds the days with no records with a count value of 0?
View 3 Replies
View Related
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Feb 22, 2013
I have to develop a software that allows users to print data from a GUI. The GUI is a form containing several fields: the user can choose only the fileds he wants to print (I thought to a checkbox to choose the needed fields)
For each field the user can specify: font, x,y coordinate to locate it into the label to be printed
The user can decide also the format (height,width dimensions) of the label
I imagine that the great difficulties is to find the function to create the string to sent to the printer....
View 1 Replies
View Related
Oct 26, 2005
hi,
Can anybody help me to create Auto filters as in Excel in Access Database ?
i dont have any knowledge of programming !
thanks,
View 2 Replies
View Related
Apr 15, 2015
I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.
ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2.
[ABCTABLE]
ABCORDER
ABCPART
[code]...
View 4 Replies
View Related
Feb 16, 2007
Hi guys!!!!
I try to find an answer in the forum about "Average Fields",but ican't
I am confused:(
I wan't to export Avg Of the fields like in the panel below:
View 2 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Mar 19, 2012
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
View 10 Replies
View Related
May 29, 2015
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
View 2 Replies
View Related
Mar 28, 2013
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies
View Related
