0 Replies Latest reply on Jan 11, 2013 9:22 PM by Michael Lance

# Display and calculate with the total of a set in a single row

Anyone want a fun project for the weekend?

It has 2 tables and a desired output table.

Table 1 has the following fields of interest:

last, first

check date

division

department

amount

Table 2 has the following fields of interest:

last, first

division

department

FTE

I need the following table and I think I need to do a right join between the two, where table 2 is the right table (of course):

last, first (from table 2)

check date (from table 1, since I need all dates displayed in rows and each individual will have multiple check dates)

Note: the next 3 fields seem problematic, in theory, because I want to display records from table 2 (assuming a right join) next to the check date column, which is not a field from table 2.

division (table 2)

department (table 2)

FTE (table 2)

Note: another challenge: for every row (division and department): I need to multiply the FTE (table 2) by sum of the amount (table 1) per last, first (table 1) and check date (also table 1).

The final table should have columns with headers that look like this:

Table A

 LAST, FIRST (from table 2) CHECK DATE (from table 1) DIVISION (table 2) DEPARTMENT (table 2) FTE (table 2) NEW AMOUNT (table 1 x table 2 calculation from above)

Extra Credit:

I then need another table with the following:

Note: the first 4 fields are from table 1:

Table B

 LAST, FIRST CHECK DATE DIVISION DEPARTMENT AMOUNT ADJUSTED AMOUNT

Note: "ADJUSTED AMOUNT" tries to find a matching value from the resulting Table A to this one (Table B) based on exact matches between the first 4 fields in each (LAST, FIRST through DEPARTMENT). If it does, it subtracts what it finds in Table B from "AMOUNT." If it does not find a match, it subtracts "AMOUNT" from 0 to get a negative value (unless there is a better way to show the negative value).