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
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
'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
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.