Re: Select Columns from Crosstab for Report - HELP PLEASE!!!

From: Duane Hookom (duanehookom_at_nospamhotmail.com)
Date: 02/24/04 

Date: Tue, 24 Feb 2004 08:52:44 -0600

If you only want to display one month, then why are you using a crosstab.
Just set the criteria in the query to the month chosen by the user. 

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups
"Donna" <anonymous@discussions.microsoft.com> wrote in message
news:A43AF1EF-971B-48DA-9A7B-74027141AAE8@microsoft.com...
> Help! I am a total novice at Access - surprised I've gotten this far by
myself, but I've officially hit the wall on this one....
> I have a crosstab query that shows Revenue by Month and totals for
clients.  I need to build a report showing Client Name, Project Description,
Total Revenue and Revenue for current month.  (With YTD Revenue, Month's
Revenue and Month Forecast in Header)
> I can get the Client, Project Descr and Total Revenue, but am at a total
loss on how to pull only the current month's revenue.
> In the Header I've gotten the YTD Rev and Forecast for current month -
(use a form for Month, pulls from another query)
>
> Question: would I need to write a SQL SELECT - But HOW!!!!! or is there a
better way
>
> Cross tab Query:
> SELECT Totals.ClientName, tblProjects.ProjectDesc, Totals.Booked AS
TotalRevenue
> FROM ((Totals INNER JOIN tblClients ON Totals.ClientName =
tblClients.ClientName) INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ClientID) INNER JOIN (tblPhase INNER JOIN tblBilling ON
tblPhase.PhaseID = tblBilling.PhaseID) ON tblProjects.ProjectID =
tblPhase.ProjectID
> WHERE (((Totals.Booked) Is Not Null))
> GROUP BY Totals.ClientName, tblProjects.ProjectDesc, Totals.Booked
> ORDER BY Totals.Booked DESC
> PIVOT Format([MoBilled],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
>
> Report has:
> ClientName; ProjectDesc; TotalRevenue; and then unbound field - how do I
link this to the crosstab column based on the month selected from
[Forms]![frmMoSelect![Month]

Relevant Pages

  • Re: TWO Crosstab Query Ouput Questions
    ... we want to see and use those as the input to the crosstab. ... and only 1098 in the final query. ... locations for each species as rows with the ... concerning the Totals Query. ...
    (microsoft.public.access.queries)
  • RE: Report Question for Duane
    ... I created a crosstab query ... this produced the Query with the following fields ... I have not had a chance yet to display them in the report... ... This sounds like a totals query based on your crosstab where the change Date ...
    (microsoft.public.access.reports)
  • Re: Crosstab Query problem
    ... The total at the end of the row is the sum of the day of all sheds. ... on these numbers and totals. ... I have already made 1 report off this crosstab and it went tikity boo, ... They show in the query if you open in design view and the query works. ...
    (microsoft.public.access.queries)
  • Re: Should I use a crosstab for this?
    ... > I am trying to get the totals of a table by client by type of income. ... > I have tried to do a crosstab but the wizard confuses me more than ... Use a crosstab query. ...
    (comp.databases.ms-access)
  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
Loading