Reports :: Grouping With Information From Multivalue Fields
Feb 6, 2015
I have a table which now contains a couple of hundred records with more than thirty fields each, and will ultimately contain over 1000 records. Some of these fields use the multiple value feature, and the fields and the forms which fill them work beautifully. Now comes the challenge.
Without going into detail that I'm not at liberty to share, I can say that there are different offices which have different people who are responsible in different ways for the work covered in these records. It is possible for each record to have multiple people assigned to it from the same office. This requires setting the control that shows the people from each office to allow multiple values. I need to be able to create a report which will allow me to hand a list of all the records each person is responsible for in the office to the person responsible, in spite of the fact that this will mean records will show up on more than one person's report.
Before I knew that there might be multiple people in the office for each record, I created great reports grouping and ordering by this office's control and field. Now, since they allow and store multiple values, I can't use them any more. I need to be able, as I said above, to get the same effect. I think the answer lies in some sort of calculated field or formula that applies text filters. What I need to be able to do is look in the field for this office and see if a person's name is contained in the field for that particular record, regardless of who else might be in the field, too. I need to be able to use the results from this filter or calculation or formula to generate something I can use in the group and order by processes.
I'm trying to create a report with data from 7 different tables. It's supposed to be an assignment overview for a transfer company (driving people from A to B, dunno how to call it in English). I put assignment and customer details into the page header, which is working out fine so far. But now I want to show transfer details and it's proving to be a bit troublesome.
When creating the assignment, the user can choose whether the transfer goes from/to an address or from/to an airport. Depending on which one he chose the data is stored in different tables. To determine whether it is an outward journey/collection, outward journey/target, return journey/collection or return journey/target, I put a field in each of those tables ("Schritt", its value being either 1, 2, 3 or 4).
To visualize I made screenshot of the tables in relationship view, but since I'm a new member i can't seem to post images, so I'm giving you a pseudo URL...
[abload (dot) de (slash) img (slash) transeren40p1r.png]
Having the "Schritt" value for each step in either one or another table (address(es) or flight data) is making it a bit hard for me to wrap my head around the problem. Is that doable with expressions or is there a way in VBA to solve this problem?
I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.
Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.
I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.
So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".
So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...
Got relationships between: "contact name" fields in both "table2" and "table3" "project number" fields in both "table1" and table2"
In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?
Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????
In my table, I setup a multivalue field. In the screen, I can check off more than one selection. It stores the selections in the table as value1, value2, value3, etc...which is fine. However, when I go back to review the entry in a report or a screen, it displays it exactly the same as it is stored in the table - value1, value2, value3, etc...Because of size limiations, the field is only so wide, thus it only shows value1, va...and then the rest cut off.
Regardless of how wide I make the field, all of the values will not be displayed, plus, if there is only one value, it will be a waste of space. What is the best way to display one (if there is only one selected) or all of the values selected ? Is there a way to display then vertically, like value1, (next line) value2, (next line) value3 ?
I have a query that gathers information from the tables in my database and returns something like this:
Code: Field Pest 1 Insect1 1 Insect2 1 Insect3 2 Insect1 2 Insect3 2 Insect4
Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:
Code: Field Pest 1 Insect1, Insect2, Insect3 2 Insect1, Insect3, Insect4
The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query.
Is there another field that can be used beside the lookup field in an Access table? I am currently using the lookup field as a multivalued lookup field and I am limited to the the things I can do with it when creating a report or a query on that field. Is there a similar field in Access 2010 that has the same look as a lookup field and allows for multiple values to be selected?
Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3 EmployeeName John EmployeePhone 555999555 EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3 ClientName ABC Company , XYZ Company, PQR Company fkeyLocationID
tblLocations LocationID 1 , 2 Location New York , Chicago
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
I have a database which is working ok where i have a set of tasks in a table with a bunch of irrelevant fields. On creation of a task i assign users to those tasks into a multivalue field(this is all fine).
Using a query i can report to each user what tasks they are assigned to easily.
To make the database more complex i would now like to assign these users to a task and then allow them to assign there portion complete (but not the whole task complete) but i can not assign a bool variable to a .value (can i?).
My thoughts are my database is just not set up for this by using the multivalue field to hold the assigned users?
I have a table for gathering interview scores for candidates from multiple reviewers (example below)
Field1 - RankID Field2 - Presentation Score Field3 - Writing Score Field4 - Applicant Name etc.
Each applicant will be interviewed by multiple reviewers. I want a report to display the data is grouped by Applicant name, but sorted by Score.
Applicant2 - average presentationscore = 5 Applicant1 - average presentationscore = 4 Applicant3 - average presentationscore 2
it seems simple enough to group by applicant name and sort by PresentationScore, but this does not work. I have used the grouping and sorting feature in every combination I can think of and it always keeps the Applicant Names in order whether I group first on PresentationScore (which will then give me several entries for same applicant) or not. What am I missing?
Group Header: Division Group Header: Subdivision Detail
Is it possible to get the Division to only appear once per value?
e.g.,
Division Subdivision Detail Subdivision Detail
NEW Division Subdivision Detail
etc.
Right now the Division appears before the subdivision every time, even if the division is the same as before. I did change "Hide Duplicates" to "yes", but that didn't help.
This is a bit of a stupid problem but I've been trying to get a report in Access that will display data in a certain format but have been having real difficultly getting the grouping right.
This is the SQL query that I've got which gets all the results
SELECT Student.Surname, Student.Forename, Student.Admission_No, Student.Year, Course.Course_Title, Student_Course.Grade, Student_Course.Points, Student_TotalPoints.Total_Points, Student_TotalPoints.Mean_Points, SchoolTotal.School_Total FROM ((SchoolTotal INNER JOIN Student ON SchoolTotal.Year=Student.Year) INNER JOIN (Course INNER JOIN Student_Course ON Course.Course_Id=Student_Course.Course_id) ON Student.Student_id=Student_Course.Student_id) INNER JOIN Student_TotalPoints ON Student_Course.Student_id=Student_TotalPoints.Stud ent_id;
See attachment for results this produces...
I would like to get these results to be displayed like this:
Name Admission No Year Course Grade Points Dave Jones 1856 2004 Business D 60 RE C 80 ------------------------- Total Points 140 Mean Points 70 -------------------------- Becky Smith 1974 2004 Chemistry A 120 Physics B 100 --------------------------- Total Points 220 Mean Points 110 --------------------------- etc
I know I could do this for a webpage using ASP so there must be somewhere to get the report looking like this in Access....
I have a report showing products and their selling prices
Most products have only one selling price, however a few have more than one selling price
To cater for this I have created a group called "Products"
The selling prices are then in the reports' "details" section.
Works well, except that every product now spans 2 lines. One for the product description and another for the price. This doubles the number of pages in the report.
As products with only one price is the norm, is there a method that I can adopt to show products:
* with one selling price - product description and selling price on one line * with more than one selling price - product description and first selling price on one line, with subsequent selling prices on line below?
Ok Second issue: I have a list of companies that each offer 5 different programs. Eash Listing in the query (My report is built on this) has both a column for the definition and a colum with a number idenitfying the amount of people in the program.
What I need is to HIRE someone, but maybe I can do this:
I want to list each company and all 5 programs associated with that company; easy and its done.
Then I want to have a summary at the bottom of the report that goes to each company, takes the 5 programs for maybe 1, maybe 3 or maybe 92 companies, and lists them in order. Order = the top 5 programs for all the different companies. See and it gets complicated because sometimes, a program can be the largest for every company in the report, and then the second program for each company is different for each each company. So I need to arrange the programs in an order. then sort them by size, and them only show the top 5.
PLEASE, if you got through this far of my irrational and illogical brain then maybe you can help
I am trying to create a report grouped by payment date. The problem I'm having is there are 3 different payment date fields on one table and 1 payment date field on another table. I cannot figure out how to get my group expression to pull up the dates from each one of the fields. Do I need to create a new table for each payment type, or is there a way to create a common field "Payment Date" and pull up date from these 3 fields. I have a criteria form which sets beginning and ending dates, but where to assign it. The report is called "Payments by Date" and I need to show PPD (primary payment date), SPD (secondary payment date), etc.
I have a report that has a group within a group. The interior group has a footer with a textbox calculating an average. I would like the Outer group to then SUM the averages. I tried doing a =sum([TextBoxWithTheGroup Average]), but access prompts me for the value of this box when I run the report.
To summarize: is there a way to sum a textbox within the footer of a grouping
I'm in the process of setting up a form, and I have 4 yes/no fields that need to be in it. I want to group the fields so that only one of the yes/no fields will be able to be selected. The fields are: Pass, Fail, N/A, and obsolete.
I tried setting up an option group, but I can't seem to get the information to filter back to the table that is capturing the data.
What I wish to achieve is to create a report that groups and sorts without the need of a header section as it is taking up to much space cascading :
---- ---- ---- ---- ---- ----
rather than :
---- ---- ---- ---- ---- ----
---- ---- ---- ---- ----
I basically want it not to cascade but do the exact same thing as it does inside its own header. (We need to dramatically cut down the page amount as a delivery schedule is printed out daily and could do with the page count around 10 rather than 50 ).
How can I change report Sorting and Grouping through code? I tried:
Dim rpt As Report Dim strReportName As String strReportName = "ReportName"
[Code].....
But this did not work. I assume because it is on the Open command for the report. I think I might have to place some Event Procedure in the Group Header - On Format or On Print? However, I can not find the right syntax to do that.
I am trying to display five columns from three seperate tables. The query is fine but I have redundant fields showing. I thought the GROUP BY operator would fix this, but I have more than one column to select.
SELECT ID, FirstName,LastName, Salary,City FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee WHERE worksat.store=store.storenumber GROUP BY city, id, firstname, lastname, salary ORDER BY city;
Access 2010. I have a table with the following fields:
- From - To - TypeOfWork (to be chosen from a combo-box) - Activity (text field to be filled in freely)
In a table i have a complete day with times (from -> to), the type of work between those times and the activity performed between those times a bit like this:
FROM TO TYPE OF WORK ACTIVITY 00:00 - 11:00 Welding Welding clamps 11:00 - 13:00 Welding Welding anodes 13:00 - 15:00 Cleaning Cleaning pipes 15:00 - 18:00 Cleaning Cleaning floor
So it should sort on "From", then group by "TypeofWork" and repeat the "From" field(I think...). But I now have tried every combination of sorting and grouping I could think of and nothing works!
I have a report (Access 2007) with multiple totals and subtotals. However, one field, whenever I click "show Grand Total," always shows up all akimbo (out of line with the other grand totals), unbound and without the nifty little blue "grand total bar" above it.
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.
I have a form with payment information and need a list, what date the payments are due. These can be only 2 or 3 payments up to maybe 24 payments. The result I want is something like this:
Date Amount Total paid 1.7. 500.00 500.00 1.8. 500.00 1,000.00
The list itself is not a problem, but I need a list who only shows the number of payments as agreed, 2 lines (with paydates) if 2, 12 if 12 payments. I have the information of the total amount, the number of installments and the first payment date.
