Sum / IIf - Basic question

[mane_uk]

Hi all,

I know this is a basic question but I haven't done reports/queries on MS Access for so long that I can't remember how it works!!

I have a database built to record the activities of my sales team. One of the fields captured is the type of the activity for the day... it can be: "on the road", "in the office", "holidays". It also capture the date of the activity, who with, etc... all in different tables using pk/fk to link each other.

Now I need to create a report which will show for each sales person, the number of the days in each type of activity per week. I am trying to create the query which I will base to build my report on but I just can't figure out what to do!!!!

I could easily do it via VBA by creating a recordset to the sales team, then for each sales person of the recordset I would create a recordset of their activities, then run through the activities recordset and hold the amalgamation in variables comparing when the activity were "on the road" or "in the office" or in "holidays". But then, how can I export the variables/recordset/result to my reports?

Anyway, I am happy to have the query approach to do it but can someone shed some light? I am lost in this part of MS Access.

Thanks
mane_uk

 

[Arvin]

I just went through the same pain

But with the help of boblarson I was able to figure it out ... the link below might help ....

http://www.access-programmers.co.uk/forums/showthread.php?t=207729

 

[mane_uk]

thanks Arvin... I had seen your thread before posting mine but at the time I couldn't figure it out what was happening ... Anyway, used your code as base to create my query and it is working!!

Now another question, I want to create it based on dates entered by the user through a form... how can I pass the information entered by the user in the text box to the query to create the report?

Also, out of curiosity, what the "NZ" in your code does?

Thanks again!!

 

[Brianwarnock]

Forms!formname!controlname
remember to use [] if the names contain spaces or special characters except _

Brian

 

[CBrighton]

NZ replaces null values.

e.g. NZ(strPhoneNumber,"Unknown") would display the contents of strPhoneNumber unless it was a null value, in which case it will substitute in the string value "Unknown".

 

[mane_uk]

Fellas, thanks for the help so far... I have tested it and it is working perfectly!! But from one question to another...

I am using the following SQL code to create my query:

SELECT tblSalesTeam.firstName, 
           tblSalesTeam.surname, 
           Sum(IIf([tblTypeOfActivity.activityDescription]="Visit Business",0.5,0)) AS VisitBusiness, 
           Sum(IIf([tblTypeOfActivity.activityDescription]="Training" Or [tblTypeOfActivity.activityDescription]="In The Office",0.5,0)) AS InTheOffice, 
           Sum(IIf([tblTypeOfActivity.activityDescription]="Holiday (annual leave/bank holiday/off sick)",0.5,0)) AS Holiday, 

FROM (tblActivity INNER JOIN tblTypeOfActivity ON tblActivity.activityTypeID = tblTypeOfActivity.activityTypeID) INNER JOIN tblSalesTeam ON tblActivity.salesTeamID = tblSalesTeam.salesTeamID

WHERE (((tblActivity.active)=Yes) AND ((tblActivity.complete)=Yes))

GROUP BY tblSalesTeam.firstName, tblSalesTeam.surname;

The sales team can visit more than 1 business in the same day so I can add more than 1 activity under the same day. So the query above show me the total number of visits during a certain period!! Now I need to add to my query above the number of days that the users were in the road instead of the number of visits they did.

So for example:
John Smith did 3 sales visits on the 01/04, stayed in the office on the 02/04 and another 2 visits on the 03/04.

The query above would show John Smith VisitBusiness 2.5 InTheOffice 0.5 Holiday 0

Instead I need it to show John Smith Days on the Road: 2 InTheOffice 0.5 Holiday 0

I was thinking about using Count (Select Distinct ... ) but I can't do it inside the query above. Comes up with an error message.

Any ideas/suggestions please?

Thanks
ps.: If you are asking why do I add only 0.5 per day, it is because I divide the day's activity in AM/PM. So sales team can enter that hey have been in the office in the morning and visiting business in the afternoon... I am not taking it in consideration at the moment but will add later.

 

[mane_uk]

Hi all,

Haven't heard about my question above so just wondering if someone could help?

Thanks

 

[CBrighton]

Sorry, I don't understand the criteria you are suggesting.

From what I can see you count 1+ visits as a whole day out of office but 0 visits only half a day in the office? What was the person doing for the other half when they were neither in the office nor out of it?

 

[mane_uk]

Hi CBrighton,

Sorry my mistake it should be:
So for example:

John Smith entered
* 2 sales visits on the 01/04 AM
* 1 sale visit on the 01/04 PM
* In the office on the 02/04 AM
* In the office on the 02/04 PM
* 1 sale visit on the 03/04 AM
* 1 sale visit on the 03/04 PM.

The query above would show
John Smith
VisitBusiness 2.5
InTheOffice 1
Holiday 0

Instead I need it to show
John Smith
Days on the Road: 2
InTheOffice 1
Holiday 0

I found the following code to find the days on the road without checking AM/PM:

SELECT Count(*) AS Count_Date
FROM [SELECT DISTINCT tblActivity.date 
     FROM tblActivity 
     WHERE tblActivity.[active]=Yes AND tblActivity.[complete]=Yes]. AS Distinct_Date;

But how can I add the AM/PM check and amalgamate it into my main query's code mentioned prevously?

Thanks

 

[CBrighton]

What criteria would you use to see if it was AM/PM? Does it have it's own field or do you want to calculate it from a date/time field (like tblActivity.date from your SQL)?