Reports :: Training Database - Group Report By Field List Row Source
Oct 30, 2014
I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.
In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.
Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:
------------------------------------------------------------
EMPLOYEE PROFICIENCIES
--Cannot Perform Task
----Cutting
----Trimming
----Grinding
--Can Perform With Assistance
----Painting
----Fixing
----Drilling
etc. etc. So in this case the Field itself would become the value being grouped. I know there has to be some logic either in a query or SQL.
View Replies
ADVERTISEMENT
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
Mar 9, 2013
I know that multivalue fields are not the best to use, but I have a simple database that they work fine in. My question is this: Is there a way to show the entire list of choices in the multivalue field on a report? Not just the ones selected, but all of them, whether checked or not? The multivalue field has a list of names and the user selects each name if they repond to a incident (fire, car accident, etc.). My report would show the entire list and count number of responses by each name during a date range. I want to show all of them on the report, with those not selected showing "0".
View 3 Replies
View Related
Dec 4, 2012
I am creating a training database and i have a courses table. And I need to somehow record all of the different employees that attended a course?
What i can do to make this happen? My employees have an employee number so is there a way of putting all of the employees together in one field? what field would the attendees be? currently is numeric which doesn't seem to work.
View 2 Replies
View Related
Aug 27, 2013
Everytime i make a report in Access, first thing i do is build a query and then use it as a record source. I try the other way, I go to create report design directly and do the drag and drop of fields.
View 2 Replies
View Related
Feb 27, 2015
I have a database that reports activities by region.
Each week, my regional volunteers report statistics on a number of club activities. This is in the form of zero to theoretically infinite activity reports that they enter on a website. I download the .csv from the website, add the activity reports to the activity table and send them a totals summary every now and then.
The summary report shows figures for every club in the region, even if no activity reports have been entered for that club that week or ever.
This works fine, including forcing the query to return zeros when no reports have been submitted for that club.
What I want to do is have the report also show (in brackets next to each figure) the position as it was X number of days previously.
I can make the query and report to show the figures now.
I can make the query and report to show the figures X days ago.
What I cannot work out is how to combine the two queries into one report source so that I can get
Club 1 100(50) 75(0) 45(45)
Club 2 0(0) 0(0) 0(0)
Club 3 20(19) 0(0) 200(50)
etc
If I try and make a third query that gets the sums from qryNow and the sums from qryXdaysago for each record in qryClubsByRegion, I get two lines for each club.
View 7 Replies
View Related
Jul 7, 2013
I have a report in access 2007, now i need to ask that i am creating new blank report and just like to to capture value from other report via textbox or any source (you may reccommend), for e.g in Report A i have months and their total amounts now i want to add both these fields in new Report B where i will do the same with other previous reports to create summary of accounts.
View 1 Replies
View Related
May 7, 2013
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct -
SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click()
If Right(strsql, 1) <> "'" Then 'check if statement was built
Else
strsql = strsql & ";" 'add trailing ; to statement
MsgBox strsql
DoCmd.OpenReport "tblincidents", acViewNormal, , strsql
End If
End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
View 6 Replies
View Related
Jun 13, 2014
How can I set the record source of a report to a saved query through VBA. I am trying to use the same report for a number of uses, all of the info on the report is the same, but the only difference is the query that the information is based on. I have this simple code below, how do I add a record source to it (if it can be done)
DoCmd.OpenReport "SellRPT", acViewReport, , , acNormal
View 2 Replies
View Related
Jul 24, 2015
I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)
This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.
However I then wanted to just pick one with exact matching ID's I select on a form.
I could not see an easy way to apply this without making another set of nested querys which seems a little excessive
Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database
' Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset)
' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
And dozens of variations over some hours but a variety of errors mainly "too few parameters."
View 3 Replies
View Related
Apr 6, 2013
I am trying to join a number of reports into one report. I have a generic report which displays a different dataset given the user's choice on a form. I created a collection where I can store multiple instances of this report (called mcolReportInstances) - this works just fine.
I was looking to combine all the reports in the collection into one report. To that end, I have created a report with a number of subreport controls but with no sourceobject. In the On_Open event of this blank report, I am trying to set the source object of the subreport to one of reports in my collection:
Me.Controls("Child" & i).SourceObject = mcolReportInstances.Item(strKey)
However, it keeps giving me the error 'Type mismatch'.
View 10 Replies
View Related
Oct 28, 2013
I need to build such a report, Unfortunately, I do not know how to use VBA to create each group (grouping by type) had a different header.
The problem becomes bigger that everything must generate queries dynamically cross, the number of columns in a given type may vary depending on the number or the size of the products in which they occur.
View 8 Replies
View Related
Mar 14, 2013
is there an easy way to do a conditional format on a report where the value is 1,2,3,or 4 from an option group. I want the 1=Yes, 2=No, 3=Maybe etc.
View 1 Replies
View Related
Sep 19, 2013
I have a report that has a number of grouping levels.I would like the highest grouping level to determine a new page.
View 2 Replies
View Related
Apr 11, 2013
In Report Design View, is there any way to copy the group, sort and total specifications that are in one report and paste them into another report?
View 4 Replies
View Related
Apr 2, 2015
I have a subreport and would like to have a header that repeats on each new page if it extends to multiple pages.I tried the suggestion I have seen to create a dummy group header based on an expression of =1 and set the group header "Repeat Section" property to "Yes".header now repeats before every record, instead of just once at the top of each new page.
View 1 Replies
View Related
Aug 19, 2013
I have a report that returns timesheet info on employees. The group-by is on the employee, and the detail records are projects billed-to, including how much time billed to each project by that employee.
Parts of the detail line are accomplished by dlookups, and I was hoping to sum() these values on each employee/group, in the employee footer area.
Per the image, the circles show where I hope to show the sum'd value from text boxes indicated by the arrows...
In the group footer on employee I've set text boxes to:
Code:
=Sum(DLookUp("ProjectLeaveHours","qryTimeRpt_LeaveOnly"))
...basically taking the dlookup accomplished in the detail line and trying to sum() it... No luck.
Code:
=Sum([RADefaultHours])
...using the name of the control where the dlookup is accomplished prompts me to declare the value of the "RADefaultHours" - indicating the report text box control doesn't know what I'm talking about...
Code:
=Sum([Reports]![rptTimesheetReport]![RADefaultHours])
...trying the above again, but hoping to properly declare what is being sum'd... No luck.
...I saw some posts regarding setting "running sum over group" - and I tried that on the text box in the footer where the sum() is being attempted, but no luck.
Can I do this this way? Perhaps I need to set some other variable that is more agreeable than a 'textbox with a dlookup' to do the sum when grouping?
View 4 Replies
View Related
Apr 28, 2008
Hi all,
I have a data set such as:
CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832
4003Severn Trent Water LimitedFRANKLEY3~00074831
I would like to only have 1 record of the Company name/ID with the RUIDs listed almost in one record something like...
CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832, ~00074831
Can we do this in SQL? (Specifically and Append query)
Thanks for any suggestions.
View 3 Replies
View Related
Jun 28, 2013
I must use XP/Access 2003 to solve this problem for reasons I won't get into.
I have a report with a Group by on City.
The users need to be able to sort by Coordinator within that OR by Client Name based on a selection they make on the form that opens the report.
-- I have tried to set .OrderBy
-- I have tried to set .RecordSource to different queries with order by hardcoded in them (I only have 2 sort options fortunately)
Is there a way to dynamically change the "Sorting and Grouping" defined in the report? This seems to be the only sorting that the report is responding to.
View 1 Replies
View Related
Jan 22, 2015
I know how to create a chart in an access report, put it in the group header or footer and link it such that the the grouping filters the chart.
My VP wants me to create a chart that compares each invididual providers episiotomy rate (something that is done to pregnant women during delivery) to the overall divisional rate on a monthly basis.
I know how to do this with 2 charts, ie 2 different group levels. One chart goes in the date grouping level (the overall rate) and another goes in the provider grouping level (the individual provide rate).
is there a way to have this in one graph in an access report?
View 5 Replies
View Related
Oct 9, 2014
I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.
View 6 Replies
View Related
Mar 3, 2013
I can not get my Access Report to give me a total number of pages by group. I've been able to have it give the correct page number per group but not the total number of pages in each group. I've looked at the threads and it appears that in order to get this, you must do a 2 part pass. 1st to get the page number and then to get the total papers per group.
I've used the code supplied and when I do a print preview, I get the message that it can not find the control, Me!ctlGrpPages. This is the control I created and placed in my page footer section of the report. I've also seen numerous references to the report, "Employee Sales by Country" in the Northwind database. I downloaded the database but could not find this report in the database.
View 3 Replies
View Related
Sep 17, 2014
I am relatively new to Access and i am wondering why i cannot find a specific field from a table on the control source when i am and attached it to a check box.
I have created the field on the table, and now i want to attached it to the check box on a form however when i go down the list of control sources i cannot see it.
View 3 Replies
View Related
Oct 23, 2012
I have an access database where I have added a new field, but I cannot find the name of that field in the Control Source list to link it from where the data will be entered. It IS in the table, too. Why in the world is it not working.
A user enters a New Job Opening into the form for New Job Opening. Later, a user enters a New Interview in the New Interview form.... when they enter the job opening number into the job opening number field, it auto-populates the other information previously entered into the New Job Opening form into an area within the New Interview form. I have made sure the new field is in the table for the New Job Openings table.
View 6 Replies
View Related
Dec 2, 2014
I'm trying to group data in a report from single table using grouping and sorting and I want the percentile of every record over group total. I'm using a query to fetch data from table, however I'm unable to get percentage of every single record over group total.
I want to display the report as attached image in single report. I'm unable to get data in "Perc" field. It's populating wrong values.
View 3 Replies
View Related
Feb 16, 2013
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values? Is there a feature provided by MS Access 2007 can enable such a conversion?
View 2 Replies
View Related
