Using totals to generate more data - Need help !

[anartey]

Hello Access Gurus,

I need help to do the following :

Assuming my table has the following

Customer Name ( Items purchased)
Customer A Bag
Customer B Pen
Customer A Pen
Customer A Bag
Customer C Bag

1) How do i determine the # of items customer A,B or C bought ? The names of the items do not matter.
2) How do i take the number of items per customer and use that in another table ?

Thanks in advance.

[maximus]

I have a simple set up here a table Table2 with three Fields:
1) ID (PK)
2) CustomerName (Text)
3) ProductName (Text)


Below is a function that I have used to find out the total number of products actually bought by a customer.

I have used a DAO Recordset here opened it and looped through it. MySQL is the used to set the criteria of records to be opened. Once the recordset is opened using a loop and a counter we find out the total number of products the customer has bought.

Function syntax: ShopTotal("CustomerName")

Dim strCustomerName As String
Dim MyRecordSet As DAO.Recordset
Dim MySQL As String
Dim intCounter As Integer

Function ShopTotal(strCustomerName) As Integer
If Len(strCustomerName) > 0 Then
    MySQL = "Select * From Table2 Where CustomerName='" & strCustomerName & "'"
    Set MyRecordSet = CurrentDb.OpenRecordset(MySQL)
        Do While Not MyRecordSet.EOF
        intCounter = intCounter + 1
        MyRecordSet.MoveNext
        Loop
        MyRecordSet.Close
        Set MyRecordSet = Nothing
Else
    Exit Function
End If
ShopTotal = intCounter
intCounter = 0
End Function

[anartey]

Hello Maximus,

Your instructions went over my head. Can you show me how to add code ? Perhaps a sample access template might help . Thanks in advance.

[maximus]

Here is a small sample for you.

Open the database make sure you enable macros if your computer security disables them.

A startup form will open. The record source of the form is Table2. The Form has three controls Customer Name; Product Name; Total Products Bought;

Total Products Bought will show the number of products the Customer Name.
Open the form in design view and you will see the Control Source of this control is =ShopTotal([Text1]).

This is reference to the Function ShopTotal. The function is in Module1

Open the Table2 You will see That Customer A has bought 4 items, Customer B has 1, Customer C has 4, Customer F has 1


You will notice when you navigate through the form the Total Products Bought will reflect the numbers mentioned above.

[rpeare]

Let me add something before you get crazy with this. If your intent is to use the summary for another thing there's no reason to create a table just to handle the summary. You can create an aggregate query that will total the data you want then you can link that query to anything else you want and it will behave as a table.

If your table has the same names as you've indicated (I'm going to call it tblTest for this example)

Your query would be something like:

Select CustomerName, Count([ItemsPurchased]) as TotalBought FROM tblTEST
GROUP BY CustomerName

NOTE I took out spaces in field names, it's a bad practice to use anything but _ in your field names as it leads to more trouble when you start to do more complex coding.

Once you have this query you can use it just like any other table in your database.

[anartey]

Hello rpeare,

I think your idea would best suit my needs. Can you provide me with a sample access database with querry and linked field example. Thanks in advance.

[rpeare]

Just create a query

Put in your customer name (or customer ID it doesn't really matter as long as you separate them by customer). Add your ProductName or ProductID field (again it doesn't matter which). Click on the SIGMA button on your query design toolbar (It looks like a capital E) a new line will appear in your query called TOTAL. In that total line all the fields you add will, by default, say GROUP BY. Change the ProductName or ProducID field to COUNT

That'll give you the sum of items a client bought assuming they did not buy multiple items per line. If they bought say 20 pens and you have the quantity in another field you would want to get rid of your ProductName or ProductID field and put in the quantity field and instead of COUNT have SUM on the totals line.

Once you have that complete you can link it to any other query or report or anything else and it will behave as a table (except that you can not update it).

[anartey]

So are you saying that there's a way to link the results of a querry to a table without codes ? If so, how ?

[rpeare]

In your summary or aggregate query you have to retain some sort of identifier that you can link to your target table.

If you just create a query with a bunch of sums in it and nothing else you can't link it to anything

Let's say you have a table

[code]tblCustomerPurchases
PurchID CustID PurchItem PurchCost PurchDate

tblCustomers
CustID CustomerName

now let's say you want to make a short list of customers and the total they spent at your company.

You would first create your summary query which would be something like:

SELECT CustID, Sum([PurchCost]) as TotalSpent GROUP BY CustID

this should um all the dollars they have spent at your company

You would then link this to your tblCustomers table through the CustID to come up with a list of all your customers and how much they've spent at your company