We help IT Professionals succeed at work.
Get Started

Counting Report - Questions about most efficient method

Painfree888 asked
Last Modified: 2006-11-18

Hello Experts,

Have a question about the best way to generate a report. Background:

I have a table populated with customer orders. It's a fairly large table, field wise, and contains about 33,000 records, with about a 100 being added every day. The table contains fields such as Customer_Payment_Type, Customer_Shipping_Type, Customer_Shipping_Priority, etc. I have been requested to create a report that will display counts of the various values in the fields. For instance, my customer wants to know how many orders from Virginia we've had, how many orders from a certain city and how many orders on a certain date. So, the report will do nothing more than go through and count field entries.

One way to do this is to create a recordset based on the following query: reportRS = reportDB.openRecordset("SELECT * FROM Customer_Orders"). I say select * here because we'll basically be counting up everything in the table. Then, I thought it would be fairly easy to just loop through the recordset and do my counts, placing the final counts of each field into the report fields. Something like this:

     Dim customerPaymentType_CreditCard As Long, customerPaymentType_Cash, customerShippingType_FedEx As Long,
     customerShippingType_UPS As Long, etc.
     Dim reportDB As DAO.Database, reportRS As DAO.Recordset

     Set reportDB = currentDB
     Set reportRS = reportDB.OpenRecordset("SELECT * FROM Customer_Orders")

     Do Until reportRS.EOF

         'Count the various types of payments used
          If reportRS!Customer_Payment_Type = "Credit Card" Then
                 customerPaymentType_CreditCard = customerPaymentType_CreditCard  + 1
          ElseIf reportRS!Customer_Payment_Type = "Cash" Then
                 customerPaymentType_Cash = customerPaymentType_Cash  + 1
         End If

         If reportRS!Customer_Shipping_Type = "Federal Express" Then
                 customerShippingType_FedEx = customerShippingType_FedEx + 1
          ElseIf reportRS!Customer_Shipping_Type = "UPS" Then
                 customerShippingType_UPS = customerShippingType_UPS + 1
         End If

    'Counting is finished, now report on counts (write value of count variables to report fields)
     Me.customerPaymentType_CreditCard = customerPaymentType_CreditCard
     Me.customerPaymentType_Cash  = customerPaymentType_Cash
End Sub

The example above is just a representative sample of what I'd like to do. I will need to count up a bunch of different field values. So, obviously, the above example won't work for me (well, actually, it will if I wanted to spend the time typing everything in, but I don't. Plus, I would imagine that using this method would be prone to really slowing down the more records that are added).

I was wondering if anyone had a better idea for how to do this. Perhaps writing various Count queries, or maybe something else. There must be a simpler (if not more simple, then more efficient) way to do this. Any advice would be greatly appreciated.

Watch Question
Top Expert 2004
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE