Adding a Column that Sums Horizontally 1

[LL1974]

I have a report that shows performance metrics for each employee by project. There are basically 2 key fields used: ProjectID, PCID (employee id). Here is how it looks currently:
_________________________________________________
Project Data: BOK-MOK Delivery CN Consulting
Invoice Amnt: $5000.00 $10000.00

Adkins, B $1250.00
Jones, M $1250.00 $10000.00
King, H $2500.00

_________________________________________________
I have the report grouped on ProjectID, then PCID. I have the report using 8 columns per page. Typically there are 20-30 projects on a report with 10-15 employees. The customer would like as many projecs to show up on a page at once. I am using code in the On Format sections of the header and details to only show the labels once, for example:

If Me.Left < Me.Width Then
Me.LabEmployee.Visible = True
Else
Me.LabEmployee.Visible = False

Ok, enough background, time for the question. Is there a way I can add a column that shows up after the last project column that would have all the totals horizontally? Like this:
_________________________________________________
Project Data: BOK-MOK Delivery CN Consulting Totals
Invoice Amnt: $5000.00 $10000.00 15000.00

Adkins, B $1250.00 1250.00
Jones, M $1250.00 $10000.00 12500.00
King, H $2500.00 1250.00

_________________________________________________

Thank you in advance!

 

[LL1974]

Do I need to provide additional info or is this not possible? Any input from the great Mr. Hookum would be greatly appreciated!

 

[dhookom]

Mr. Hookom (who actually has a day job ;-) ) read this a while back and thinks you are using the multi-column page layout with the code How to Print Labels on the Left Margin of a Report[/code].

I would need to know where the actual numbers come from. Are you column headings projects? Is there a reason why you didn't use a crosstab query?

Duane
[url=http://www.access.hookom.net/]Hook'D on Access
MS Access MVP

 

[LL1974]

Yes the column heads are projects. I tried several variations of cross-tab queries, but couldn't get the data to display correctly. Here is description of where the data comes from:
6 Tables: (1)Projects = contains the title, customer info, other pertinent project data. (2)Profit Centers(PC) = employees, (3)Invoice_Projections = related to projects using ProjectID, it contains the month, year,and amount of invoices, and (4)tblSplits = related to Projects and Profit Centers, contains three % fields per project and pc. (5) Regions = related to Project, the region a project is in determines the respective % allocations of three categories (Sales, Delivery, and Mgt). (6) Dates = Not sure why I did this, but invoice projections table does not tie month and year together in the same control, the user selects a month, then a year. Dates table's purpose is to allow me to sort the combination of the month and date in order. Dates has 2 fields, DateID and MoYear.

Description of use: Basically, each project has a Sales, Mgt, and Delivery aspect. Every invoice amount is broken up into the 3 categories. Mgt assigns % of these 3 categories to employees based on their level of effort on the project. A combination of the employees % and actual invoices results in a "Revenue Generation" calc for each employee. Example:

1. Project A is in region A and has the following region splits: 25% Sales, 70% Delivery, 5% Mgt.
2. Project A has the following PC splits: Employee A, 0% Sales, 50% Delivery, 0% Mgt. Employee B 100% Sales, 0% Delivery, 0% Mgt. Employee C 0% Sales, 50% Delivery, 100% Mgt.
3. When an invoice for $10000 is paid, Employee A gets a revenue generation calc of $3500 (0$ sales, $3500 delivery, $0 mgt). Employee B $2500 ($2500 Sales, $0, and $0) and Employee C $4000 ($0 sales, $3500 del, $500 mgt)

There is a form where the CEO specifies a date range. After he sets the range, he clicks a button to see the "Revenue Generation Report". Embarrassingly, this is what is done to generate that report:

1.qrySplitCredits: Purpose is to pull all of the data that meets the date criteria together and generate the $ values of each split category per employee.
SELECT Splits!PCID & Projects!ProjectID & [MoYear] AS DKey, Splits.PCID, Projects.ProjectID, [LastName] & ", " & Left([firstname],1) AS FName, Projects.Project_Title, Dates.MoYear, Invoice_Projections.Amount, ([MGR1_Split]+[MGR2_Split]+[MGR3_Split]+[MGR4_Split]+[MGR5_Split])*[Amount] AS MGRCredit, [Sales_Split]*[Amount] AS SalesCredit, [Delivery_Split]*[Amount] AS DelCredit, [SSplitVal]*([Sales_Split]*[AMount]) AS PCSalesCredit, [DSplitVal]*([Delivery_Split]*[AMount]) AS PCDelCredit, [MSplitVal]*[AMount] AS PCMgtCredit, Splits.SSplitVal, Splits.DSplitVal, Splits.MSplitVal, [PCSalesCredit]+[PCDelCredit]+[PCMgtCredit] AS PCTot, Dates.DateID, Region.Region
FROM (Region INNER JOIN ((Projects INNER JOIN (Dates INNER JOIN Invoice_Projections ON Dates.DateID = Invoice_Projections.DateID) ON Projects.ProjectID = Invoice_Projections.ProjectID) INNER JOIN Splits ON Projects.ProjectID = Splits.ProjectID) ON Region.REGID = Projects.REGID) INNER JOIN [Profit Centers] ON Splits.PCID = [Profit Centers].PCID
WHERE (((Dates.DateID)>=[Forms]![frmRevGen]![FromDate] And (Dates.DateID)<=[Forms]![frmRevGen]![ToDate]))
ORDER BY Projects.Project_Title;

2.XMasDeliveryopulates a table with the Delivery credits for each employee. I wipe XMaster clean before these queries populate it.
INSERT INTO XMaster ( PCID, ProjectID, SplitType, MoYr, Val, DateID, [Val$] )
SELECT qrySplitCredits.PCID, qrySplitCredits.ProjectID, "Del" AS SplitType, qrySplitCredits.MoYear, qrySplitCredits.DSplitVal, qrySplitCredits.DateID, qrySplitCredits.PCDelCredit
FROM qrySplitCredits;

XMasSales:
INSERT INTO XMaster ( PCID, ProjectID, SplitType, MoYr, Val, DateID, [Val$] )
SELECT qrySplitCredits.PCID, qrySplitCredits.ProjectID, "Sales" AS SplitType, qrySplitCredits.MoYear, qrySplitCredits.SSplitVal, qrySplitCredits.DateID, qrySplitCredits.PCSalesCredit
FROM qrySplitCredits;

XMasMgt:
INSERT INTO XMaster ( PCID, ProjectID, SplitType, MoYr, Val, DateID, [Val$] )
SELECT qrySplitCredits.PCID, qrySplitCredits.ProjectID, "Mgt" AS SplitType, qrySplitCredits.MoYear, qrySplitCredits.MSplitVal, qrySplitCredits.DateID, qrySplitCredits.PCMgtCredit
FROM qrySplitCredits;

XMaster: Table contains PCID, ProjectID, SplitType, MoYr, Val, DateID, and Val$ (as built from three Xmas queries above)

X_PC_Proj: Purpose is to ensure every employee that has been assigned a % value to at least 1 project is given respective categories for all projects.
SELECT XPCs.PCID, XProjIDs.ProjectID, XMaster.SplitType
FROM XPCs, XProjIDs, XMaster
GROUP BY XPCs.PCID, XProjIDs.ProjectID, XMaster.SplitType;

ZAnswer: Purpose is that this query gives me the complete data set for the RevGen report. Employees will have three values (or at least spots for data) for every project on the report.
SELECT X_PC_Proj.PCID, X_PC_Proj.ProjectID, X_PC_Proj.SplitType, Avg(XMaster.Val) AS AvgOfVal, Sum(XMaster.[Val$]) AS [SumOfVal$]
FROM X_PC_Proj LEFT JOIN XMaster ON (X_PC_Proj.PCID = XMaster.PCID) AND (X_PC_Proj.ProjectID = XMaster.ProjectID) AND (X_PC_Proj.SplitType = XMaster.SplitType)
GROUP BY X_PC_Proj.PCID, X_PC_Proj.ProjectID, X_PC_Proj.SplitType;

ZZZ: Purpose is just to have the final data in a non-Totals query, with a couple key information points (title, first and last name (Fname).
SELECT Projects.Project_Title, ZAnswer.ProjectID, ZAnswer.PCID, [LastName] & ", " & Left([FirstName],1) AS FName, ZAnswer.SplitType, ZAnswer.AvgOfVal, ZAnswer.[SumOfVal$]
FROM (ZAnswer INNER JOIN [Profit Centers] ON ZAnswer.PCID = [Profit Centers].PCID) INNER JOIN Projects ON ZAnswer.ProjectID = Projects.ProjectID;

The report is based off the very logically named ZZZ query, with a Page header, a ProjectID header, and FName header. Sorted by smallest to largest on ProjectID, Ato Z on FName and SplitType.

Sigh. I apologize for sucking in advance.

 

[dhookom]

Are the amounts displayed a total of Sales, Delivery, and Mgt or are these separated out?

I hate to ask but where do "[MGR1_Split]+[MGR2_Split]+[MGR3_Split]+[MGR4_Split]+[MGR5_Split]" come from and why?

Duane
Hook'D on Access
MS Access MVP

 

[LL1974]

Are the amounts displayed a total of Sales, Delivery, and Mgt or are these separated out?
> There are basically 5 amounts displayed per project. The first amount is total invoices for the project over the specified time period. Beneath that,each employee that shows up on the report has 4 values per project; Delivery %, Sales %, Mgt %, and $ Value. The %'s come directly from the tblSplit table. The $ value is based on the employees respective splits for that project (total for the time period, not broken out by any particular time interval). The Regional splits are not displayed anywhere and are only used for the calculation to arrive at $ value. Does that answer your question?

I hate to ask but where do "[MGR1_Split]+[MGR2_Split]+[MGR3_Split]+[MGR4_Split]+[MGR5_Split]" come from and why?
> Each Region has 7 split values potentially: Sales%, Delivery%, and the 5 management splits. There can be up to 5 managers that receive management credit. The Mgr splits are tied to a PCID. When assigning a splits for a region, they simply select a PCID from a drop-down list and then assign a %. Since our CEO can possibly have MGT %'s in any of the 5 MGR split fields, I add them up. The code disallows a PCID from being used multiple times for the same project, so there will only be 1 (or none) value in these 5 fields for an employee per project, I just don't know which one. Does that make sense? (in terms of understanding, not sense from a coding perspective. grin)

Thanks for your help, much appreciated.

 

[dhookom]

Your sample report in an early post seemed to have only one project total and then summary by employee. I can't get my head around all the various totals.

Since this seems so complex, I would probably decide what I wanted the report display to look like and then create temporary tables that would be built by code.

Duane
Hook'D on Access
MS Access MVP

 

[LL1974]

Yews, I apologize, in the sample initially, I was trying to limit my question to necessity. I'm not concerned with all of the %'s, only concerned with getting a totals column to appear at the end of the report. All of my jibber jabber probably confuses the whole thing, so let me try a simpler stated question:

Let's just say I have a table with 5 fields:
ProjectID
PCID
ProjectTitle
Name
Amount

Need: A columnar report grouped by Project Title and then Name. A totals column at the end that sums the amounts per employee.

Project ABC Project XYZ Totals
Bob $1000 $5000 $6000
Joe $3000 $1000 $4000
Kay $8000 $1000 $9000

How can I get a totals column to show up at the end? If I were to use a temp table, are you suggesting I create a "dummy" project called totals?

Thanks Duane

 

[dhookom]

You can create a crosstab like:

TRANSFORM Sum(LL1974.Amount) AS SumOfAmount1
SELECT LL1974.PCID, LL1974.Name, Sum(LL1974.Amount) AS SumOfAmount
FROM LL1974
GROUP BY LL1974.PCID, LL1974.Name
PIVOT LL1974.ProjectTitle;

Use this as the record source of a report and use report totals.

There is a possible solution for displaying dynamic crosstabs in a report at Crosstab

Duane
Hook'D on Access
MS Access MVP

 

[LL1974]

Very slick, you rock! Thanks you.