Report by Month (1 Viewer)

[Johnmad86]

Hi,

I've seen other posts that seem to be the same as my issue, but I'm afraid I've not understood the answers. Hopefully someone is able to point me in the right direction.

I need to create a report from a query. I have created the query that pulls the below data from the relevant tables:

ClientName;invoiceAmount;InvoiceDate

Using the wizard I have created a basic report that shows the information in the query, but I need it to list all clients and all months - even if some months show no invoices sent.

Ideally it would show as follows (with an option for the relevant year to display):

______ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec
Client1
Client2
Client3
Client4

Do I need to create a separate tables for Month/Year and link these to the query? Or is there a simple function I can put into the query to achieve this? It seems a simple task, but after playing for a week I've got no where :banghead:

 

[pr2-eugin]

Check out the Cross tab Query.

 

[Johnmad86]

Excellent thanks. I had read about them, but didn't realise they were different to the usual query.

I've got it working now and the query displays all months.
I'm having trouble getting it to differentiate between different years, but with some tinkering I think I should be able to get that working.

then I can have a dropdown on the report page to choose the relevant year and have that year's data display.

thanks for pointing me in the right direction

 

[pr2-eugin]

No probs ! Good luck !

 

[Johnmad86]

OK, I've managed to create a simple crosstab query, and can list the results by Months with no problem.
I've manually entered the Column headings as the Months with the PIVOT IN function ("Jan","Feb" etc) and these display correctly, even if there is no data for a certain month:

TRANSFORM Sum(qry_InvoicesSent.Fee) AS SumOfFee
SELECT qry_InvoicesSent.ClientName
FROM qry_InvoicesSent
WHERE ((Not (qry_InvoicesSent.Invoiced) Is Null))
GROUP BY qry_InvoicesSent.ClientName, qry_InvoicesSent.Invoiced
PIVOT Format([qry_InvoicesSent].[Invoiced],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

However I'd like the database to have the option for selecting a date range/year and then display the relevant data with dynamically updated column headings ("2014-Jan", "2014-Feb" etc). Is this possible?

I've read potential examples, but the coding to get this to work seems very complex. Is there a simple way of getting this to work?

Thanks in advance

 

[JHB]

...
However I'd like the database to have the option for selecting a date range/year and then display the relevant data with dynamically updated column headings ("2014-Jan", "2014-Feb" etc). Is this possible?

Always 12 month or??
If yes then post some sample data in Excel or MS-Access, (zip it).

 

[Johnmad86]

always 12 months would be fine, but a specified range would also be acceptable.

Which ever would be easiest I'm thinking would be best!
I've got a date picker form where the dates can be entered, but the code to pass the dates to the query doesn't seem to be working well:

Private Sub cmdPreview_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rpt_MonthlyInvoicesByClient"
strDateField = "[Invoiced]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Thanks

 

[Johnmad86]

Apologies for the delay,

I've attached a sample DB with the relevant crosstab query and report.

Currently it displays by month, but as you can see, the figures for each month do not discern by year, so April2013 and April 2014 are totalled and the sum of these values is displayed.

When I played with passing a date range to the form it would not accept the date format as the column headings are hard coded with the month names, rather than a date itself.

Any help on this would be greatly appreciated.

 

[JHB]

Try if it as you expected, open the form, put in a date and click the button.

 

[Johnmad86]

thank you for the help. I have managed to get a working report now.

I've attached the DB report here in case any others have a similar issue and need pointing in the right direction.