I'm working on a database I didn't create that is linked together like an M C Escher painting.
I've been asked to amend an existing report by adding in an extra totals box, but am having problems with duplicates.
I'll try and explain.
Say I have 3 members of staff: Steve, Bob and Gary, and each man has a number of hours available to them each week that they can use to visit clients (Steve has 20, Bob has 30 and Gary has 40). Each man visits clients and records their visits in a table that is then added to the database. The information recorded includes the StaffID (i.e. Steve, Bob or Gary), the name of the client visited, whether that client placed an order (a simple Y or N) and the time taken for the visit. However, the database contains a field that displays the total hours available to each man for each and every entry.
What this means is that there is a Total Hours field that contains the same information depending on the ID of the staff member, i.e. if Gary's information is being added, the Total Hours column will always contain 40, if it's Steve's information then Total Hours will always contain 20 etc)
If a report is run from this information as it is, would it be possible to create a calculation, in the Design section of the report, to add together the total hours available, without including duplicates? I.e. I simply want 20 + 30 + 40, but if I try to simply Sum that field I get 20 + 20 + 30 + 20 + 40 + 40 etc.
I hope that makes sense. I'm trying to make it as simple as possible for myself by simply adding a calculation to the existing report, rather than having to create extra queries as, as I mentioned above, this isn't my database.
I've been asked to amend an existing report by adding in an extra totals box, but am having problems with duplicates.
I'll try and explain.
Say I have 3 members of staff: Steve, Bob and Gary, and each man has a number of hours available to them each week that they can use to visit clients (Steve has 20, Bob has 30 and Gary has 40). Each man visits clients and records their visits in a table that is then added to the database. The information recorded includes the StaffID (i.e. Steve, Bob or Gary), the name of the client visited, whether that client placed an order (a simple Y or N) and the time taken for the visit. However, the database contains a field that displays the total hours available to each man for each and every entry.
What this means is that there is a Total Hours field that contains the same information depending on the ID of the staff member, i.e. if Gary's information is being added, the Total Hours column will always contain 40, if it's Steve's information then Total Hours will always contain 20 etc)
If a report is run from this information as it is, would it be possible to create a calculation, in the Design section of the report, to add together the total hours available, without including duplicates? I.e. I simply want 20 + 30 + 40, but if I try to simply Sum that field I get 20 + 20 + 30 + 20 + 40 + 40 etc.
I hope that makes sense. I'm trying to make it as simple as possible for myself by simply adding a calculation to the existing report, rather than having to create extra queries as, as I mentioned above, this isn't my database.
Last edited:
