Hi Folks,

I am hoping you can help me puzzle out a more elegant and less computer resource intensive solution to a problem.

I would like a summary report form to cover the following areas, yet I cannot seem to find a way of doing it without creating a very resource heavy solution.

Rather than give you specifics as it will bore you all to death i'll describe it as a monitoring report of a sales reps activities.

No of visits to clients in a particular 6 month period
No of quality checks on clients in same 6 month period
no of credit checks on clients in same 6 month period
no of shadow calls in same 6 month period

Each of these are in seperate tables with a count query already set up for them on the unique identifier and the date parameters.

all the tables have the foreign key of the clientid (equiv) within them

I was hoping to have an unbound form and an unbound report as it is a requirement to have them printed out as well and on file.

I am not sure why unbound form and report are requisites for printing. You can print bound forms and reports.

What is the computer intensive method you have for solving this already? It looks like this will be a cross tab query. Like:

          Rep Rep Rep Rep
Visits
QCheck
CCheck
Shadow

Or:

          Client Client Client Client
Visits
QCheck
CCheck
Shadow

The boring details are essential, however, in order to solve the problem. What is the table structure of the data currently, and how do you want it to look when this is processed properly?

Computer intensive shouldn't matter if this report is not run every minute. If there is tons of data, perhaps moving the pertinent information to an OLAP database would be in order.

Did any of that hlep?


mmcdonal

Look it up at: http://wrox.books24x7.com

Thank MMC,

Well ok here we go. In the 1st I've said client and in this I've said subcontractor, they are one and the same, i've just switched terminology as I've had to introduce customer into the mix and to keep it somewhat understandable.

table 1 - subcontractor firm's name and contact details
table 2 - audit visits for that firm (table1)- multiple visits to the firm during the year to check the paperwork contents of a file and the security of the offices
table 3 - credit and reference checks done on that firm (table1) ( done annually, six monthly and quarterly depending on type of check and required to keep records of each check being done for 6 years)
table 4 - sales supervisory monitoring visits - checking the quality of the sales process on customer ( could be anything from 6 to 100 as seperate one for each of the subcontractor's sales reps)
table 5 - quality checks - checking various other aspects of the subcontractor

now during this whole reporting period (6 months in all) subcontractors firms and their employees naturally come and go.

This report is run internally a random times as a quality check on multiple parties - the parties doing the checks, the subcontractor firms.

I need to be able to see a summary sheet as one total amount of each type of visit, check for every firm that was active during a set time period for governmental reporting.

i.e.

total number of visits: y
total number of credit checks: x
total number of inspections: c
total number of quality checks: f

The reason I said unbound was that there isn't really a common source to it that i can see

Sorry for the delay in getting back. It's been busy around here.

I would do this:

Create a local table with the columns you want in your report.

Then populate the first column with the subcontractor's firm name, which is linked to the other tables by either the name or a PK (autonumber) and the PK field. Use an Append query for this, and pass parameters to the append query if you just want this report for one subcontractor.

Then do this:

'Declare variables
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL1 As String
Dim sSQL2 As String
Dim iFirm As Integer
Dim dtStart As Date
Dim dtEnd As Date

'Take start and end dates
dtStart = Me.Text3
dtEnd = Me.Text5

'Remove old data first
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteFirmReport"
DoCmd.OpenQuery "qryAppendFirmReport"
DoCmd.SetWarnings True

'Target
sSQL1 = "SELECT * FROM tblFirmReport"

Set rs1 = New ADODB.Recordset
rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs1.EOF
    iFirm = rs1("FirmID")
        sSQL2 = "SELECT Count(tblFirm_CChecks.FirmID) AS FirmCount FROM tblFirm_CChecks WHERE [FirmID] = " & iFirm & _
                " AND [CCheckDate] Between #" & dtStart & "# And #" & dtEnd & "#"

        Set rs2 = New ADODB.Recordset
        rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

        rs1("CCheck") = rs2("FirmCount")
        rs1.Update

        rs2.Close

        sSQL2 = "SELECT Count(tblFirm_Inspect.FirmID) AS FirmCount FROM tblFirm_Inspect WHERE [FirmID] = " & iFirm & _
                " AND [InspectDate] Between #" & dtStart & "# And #" & dtEnd & "#"

        Set rs2 = New ADODB.Recordset
        rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

        rs1("ICheck") = rs2("FirmCount")
        rs1.Update

        rs2.Close

        sSQL2 = "SELECT Count(tblFirm_QCheck.FirmID) AS FirmCount FROM tblFirm_QCheck WHERE [FirmID] = " & iFirm & _
                " AND [QCheckDate] Between #" & dtStart & "# And #" & dtEnd & "#"

        Set rs2 = New ADODB.Recordset
        rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

        rs1("QCheck") = rs2("FirmCount")
        rs1.Update

        rs2.Close

        sSQL2 = "SELECT Count(tblFirm_Visits.FirmID) AS FirmCount FROM tblFirm_Visits WHERE [FirmID] = " & iFirm & _
                " AND [VisitDate] Between #" & dtStart & "# And #" & dtEnd & "#"

        Set rs2 = New ADODB.Recordset
        rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

        rs1("Visit") = rs2("FirmCount")
        rs1.Update

        rs2.Close

    rs1.MoveNext
Loop

rs1.Close


This works for what you want. Is this too process intensive?
I used to text boxes to take the date values and didn't check for nulls since I am the only user of this demo.

HTH


mmcdonal

Look it up at: http://wrox.books24x7.com

i'll give it a try and let you know