Report combining data from 3 tables with a crosstab?

  • Follow


Ok, so, I'm self-taught in Access so sometimes efficient design withmore complex databases evades me.  I've spent a while searching thegroups and trying to apply different situations to this one, but Istill haven't found a solution.  I did previously ask a questionregarding the first two tables, but of course once I was able to applya solution to that, the person I'm doing this for wants something morecomplex.  I tried to build on what I did, but all my attempts havegiven unexpected results.  I'm missing something.Anyway, I have 3 tables (from a database I did not create):tblManagerManagerID (primary)ManagerDirectortblEmployeesEmployeeID (primary)ManagerID (foreign from tblManager)Status (this field has four options, A,B,C,D)tblCostsCostIDEmployeeID(foreign from tblEmployees)CostI need to create a report that looks like this:Manager         Director          A       B       C      D     CostJohn                Sally              1       0       0       2$100.00Jane                Bob               0       1       0       1$50.00                          Totals:        1       1       03       $150.00So, I know a crosstab query would need to be created to get the valuesfrom Status as a column header.   I was able to do that.  I also knowhow to create a query so that it groups by manager and director andsums the cost.  What I can't do, is combine the two and create areport that gives expected results.Any help is appreciated.
0
Reply Nikki 3/29/2007 3:52:26 PM

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

On Mar 29, 3:41 pm, "Al Campagna" <alcampagna@msnewsgroups> wrote:
> 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."
>
> "Nikki" <nicolemis...@gmail.com> wrote in message
>
> news:1175192464.839504.277300@p77g2000hsh.googlegroups.com...
>
>
>
> > 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.
>
> > On Mar 29, 12:59 pm, "Al Campagna" <alcampagna@msnewsgroups> wrote:
> >> 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 MVPhttp://home.comcast.net/~cccsolutions
>
> >> "Find a job that you love, and you'll never work a day in your life."
>
> >> "Nikki" <nicolemis...@gmail.com> wrote in message
>
> >>news:1175183546.893071.94020@y80g2000hsf.googlegroups.com...
> >> <snip>
>
> >> > 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.- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
Reply Nikki 3/30/2007 7:39:51 PM


1 Replies
610 Views

(page loaded in 0.109 seconds)


Reply: