Sorting a Report

[Access_Person]

I need to sort a report according to a value that is obtained in one of the text boxes in it. I have read on this forum that this is impossible and that a report can only be sorted based on the fields available in the table/query it is based on. Is this true?

I also read that I must build another query with this text box already in it and then somehow base my report on that new query too so that the value can be used to sort it.

I started building the query (still having no idea how I would add it to the report) but ran into a problem.

In my query I insert the following into one of the fields :

TotalFromRecruiter: DCount("[Source Name]","Applicants","[Source Name]=[Company Name]")

I have 17 companies in the query (these come from one table) and must count how many times each of them appear in a different table ("Applicants") however Access won't let me use a criteria from this query ("Company Name") to count the entries in the other table.

This exact same method worked just fine in 1) a form and 2) the report. But a query which I must create in order to let me sort the report won't let me do it.

Thanks in advance,
Frustrated Access user

BTW, the error message is the following one: "The expression you entered as a query parameter produced this error: 'MS A can't find the name 'Company Name' you entered in the expression' "

 

[Pat Hartman]

I have read on this forum that this is impossible and that a report can only be sorted based on the fields available in the table/query it is based on. Is this true?

- If you had a pile of forms and the forms contained CustomerID but not CustomerName, how would you sort the forms by CustomerName? You would somehow need to get CustomerName on the form. Luckily this is much easier in Access than with paper.

Create a totals query that counts applicants by company. Join this new query to the old query. Now, every row in the final query includes the applicant count so you can sort by it.

query1:
Select [Source Name], Count(*) as ApplicantCount
From Applicants;

query2:
Select OtherQuery.*, query1.ApplicantCount
From OtherQuery Inner Join query1 ON OtherQuery.[Source Name] = query1.[Source Name];

 

[Access_Person]

Thanks a lot for your reply, but I don't quite understand how to create a query that counts applicants by company.

The applicants table has the "source name" field. There is therefore a company next to each person's name. I need to count the number of applicants but then I also need to find the success percentage from each company by diving the "hired" number of applicants by the "total" number. Hired is one of the options that appear in the "Interview Status" field of the "Applicants" table.

Do you know why my DCount expression did not work?

 

[Access_Person]

Ok, despite the lack of responses I haven't sat idly by.

I have created a crosstab query which actually adds up the total per "Agency" and adds up the totals for "hired"/"declined" for each Agency.

Here's a visual rendition in case you are unclear:

Source Name     |     Total     |     Declined     |     Hired     |     etc.

Agency1         |               |                  |               |    
Agency 2        |               |                  |               |
Agency 3        |               |                  |               |

Now all I need to do is actually be able to perform a simple division calculation on the columns to get the % from each agency.

The issue is that in the Query format there isn't an invividual field for each of the columns. All of the columns except for "Total" come from this field: "Interview Results": this one's crosstab property is set to "column headig".

How do I do calculations on the individual columns?

Someone?

Thanks in advance.

 

[Pat Hartman]

Use the crosstab as the RecordSource for a report. You can calculate anything you need in the report. If you are unsure of how to do % calculations in a report, use the wizard to build the report and look carefully at the options at each stage. There will be a button on one of the forms that will let you specify totals.