Report combining data from 3 tables with a crosstab?

[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.

 

[Al Campagna]

Nikki,
You wrote...

Status (this field has four options, A,B,C,D)

Are those four separate fields with a value like 0 or 1 or 2 in each one?

Or, or one field that can contain an "A" or "B" ... etc

In other words, where do the values 0,1,2 come from?

Manager Director A B C D Cost
John Sally 1 0 0 2 100.00

Please describe in detail...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Nikki]

Sorry for not explaining well.

The A, B, C, D are values in a drop down for the Status field. The
numbers would be counts of how many of each status that manager and
director have.

Let me know if I'm still missing some details.

 

[Al Campagna]

Nikki,
Try this simpler method... since there are only 4 possible values for Status. Just a
normal Totals query will do it (no Crosstab)
Break ou the 4 values manually in the query behind the report, by creating 4 calculated
"bound" controls
CountA : IIf(Status = "A", 1, 0)
CountB : IIf(Status = "B", 1, 0), etc.... for all A,B,C,D Sum on those fields, by
Director, right in th query.
Then, on the report...

Manager Group --------
[Manager]
Detail------------
[Director] [CountA] ....etc................ [Cost]
Manager Footer------------
=Sum(CountB) =Sum(Cost)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Nikki]

Yes!! That's it! Thanks so much!

Nikki,
Try this simpler method... since there are only 4 possible values for Status. Just a
normal Totals query will do it (no Crosstab)
Break ou the 4 values manually in the query behind the report, by creating 4 calculated
"bound" controls
CountA : IIf(Status = "A", 1, 0)
CountB : IIf(Status = "B", 1, 0), etc.... for all A,B,C,D Sum on those fields, by
Director, right in th query.
Then, on the report...

Manager Group --------
[Manager]
Detail------------
[Director] [CountA] ....etc................ [Cost]
Manager Footer------------
=Sum(CountB) =Sum(Cost)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Sorry for not explaining well.

The A, B, C, D are values in a drop down for the Status field. The
numbers would be counts of how many of each status that manager and
director have.

Let me know if I'm still missing some details.

- Show quoted text -