Generating an XLS grid type table report 1

[1DMF]

Hi,

I've been tasked to generate a report which is basically a grid XLS type table with totals.

The columns are the months of year and the rows are various categories.

These have no bearing on any table data or records in the entire DB , but are all calculation fields.

Is the only way to create the data for the report is to create a temp table and write the code to populate the table with the calculated results?

I don't see how any standard query could get to the data required.

Can Access reporting engine take a column value (a date) and transform it somehow into a column heading representing each month of the year?

I guess I'm talking grouping somehow but not down the report page, but across?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

You might want to check out faq703-5466.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Cool thanks Duane, always helps when you know the terminology.

Got to get my head round 'CrossTab' reporting tomorrow!

I can see i've got a headache tomorrow getting my head around the query first



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

I can see how the CTQ can be used, but it won't work for my purpose.

You can only have one 'value' in the query and I need different values for each row, eg..

Jan | Feb | Mar
Completed Visits 5 | 7 | 9
Total File Checks 21 | 13 | 12
Visits for next month 12 | 9 | 15

The only way I can see will be to build a temp table with the criteria and then use cross tab query to report against the table.

It's a nighmare when you have staff that are in love with their damn spreadsheets and then expect you to emulate it via a report when half the data doesn't even exist in the DB in a reasonable format.

Hey ho, keeps me busy I guess!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

You were directed to the FAQs to find a monthly crosstab report. Please scour the FAQs for queries to find a solution for a multiple value crosstab query. If you can't find it, come back and ask so I can provide the link.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Not sure that's what i'm after Duane, I don't want multiple values for each month (column)

I'm finding it hard to explain what I want or to translate the FAQ to my scenario.

each row in the table is a different calculatin of records in the same table.

Januany for example would be...

Compelted visits, are records that have a [CVisit Date] in Jan and [VType]->[Category] of 'V'

File Checks have a [CVisit Date] in Jan and a [VType]->[Category] of 'F'

Visits for next month have [CVisit Due] in Feb and [VType]->[Category] of 'V'

All records are in the one table, but the selection differs dependent on date field and category types.

How would you do a count of each particular grouping and display it in a crosstab as required?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

Since you seem to be having trouble explaining your requirements, please take the time to type about 10-12 records (with significant fields) into a reply and then show us how you want them to display in your report. Provide actual table and field names as well as the logic.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

ok, here goes.... This is the entire report request which does span more than 1 table.

Table :- [Compliance_Audit]
Columns :- [CDate Visited],[ContactID],[CVisit Due],[VType]

Table :- [Contacts]
Columns : [MembershipLevel],[ContactID],[Membership Date],[Leaving Date]

Lookup Table :- [Comp_VType]
Columns :- [VType],[ Cat]

-----------------------------------------------------------

The columns for the report are to be Jan-Dec and then a total on the end.

-----------------------------------------------------------



Each row required is as follows...

this is a scan of what i've been given as the required layout

Visits Completed are any record in [Compliance_Audit] with a [CDate Visited] and [VType]->Cat of 'V'

Total file checks are any record in [Compliance_Audit] with [CDate Visited] and [VType]->Cat of 'F'

..remote are any of the above file checks with a [VType] of 'File Remote'

..on site are any of the above file checks with a [VType] of 'File OnSite'

AVG file checks per visit is 'OnSite' checks divided by 'Visits Completed'

Visits booked for next month are any records in [Compliance_Audit] with a [CVisit Due] date of the following month and a blank [CDate Visited]

Number of advisers are records from [Contacts] where they had a [Membership Date] in the required month and [Leaving Date] blank or not in that month and a [MembershipLevel] of either 'Adviser' or 'Trainee'

..having checks are number of those advisers/trainees who had a file check against them from the [Compliance_Audit] 'file checks' (VTYPE->Cat = 'F')

The last AVG per adviser is the above two divided by each other.

-----------------------------------------------------

I'm not sure what example record data you want.

But the main DB with the members is [Contacts] and they could have been and gone as a member and had no checks but they need to show as being a member under the correct month, whether they had a visit or check or not.

--------------------------------------------------

Possible data for the fields are as follows... (brackets shows the lookup category - Comp_VType)

[VType] :- File Remote(F), File Onsite(F), Guidance(V), Annual(V), Training(V)

[MembershipLevel] :- Appointed Rep, Adviser, Trainee, Controller, Introducer

----------------------------------------------------

Here are some records from a basic linked query

CDate Visited VType CVisit Due Cat MembershipLevel Membership Date Leaving Date
14/02/2005 Guidance 14/02/2005 V Appointed Rep 03/11/2004 24/10/2006

CDate Visited VType CVisit Due Cat MembershipLevel Membership Date Leaving Date
01/02/2005 File OnSite 01/02/2005 F Adviser 02/11/2004 30/06/2005

CDate Visited VType CVisit Due Cat MembershipLevel Membership Date Leaving Date
01/08/2005 File Remote 01/08/2005 F Trainee 13/04/2005 01/06/2006

CDate Visited VType CVisit Due Cat MembershipLevel Membership Date Leaving Date
01/09/2005 File Remote 01/09/2005 F Adviser 02/11/2004

CDate Visited VType CVisit Due Cat MembershipLevel Membership Date Leaving Date
16/03/2006 Annual 16/03/2006 V Appointed Rep 30/10/2004 01/03/2010

-----------------------------------------------------

Let me know if you need anything else.

Oh and the overall selection is the year to be displayed.

So for [Compliance Audit] that's [CDate Visited] and [Contacts] that'll be [Membership Date]/[Leaving date]



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

I gave up with trying to generate a single query for this, I guess you were having dificulty with it also Duane!

I couldn't get my head round it at all... came up with this solution instead

    Dim sDateS As Date
    Dim sDateE As Date
        
    'set date ranges
    If IsNull([Forms]![rptQueryTool].StartDate) Then
        sDateS = Format(Now(), "YYYY") & "/01/01/"
        sDateE = Format(Now(), "YYYY") & "/12/31"
    Else
        sDateS = Format([Forms]![rptQueryTool].StartDate, "YYYY/MM/DD")
        If IsNull([Forms]![rptQueryTool].EndDate) Then
            sDateE = Format(sDateS, "YYYY") & "/12/31"
        Else
            sDateE = Format([Forms]![rptQueryTool].EndDate, "YYYY/MM/DD")
        End If
    End If
            
    ' Clear Table
    CurrentDb.Execute ("DELETE FROM rptMonths WHERE 1=1")
    
    'Create Visits Completed
    CurrentDb.Execute ("INSERT INTO rptMonths ([Ord],[Cat],[Dates]) SELECT 1 AS [Ord],'Visits Completed' AS [Cat],[CDate Visited] AS [Dates] FROM [Compliance_Audit] WHERE [CDate Visited] BETWEEN #" & sDateS & "# AND #" & sDateE & "#")
       
    'Create Total File Checks
    CurrentDb.Execute ("INSERT INTO rptMonths ([Ord],[Cat],[Dates]) SELECT 2 AS [Ord],'Total File Checks' AS [Cat],[CDate Visited] AS [Dates] FROM [Compliance_Audit] WHERE [CDate Visited] BETWEEN #" & sDateS & "# AND #" & sDateE & "# AND [VType] IN (SELECT [VType] FROM [Comp_VTypes] WHERE [Cat] = 'F')")
    
    'Create Remote File Checks
    CurrentDb.Execute ("INSERT INTO rptMonths ([Ord],[Cat],[Dates]) SELECT 3 AS [Ord],'...of which were remote' AS [Cat],[CDate Visited] AS [Dates] FROM [Compliance_Audit] WHERE [CDate Visited] BETWEEN #" & sDateS & "# AND #" & sDateE & "# AND [VType] <> 'File OnSite'")
    
    'Create OnSite File Checks
    CurrentDb.Execute ("INSERT INTO rptMonths ([Ord],[Cat],[Dates]) SELECT 4 AS [Ord],'...of which were on site' AS [Cat],[CDate Visited] AS [Dates] FROM [Compliance_Audit] WHERE [CDate Visited] BETWEEN #" & sDateS & "# AND #" & sDateE & "# AND [VType] = 'File OnSite'")
       
    'Visits For Next Month
    CurrentDb.Execute ("INSERT INTO rptMonths ([Ord],[Cat],[Dates]) SELECT 6 AS [Ord],'Visits booked for next month' AS [Cat],DATEADD(""m"",-1,[CVisit Due]) AS [Dates] FROM [Compliance_Audit] WHERE [CVisit Due] BETWEEN #" & Format(DateAdd("m", 1, sDateS), "YYYY/MM/DD") & "# AND #" & Format(DateAdd("m", 1, sDateE), "YYYY/MM/DD") & "#")
    
    'Advisers having checks
    CurrentDb.Execute ("INSERT INTO rptMonths ([Ord],[Cat],[Dates]) SELECT 8 AS [Ord],'Advisers having checks' AS [Cat],[CDate Visited] AS [Dates] FROM [Compliance_Audit] LEFT JOIN [Contacts] ON [Compliance_Audit].ContactID = [Contacts].[ContactID] WHERE [CDate Visited] BETWEEN #" & sDateS & "# AND #" & sDateE & "# AND ([MembershipLevel] = 'Adviser' OR [MembershipLevel] = 'Trainee') AND [VType] IN (SELECT [VType] FROM [Comp_VTypes] WHERE [Cat] = 'F') AND [Client Name] NOT LIKE '%recheck%'")
           
    'Number of advisers
    Dim sDateLoop As Date
    Dim sMemDate As Date
    
    sDateLoop = sDateS
    
    Do While Month(sDateLoop) <= Month(Now()) And Year(sDateLoop) = Year(Now())
        sMemDate = Year(sDateLoop) & "/" & Month(sDateLoop) & "/01"
        sMemDate = DateAdd("m", 1, sMemDate)
        sMemDate = DateAdd("d", -1, sMemDate)
        CurrentDb.Execute ("INSERT INTO rptMonths ([Ord],[Cat],[Dates]) SELECT 7 AS [Ord],'Number of Advisers' AS [Cat],'" & sDateLoop & "' AS [Dates] FROM [Contacts] WHERE [Membership Date] <= #" & sMemDate & "# AND ([Leaving Date] IS NULL OR [Leaving Date] > #" & sMemDate & "#) AND ([MembershipLevel] = 'Adviser' OR [MembershipLevel] = 'Trainee')")
        sDateLoop = Format(DateAdd("m", 1, sDateLoop), "YYYY/MM/DD")
    Loop

Now I have a table I can run the crosstab query against.

The above code runs in a matter of seconds and gives me exactly what I need to then report against with the crosstab query.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

oh and yes, I changed [Dates] to [Month] used MONTH([CDate Visited]) with a COUNT(1) and a GROUP BY MONTH([CDate Visited]) and now I just have a few hundred records not a few thousand with grouped counts.

Thanks you so much for the inspiration and guidance Duane, as always much appreciated.


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts