Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 7th, 2007, 06:16 AM
Authorized User
 
Join Date: May 2007
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default multiple queries one form

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.

 
Old November 7th, 2007, 08:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old November 7th, 2007, 09:33 AM
Authorized User
 
Join Date: May 2007
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old November 9th, 2007, 02:00 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old November 13th, 2007, 12:45 PM
Authorized User
 
Join Date: May 2007
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i'll give it a try and let you know






Similar Threads
Thread Thread Starter Forum Replies Last Post
Help: Need to combine multiple IF queries scotts SQL Server 2005 1 April 14th, 2008 07:54 PM
update/insert work in queries but not on form jonicholson Access VBA 3 January 18th, 2005 06:05 PM
Select with multiple queries problem djbst84 SQL Language 0 May 21st, 2004 07:58 PM
Multiple queries at one time VBAHole22 SQL Server 2000 2 July 27th, 2003 06:59 PM
Send Multiple Queries to ONE Excel Spreadsheet dgarcia1128 Access VBA 2 June 23rd, 2003 08:31 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.