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
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];
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.
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?
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?
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.
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?
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.
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.
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?
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.
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.
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.
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.
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
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....
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
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.
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?
