Perplexed! (1 Viewer)

[CazB]

I'm looking for some help here (obviously!)

I have a database which tracks Sales Leads...

in tblLeads (which is a download from another system, so I have no control over how it 'appears' in my database) I have

LeadID (PK)
ConsultantID (FK to tblConsultants)
DateLeadPulled (Date )
Rating (number between 0 and 5)
RolesPulled (number)
Meetings (number)
Interviews (number)
Offers (number)
Placements (number)

... and a few other fields which are of no relevance to the question I have here

So: my question:

I've been asked to build a report which summarises, by Consultant, by Month =format([DateLeadPulled],"yyyymm"), the number of Leads raised (count of LeadID), Score for the month (Sum of Rating), and Average for the month (Sum of rating / count of LeadID)... and then the same stats for the Year.

Easy enough... but I don't know how to get it in the report layout they want - which is like the sample shown below.... (report format.png) based on the data that arrives in a format as shown in Tabledata.png

If it was a 'static' report I'd have a good go at doing it but the thing is, each month there wil be another month on the report, and I'm not sure how to achieve what they want!

 

[pr2-eugin]

I think you would be better off with Pivot Tables. The information can be laid out as you expect to see.

 

[CazB]

can I PIVOT in Access??

 

[pr2-eugin]

Of course you can..

Designing your first PivotTable/PivotChart views in Access
Create PivotTable or PivotChart views in a desktop database

 

[CazB]

well, blow me down, how come I never spotted that, lol..... ty

 

[pr2-eugin]

Ha ha.. Your welcome. Good Luck.