Count Records in a Table From a Report

[gschimek]

I have a database that tracks recommendations for people. So one of the tables contains a person's name, what they're recommended for, and the event number that the recommendation came from. e.g.:
John Doe, Head Honcho, 75
John Doe, Head Honcho, 74
John Doe, Head Honcho, 74
John Doe, Head Honcho, 73

I've also created a report that displays the name of the person, and counts the number of recommendations that were received for each person after the most recent event. So the report's dataset query would be restricted to only show records where the event = 75. Which results in a report that looks like this:

HEAD HONCHO RECOMMENDATIONS
_____________(75)
John Doe_______1

What I'd like to do is to create an unbound text box that would look up the person's record ID in another table (that isn't the recordset for the report) and count the number of times that person was recommended after previous events.

So the result would be:
HEAD HONCHO RECOMMENDATIONS:
__________(73)__(74)__(75)
John Doe____1____2_____1

with the (75) column being the only Bound text box, and the other 2 being calculated from the other table.

So basically I just need to know if there's any code to say "Look for John Doe in table 'Recommendations' and count how many times there's a '74' in the event field."

Hopefully that makes sense. And if it does, is there any way to do what I want?

 

[pbaldy]

You might want to look at crosstab queries, which can do what you're asking dynamically. You could also use a DCount() to count the records.

 

[gschimek]

That looks like it's close to what I want. So I set up a crosstab query to use event number as the column headings, so I get a column heading for each event, and I set the "Recommended For" column to be the value and to count. But that just counts the number of records that have a value in them. But there are different values in that field. Here's an example:

John Doe, Head Honcho, 75
John Doe, Head Honcho, 74
John Doe, Head Honcho, 74
John Doe, Vice-Head Honcho, 74
John Doe, Secretary, 74
John Doe, Head Honcho, 73

With the above data in my crosstab query, the value for John Doe under the "74" column is 4, because he was recommended 4 different times. What I really want to do though is have my report break down by Recommendation type (which it's currently doing for the most recent event's data) and display the number of recommendations of each type. So the report would be:

HEAD HONCHO RECOMMENDATIONS:
__________(73)__(74)__(75)
John Doe____1____2_____1

VICE-HEAD HONCHO RECOMMENDATIONS:
__________(73)__(74)__(75)
John Doe____0____1_____0

SECRETARY RECOMMENDATIONS:
__________(73)__(74)__(75)
John Doe____0____1_____0



Am I asking too much of a single report, or is there some magical way of accomplishing this? Thanks in advance.

 

[pbaldy]

Can you add that field and both as a group by and row heading? Can you post a sample db?

 

[gschimek]

I tried adding the RecommendedFor column to the crosstab query twice, once as a groupby and once as a column heading, but it gave me multiple records for each person.

I stipped down the database to leave only the tables, queries, and reports in question, and attached it to this post. I put unbound text boxes in the report to represent the way I'd like it to look.

Thanks for the help!!

 

[pbaldy]

The mock report does not seem to match the crosstab type layout you had earlier (events along the top). This is ultra quick and dirty, but is this in line with what you want?

 

[gschimek]

Yeah, that could work. How'd you do it?

Thanks!

 

[pbaldy]

Pretty much what I said. It's a report based on this:
TRANSFORM Count(tblRecommendations.recommendationID) AS CountOfrecommendationID
SELECT tblRecommendations.recommendedFor, tblMasterContact.LastName, Count(tblRecommendations.recommendationID) AS [Total Of recommendationID]
FROM tblRecommendations INNER JOIN tblMasterContact ON tblRecommendations.masterContactID = tblMasterContact.MasterContactID
GROUP BY tblRecommendations.recommendedFor, tblMasterContact.LastName
PIVOT tblRecommendations.tecNumber;

 

[gschimek]

Any chance you could reattach my sample DB that you created this report in? I think it would help me make sense of what you did.

 

[pbaldy]

Here you go

 

[gschimek]

Thanks a lot pbaldy! My report is doing exactly what I wanted. You were a huge help, and I learned a few things along the way.

Much appreciated!

 

[pbaldy]

Excellent! Glad we got it working for you.