N
Nikki
Ok, so, I'm self-taught in Access so sometimes efficient design with
more complex databases evades me. I've spent a while searching the
groups and trying to apply different situations to this one, but I
still haven't found a solution. I did previously ask a question
regarding the first two tables, but of course once I was able to apply
a solution to that, the person I'm doing this for wants something more
complex. I tried to build on what I did, but all my attempts have
given unexpected results. I'm missing something.
Anyway, I have 3 tables (from a database I did not create):
tblManager
ManagerID (primary)
Manager
Director
tblEmployees
EmployeeID (primary)
ManagerID (foreign from tblManager)
Status (this field has four options, A,B,C,D)
tblCosts
CostID
EmployeeID(foreign from tblEmployees)
Cost
I need to create a report that looks like this:
Manager Director A B C D Cost
John Sally 1 0 0 2
$100.00
Jane Bob 0 1 0 1
$50.00
Totals: 1 1 0
3 $150.00
So, I know a crosstab query would need to be created to get the values
from Status as a column header. I was able to do that. I also know
how to create a query so that it groups by manager and director and
sums the cost. What I can't do, is combine the two and create a
report that gives expected results.
Any help is appreciated.
more complex databases evades me. I've spent a while searching the
groups and trying to apply different situations to this one, but I
still haven't found a solution. I did previously ask a question
regarding the first two tables, but of course once I was able to apply
a solution to that, the person I'm doing this for wants something more
complex. I tried to build on what I did, but all my attempts have
given unexpected results. I'm missing something.
Anyway, I have 3 tables (from a database I did not create):
tblManager
ManagerID (primary)
Manager
Director
tblEmployees
EmployeeID (primary)
ManagerID (foreign from tblManager)
Status (this field has four options, A,B,C,D)
tblCosts
CostID
EmployeeID(foreign from tblEmployees)
Cost
I need to create a report that looks like this:
Manager Director A B C D Cost
John Sally 1 0 0 2
$100.00
Jane Bob 0 1 0 1
$50.00
Totals: 1 1 0
3 $150.00
So, I know a crosstab query would need to be created to get the values
from Status as a column header. I was able to do that. I also know
how to create a query so that it groups by manager and director and
sums the cost. What I can't do, is combine the two and create a
report that gives expected results.
Any help is appreciated.