qry count record **then

G

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 15:36
Joined
Nov 8, 2005
Messages
3,302
OK guys i have a export qry that creates a xls ..
tick-er-tee boo (ish)

I have had to change the way i was going to do this - but the same problem still exists

now i have a make table qry then i export out as below ...

however i would like to have some totals
now the number of records will change each time its run - from2 or 3 to 400-500 (or more)
So how do i get it to count the number of records then add on a few numbers (lets say 15) then use that as the line to enter in total formulas
below is the code ( the totalling would start at line9)
so something along x=(sum9,9: x,9)(?)- just my thought **
the export will be done (or variations of it )15-20 times every month

Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Oxygen"
Set rstGetRecordSet = dbs.OpenRecordset("Oxygenqrymaster")
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(1, 1) = "Cover holder"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(1, 5) = "Type:"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(1, 6) = "Liability:"

objActiveWkb.Worksheets("Oxygenqrymaster").Cells(2, 1) = "Borderaux Month"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(2, 2) = Forms!accountsadminfrm!Months

objActiveWkb.Worksheets("Oxygenqrymaster").Cells(3, 1) = "UMR Number"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(3, 2) = Forms!accountsadminfrm!UMR
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(4, 5) = "Period"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(4, 6) = Forms!accountsadminfrm!Datefrom
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(4, 7) = "To"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(4, 8) = Forms!accountsadminfrm!Dateto


objActiveWkb.Worksheets("Oxygenqrymaster").Cells(1, 3) = "Cover Holder Name"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(5, 1) = "Cover Holder Ref"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(5, 2) = "Liability"

objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 1) = "Cert No."
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 2) = "Insured."
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 3) = "Trade"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 4) = "From"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 5) = "To"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 6) = "Effective date for ADJs"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 7) = "Postcode"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 8) = "Trans Type"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 9) = "EL Premium (Excl Tax)"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 10) = "PL Premium (Excl Tax)"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 11) = "EL Limit"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 12) = "PL Limit"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 13) = "Survey Fees"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 14) = "Referral"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 15) = "Date Certificate Issused"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 16) = "Premium Total (Excl Tax)"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 17) = "Total IPT"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 18) = "Premium Tax inc Tax"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 19) = "Commission"
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(7, 20) = "Balance"

'objActiveWkb.Worksheets("Aggregation").Cells(1, 1).Font.Name = "Aharroni"
'objActiveWkb.Worksheets("Aggregation").Cells(1, 1).Font.Size = 12
'objActiveWkb.Worksheets("Aggregation").columns("B").entirecolumn.autofit
'objActiveWkb.Worksheets("Aggregation").columns("H:J").entirecolumn.autofit
'objActiveWkb.Worksheets("Aggregation").columns("O:S").entirecolumn.autofit
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(9, 1).CopyFromRecordset rstGetRecordSet

conti........
 
Couldn't you use a dcount of the number of records in the query and then add the necessary number of rows to go below the report data? I've done very similar things in Access myself.
 
- I checked the samples and there is something there..

but the count would have to happen first (I presume) then I can use this count number and add to it to get to the line number i want ...
(it was late last night when I sent the post - so I won't be able to check this until later on )
but thanks for your response

regards
 
GaryPanic said:
- I checked the samples and there is something there..

but the count would have to happen first (I presume) then I can use this count number and add to it to get to the line number i want ...
(it was late last night when I sent the post - so I won't be able to check this until later on )
but thanks for your response

regards
Click to expand...

Here is something similar I've done in the past using DCount to get the Count so I could tell Access where in the Excel sheet to start placing the data. I even used Access to do some cell formatting and to insert formulas for total rows based on the number of rows used for each department so it looks like it's manually and professionally done. Not a bit of this is done by hand. The code is written in such a way that an unlimitted number of departments and supervisors for each department can be included. The names have been changed for privacy and confidentiality of course.

Code: 
Dim CellRef As Long
Dim NoOfLoops As Long
Dim SheetNumber As Integer
Dim recordnumber As Long
Dim RepNumber As Long

openexcel ("\\jxflpr4\data\department\Ops Tech\Raw Builder Archive\BE\Performance Points Typing RP Template.xlt") 
xl.UserControl = False 

xl.Visible = False

Set rsdlcount = currentdb.OpenRecordset("qry_performance_points_summary")

NoOfRecords = DCount("*", "qry_performance_points_summary")

NoOfLoops = NoOfRecords

xl.Sheets("Overall").Select
xl.Sheets("Overall").PageSetup.CenterHeader = xl.Sheets("Overall").PageSetup.CenterHeader & "&""Bold""Overall Through " & Me!txt_enddate

With rsdlcount
    CellRef = 4
    .MoveFirst
    Do Until NoOfLoops = 0
        ContestTeam = rsdlcount![department]
        teamcounter = teamcounter + 1
        recordnumber = 0
        RepNumber = 0
        origcellref = CellRef
        Do Until rsdlcount![department] <> ContestTeam
            RepNumber = RepNumber + 1
            xl.Range("A" & CellRef & "").Value = RepNumber
            xl.Range("B" & CellRef & "").Value = rsdlcount![Sup]
            xl.Range("C" & CellRef & "").Value = rsdlcount![department]
            xl.Range("D" & CellRef & "").Value = rsdlcount![sumofrpvalue]
            xl.Range("E" & CellRef & "").Value = rsdlcount![sumofrollpoints]
            xl.Range("F" & CellRef & "").Value = rsdlcount![rollpointfte]
            xl.Range("G" & CellRef & "").Value = rsdlcount![SumOfCompleted]
            xl.Range("H" & CellRef & "").Value = rsdlcount![SumOfApproved]
            xl.Range("I" & CellRef & "").Value = rsdlcount![SumOfDenied]
            xl.Range("J" & CellRef & "").Value = rsdlcount![sumoftotaldeals]
            xl.Range("K" & CellRef & "").Value = rsdlcount![ApprovedPerc]
            xl.Range("L" & CellRef & "").Value = rsdlcount![DealPoints]
            xl.Range("M" & CellRef & "").Value = rsdlcount![BonusDealPoints]
            xl.Range("N" & CellRef & "").Value = rsdlcount![TotalDealPoints]
            xl.Range("O" & CellRef & "").Value = rsdlcount![TotalTeamPoints]
            xl.Range("P" & CellRef & "").Value = rsdlcount![TotalPointsFTE]
            CellRef = CellRef + 1
            NoOfLoops = NoOfLoops - 1
            If NoOfLoops = 0 Then
                GoTo Endofthisone2
            End If
            .MoveNext
        Loop
Endofthisone2:
        xl.Range("A" & CellRef & "").Value = "*"
        xl.Range("C" & CellRef & "").Value = "Unit Totals"
        xl.Range("D" & CellRef & "").Value = "=Sum(D" & origcellref & ":D" & (CellRef - 1) & ")"
        xl.Range("E" & CellRef & "").Value = "=Sum(E" & origcellref & ":E" & (CellRef - 1) & ")"
        xl.Range("F" & CellRef & "").Value = "=E" & CellRef & "/D" & CellRef
        xl.Range("G" & CellRef & "").Value = "=Sum(G" & origcellref & ":G" & (CellRef - 1) & ")"
        xl.Range("H" & CellRef & "").Value = "=Sum(H" & origcellref & ":H" & (CellRef - 1) & ")"
        xl.Range("I" & CellRef & "").Value = "=Sum(I" & origcellref & ":I" & (CellRef - 1) & ")"
        xl.Range("J" & CellRef & "").Value = "=Sum(J" & origcellref & ":J" & (CellRef - 1) & ")"
        xl.Range("K" & CellRef & "").Value = "=IF(J" & CellRef & " = 0," & Chr(34) & " " & Chr(34) & ",(G" & CellRef & "+H" & CellRef & ")/J" & CellRef & ")"
        xl.Range("L" & CellRef & "").Value = "=Sum(L" & origcellref & ":L" & (CellRef - 1) & ")"
        xl.Range("M" & CellRef & "").Value = "=Sum(M" & origcellref & ":M" & (CellRef - 1) & ")"
        xl.Range("N" & CellRef & "").Value = "=L" & CellRef & "+M" & CellRef
        xl.Range("O" & CellRef & "").Value = "=E" & CellRef & "+N" & CellRef
        xl.Range("P" & CellRef & "").Value = "=O" & CellRef & "/D" & CellRef
        CellRef = CellRef + 2
    Loop
End With

xl.Rows("1:3").Select
xl.Selection.Copy
xl.Rows(CellRef & ":" & CellRef).Select
xl.Selection.Insert Shift:=xlDown
CellRef = CellRef + 1

xl.Range("A" & CellRef & "").Value = ""
xl.Range("B" & CellRef & "").Value = "Manager"
xl.Range("C" & CellRef & "").Value = ""
CellRef = CellRef + 1
xl.Range("A" & CellRef & "").Value = ""
xl.Range("C" & CellRef & "").Value = ""

CellRef = CellRef + 1

Set rsdlcount = currentdb.OpenRecordset("qry_performance_points_manager")

NoOfRecords = DCount("*", "qry_performance_points_manager")

NoOfLoops = NoOfRecords
With rsdlcount
.MoveFirst
RepNumber = 0
origcellref = CellRef
Do Until NoOfLoops = 0
RepNumber = RepNumber + 1
xl.Range("A" & CellRef & "").Value = "*"
xl.Range("B" & CellRef & "").Value = rsdlcount![Man]

xl.Range("D" & CellRef & "").Value = rsdlcount![sumofrpvalue]
xl.Range("E" & CellRef & "").Value = rsdlcount![sumofrollpoints]
xl.Range("F" & CellRef & "").Value = rsdlcount![rollpointfte]
xl.Range("G" & CellRef & "").Value = rsdlcount![SumOfCompleted]
xl.Range("H" & CellRef & "").Value = rsdlcount![SumOfApproved]
xl.Range("I" & CellRef & "").Value = rsdlcount![SumOfDenied]
xl.Range("J" & CellRef & "").Value = rsdlcount![sumoftotaldeals]
xl.Range("K" & CellRef & "").Value = rsdlcount![ApprovedPerc]
xl.Range("L" & CellRef & "").Value = rsdlcount![DealPoints]
xl.Range("M" & CellRef & "").Value = rsdlcount![BonusDealPoints]
xl.Range("N" & CellRef & "").Value = rsdlcount![TotalDealPoints]
xl.Range("O" & CellRef & "").Value = rsdlcount![TotalTeamPoints]
xl.Range("P" & CellRef & "").Value = rsdlcount![TotalPointsFTE]
CellRef = CellRef + 1
NoOfLoops = NoOfLoops - 1
.MoveNext
Loop
End With

xl.Range("A" & CellRef & "").Value = "*"
xl.Range("C" & CellRef & "").Value = "Site Totals"
xl.Range("D" & CellRef & "").Value = "=Sum(D" & origcellref & ":D" & (CellRef - 1) & ")"
xl.Range("E" & CellRef & "").Value = "=Sum(E" & origcellref & ":E" & (CellRef - 1) & ")"
xl.Range("F" & CellRef & "").Value = "=E" & CellRef & "/D" & CellRef
xl.Range("G" & CellRef & "").Value = "=Sum(G" & origcellref & ":G" & (CellRef - 1) & ")"
xl.Range("H" & CellRef & "").Value = "=Sum(H" & origcellref & ":H" & (CellRef - 1) & ")"
xl.Range("I" & CellRef & "").Value = "=Sum(I" & origcellref & ":I" & (CellRef - 1) & ")"
xl.Range("J" & CellRef & "").Value = "=Sum(J" & origcellref & ":J" & (CellRef - 1) & ")"
xl.Range("K" & CellRef & "").Value = "=(G" & CellRef & "+H" & CellRef & ")/J" & CellRef
xl.Range("L" & CellRef & "").Value = "=Sum(L" & origcellref & ":L" & (CellRef - 1) & ")"
xl.Range("M" & CellRef & "").Value = "=Sum(M" & origcellref & ":M" & (CellRef - 1) & ")"
xl.Range("N" & CellRef & "").Value = "=L" & CellRef & "+M" & CellRef
xl.Range("O" & CellRef & "").Value = "=E" & CellRef & "+N" & CellRef
xl.Range("P" & CellRef & "").Value = "=O" & CellRef & "/D" & CellRef

xl.UserControl = True
xl.Visible = True
 
thats excatly what i wa trying to do - I need to read and reread this to digest - but I had visions of dumping into xls and then adding the totals (which would not of been the end of the world - but If I can smart it up all the better)
 
The totals line is added by the code, sorry I forgot to post the attachment of how the spreadsheet looks when done. There is too much distraction in my moderating duties ATM. :o
 

Attachments

Fab - there are bits inth exls that I needed and were going to ignore (merege cells stuff) so i cna work on it -

big thanks (still don't understand all the code - but I'll get there)

regards

a greatful Gary
 
GaryPanic said:
Fab - there are bits inth exls that I needed and were going to ignore (merege cells stuff) so i cna work on it -

big thanks (still don't understand all the code - but I'll get there)

regards

a greatful Gary
Click to expand...

Sorry, I'm really terrible when it comes to leaving comments in my code. I've just been the only one here that understands vba for so long, it becomes redundant. :o

Let me know if there's any part of the code you are not too sure about. I'm confident you'll be able to make it work for you.
 
You must log in or register to reply here.

Similar threads

O
VBA Counting Records Based on Select Case
Replies
5
Views
1,220
Oreynolds
O
mike60smart
Calculated Total Days Vacation taken
Replies
7
Views
1,972
theDBguy
theDBguy
P
VBA correction to export multi-query data to excel
Replies
9
Views
1,586
Punice
P
MajP
Access Fill Down to Emulate Excel Fill Down
Replies
0
Views
3,574
MajP
MajP
S
Access VBA - Export to Excel without saving with field name
Replies
2
Views
5,930
boblarson
boblarson

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom