Question Getting Count Values in Report

[bhalo_manush]

I need some help with an issue in a database i am working on currently.

Basically the database that I have is a database for keeping account numbers of our clients. Among other things what is stored in the table is: name of account holder, the date account opening application is received and the account number. This is in "tbl_accounts" table. In another table ("tbl_schemes") we store the scheme account numbers. The two tables are related via the ID field of tbl_accounts. This means, one customer (who will have one account number) can open one or more schemes. Every scheme will be assigned a scheme number similar to the account number. There are 4 types of schemes. The tbl_schemes table has a structure as follows:

ID
AccountID (the autonumber key of tbl_accounts)
Date application received
Scheme Account Number
Scheme Type
...

What I need is to generate a report where I will be able see the number of accounts opened betwen a certain date range (not fixed. parametered). However, the report is going to be monthly ie show the number of accounts opened each month. Also in the same report, it is going to show the number of schmes opened according to the type for that corresponding month.

Please suggest how to do this the best way. Note that I am not too familiar with Pivot Tables and crosstab query. However I am ready to implement the best way to achieve my requirement.

Thanks

Chris.

 

[DavidAtWork]

When working with reports where you're trying to group by months, I've always found if useful to have an extra field in the table, call it reportingMonth, where you use the actual date (ie Date application received) and use that date to populate the field reportingMonth by setting Me.reportingMonth = CDate("01/" & Month(Me.[Date application received]) & "/" & Year(Me.[Date application received]))
This way you will have many schemes with the same reportingMonth date which makes grouping easy in your reports. This is also useful as well because a date such as 01/01/2012 is easy to display as Jan-12 (dd-mmm) format on graphs etc
David

 

[bhalo_manush]

Sorry David, I did not fully understand. Are you suggesting me to add another column to my tbl_schemes table?

Thanks
.

 

[DavidAtWork]

Chris, yes that's exactly what I'm suggesting. Another date field which can be populated as I suggest above when a new scheme record is added. You could also run an update query to update this new field in old scheme records using:
update reportingMonth to CDate("01/" & Month([Date application received]) & "/" & Year([Date application received]))
David

 

[bhalo_manush]

Thanks David.

I tried your way but its not giving me what I want. I did some further research and came across the sql query mentioned below which is near to what I am after. I have adjusted it to my objects' names of course.

SELECT Year([ScanReceived]) AS Expr1, Month([ScanReceived]) AS Expr2, Count(Tbl_Schemes.ID) AS CountOfID
FROM Tbl_Schemes
WHERE (([ScanReceived] Between [Enter First Date] And [Enter Last Date]))
GROUP BY Year([ScanReceived]), Month([ScanReceived]);

Can you please guide me on two things?

1) You can tell from the code that when the query returns the result, I get the number for the corresponding month the account was opened. How do I get it to display the month name when I display the above query result in a report?

2) What do I need to add to the query above so that I can the count grouped by the 'type of schemes' (which as "Type 1". "Type 2" and so on) in addition to the month they were opened ?

Looking forward to your response. Cheers.

Chris.

 

[DavidAtWork]

Chris, shame you couldn't get it to work as I suggested because it would solve problem 1) for you, however I would suggest in:
1) you use this sql to append to a reporting table which has a field for month name and afterwards you run another query that updates the month name according to the month number your sql above returns
2) amend this sql to:
SELECT Year([ScanReceived]) AS Expr1, Month([ScanReceived]) AS Expr2, Count(Tbl_Schemes.ID) AS CountOfID, [type of schemes]
FROM Tbl_Schemes
WHERE (([ScanReceived] Between [Enter First Date] And [Enter Last Date]))
GROUP BY [type of schemes], Year([ScanReceived]), Month([ScanReceived]);

David