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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 wa

Cross tab Query
SELECT Totals.ClientName, tblProjects.ProjectDesc, Totals.Booked AS TotalRevenu
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.ProjectI
WHERE (((Totals.Booked) Is Not Null)
GROUP BY Totals.ClientName, tblProjects.ProjectDesc, Totals.Booke
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]
 
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 said:
Help! I am a total novice at Access - surprised I've gotten this far by
Click to expand...
myself, but I've officially hit the wall on this one....
I have a crosstab query that shows Revenue by Month and totals for
Click to expand...
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
Click to expand...
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 -
Click to expand...
(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 =
Click to expand...
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
Click to expand...
link this to the crosstab column based on the month selected from
[Forms]![frmMoSelect![Month]
 
Back
Top