Account monthly running total

[wwwredback]

Hello all,

Its been a while.

I have my little retail database and am now trying to add a customer account running total so I can check if they are over there agreed total or not.

does anyone have any pointers on how I would go about this and the ability to reset the amount as and when the account has been paid. To be honest I don't know where to start.

Thanks for all your help.

Cheers

James.

 

[PNGBill]

In your CustomerTable, Have a Currency Field where you can enter and edit the Credit Limit.

You will then one or more queries to have a Net customer balance at any time. Accumulated Sales Less accumulated Payments. You should have this already so you can see the customers Current Balance.

In your Invoice Form/s you setup an event that will look for the Credit Limit and the Current Balance and if CL - CB <1 then display a message.
This could be in the text Box Control where the CustomerID is entered and be an on Update Event.

You could also have another set to the Invoice Total Field that Compares the Credit Limit to Current Balance plus this amount and here you could prevent the invoice from being saved or some other feature depending on how your system is setup.

 

[wwwredback]

Excellent,

Thanks for the great advice PNGBill.

Will try that out now.

I will let you know and if I can get it working I'll place up a sample database.

Thanks

James.

 

[wwwredback]

Hi PNGBill,

sorry to be a pain but I can't seem to get my query to run.

I have at the moment got a query that runs a list of all the customers with all the invoices and line entries that they have purchased, then at the end I have created a new column to add up the line total, but how do I now select just each of the customers and total up each of their line totals?

Thanks

James.

 

[PNGBill]

Be sure you have the Full invoice total including Taxes etc. Maybe this will require one query to get your Full Invoice Values and you should also have the customerID in this query.
Move the CustID column to the left and sort on this field with InvoiceID next and Amount last.
Click on the totals symbol on the menu bar. Select Group by for CustID, Count for InvoiceID and Sum for InvoiceAmount.
This should give you a Sales Sum for each CustomerID

 

[PNGBill]

A more secure way is to make a query of all Customers and write down your query record count - say eg 3,650
Make a query to return all your Sales Invoices as in my earlier post with the SalesAmountSum.

Join these two queries in a 3rd query but use these field names:
CustomerID (from 3,650 query), SumSalesValue: Nz([SalesAmountSum], 0) (from your earlier query)
Use your own field names but follow the system. Nz([fieldname], 0) substitutes a Zero value where no record exists.
This 3rd query should return 3,650 records and some may be Zero.

This will asist in your later calculations as you have a Value for Every Customer, even if they are now making their first purchase.

For Credit Limit, ensure you have an entry on every Customer Record of their Credit Limit - maybe use a default, $500, to ensure all new Customers have a value.

Once you have values in the two above then you can do the calculation to decide if the customer is able to purchase.

 

[wwwredback]

Hi PNGBill,

Thanks for the info, I will get onto it straight away and let you know the outcome.

Thanks again.

James.

 

[wwwredback]

Hi PNGBill,

I have done what you have suggested and I can't get the invoice amounts to total them sum of the customers. e.g. 100 customers but only 80 invoices.

very bizarre!

Code used:

Qry_Running_Total_3rd

Field: CountOfCustomerID
Table: qry_Customer_Count

Field: SumSalesValue: Nz([LineTotal],0)



qry_USE_for_running_total

Field: CustomerID
Table: Orders
Total: Group By
Sort: Ascending

Field: OrderID
Table: Order Details
Total: Count

Field: LineTotal: Sum(([Order Details].[UnitPrice]*[Order Details].[Quantity]*1.175))
Total: Expression



qry_Customer_count

Field: CustomerID
Table: Customers:
Total: Count

What can I be doing wrong, apart from not being very good at programming.

Thanks

James.

 

[PNGBill]

How many customers do you have? Don't supply Sensitive information but a "number" should be recorded until you are confident that part of the query works.
Just open the Customer table in datasheet view and the number of records will be on the bottom left hand corner.

Make a simple query that returns the Customer ID for all customers - should have same number of records.

Do the same for the InvoiceHeader table - you should get a count of the invoices ever raised.

Do you already have a query to calculate the Total for a Sale/Order? If so, then use that or combine that with the new query above so you still have the same number of records (InvoiceHeader) but they now have their respective TotalSale value (currency field). - If not then you should do this.
You should also have CustomerID in the same table so add this to the query and Group on this field.
Your query fields will be:
OrderID(Count), CustomerID(group), OrderTotal(Sum)

This will give you a SumOrderTotal for each CustomerID and a count of sales - not important.

Now you make a new query and join the original Customer Query (100 customers) and this last query (SumOrderTotal).
Your new query will use CustomerID from the CustomerID query, not the OrderID query and also the SumOrderTotal from the OrderID query but make a new field name.
eg. CustomerID, SumSales: Nz([SumOrderTotal],0)
Group on CustomerID and Sum on the new field "SumSales"

You should now have a result of the same number of Customers (100) and a value for each record. either zero or the total sale value.

You can do a quick query and use this last query. Just use SumSales and Sum this field and you should have a value that equals your total sales from day one. - delete this query, just a test.

 

[PNGBill]

This query looks a bit odd.
qry_USE_for_running_total

Are you trying to calculate the total of a Sale ie all the items and also sum the sales for each CustomerID ?

Too much for one query. You could use a subquery but better to use two queries. First will get the Sum (OrderTotal) for each OrderID and then the 2nd will use this to Sum the value for the CustomerID.

 

[PNGBill]

See attached Database with queries. access 2000