Customer Repeat - Calculating a running balance

[solsen via AccessMonster.com]

Hi,

I am trying to create a query that shows the number of orders a customer has.
The data set I am using will not have any customer information in the periods
with no sales, so they would be skipped from a sequential stand point. A
Sample of the data table is below

Output
Period Cust Sales Orders
200809 CustA 100 1
200810 CustA 0 1
200811 CustA 200 2
200812 CustA 0 2

Data Table
Period Cust Sales
200809 CustA 100
200811 CustA 200

 

[MGFoster]

Hi,

I am trying to create a query that shows the number of orders a customer has.
The data set I am using will not have any customer information in the periods
with no sales, so they would be skipped from a sequential stand point. A
Sample of the data table is below

Output
Period Cust Sales Orders
200809 CustA 100 1
200810 CustA 0 1
200811 CustA 200 2
200812 CustA 0 2

Data Table
Period Cust Sales
200809 CustA 100
200811 CustA 200

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Got this info of one of the newsgroups, concerning this same problem:

====================== begin =============================

If you have a table with sequential numbers in it,
Table: Iotas
Field: Iota (numbers from 1 to the max needed) - unique

SELECT YourTable.[Id Name]
, DateAdd("d",Iotas.Iota -1,StartDate) as theDate
FROM Iotas, YourTable
WHERE Iotas.Iota <= YourTable.Days

The number table is handy to have and you only need to create as many
records in it as the highest number you want to use. In this example
you would only need 9 records - if that were your maximum value in the
Days field.

You can actually use just one table with the numbers from 0 to 9 and
then use the table multiple times (aliased) to get any number you want

Iotas1000.Iota * 1000 + Iotas100.Iota * 100 + Iotas10.Iota*10 +
Iotas.Iota would generate every number from 0 to 9999

========================= END ===================================

You'll need to format the date like this:

Format(DateAdd("d",Iotas.Iota -1,StartDate),"YYYYMM") as Period

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBSd6hHoechKqOuFEgEQLaWgCfQ/KfNsTUmmq3Qm3A5hYrlJTL8Y4AmMhU
Wxu47AAELEgNokh5SYxH4qM=
=IWj6
-----END PGP SIGNATURE-----

 

[Ken Sheridan]

If you do not already have a Periods table with a column Period as its
primary key then you should create one and insert rows for every period from
whenever your sales data starts to some arbitrary period in the future, e.g.
209912. An easy way to do this is to serially fill down a column in Excel
and import it into Access as a table. Then create an enforced relationship
between this table and your Data Table.

Also, if you don't already have one, create a table Customers with column
Cust as its primary key. Insert this with one row per customer and create an
enforced relationship between this table and your Data Table.

You now have a set of normalized tables, with data integrity protected by
the enforced relationships.

You can then return the results you want with a query like this:

SELECT Period, Cust,
NZ((SELECT SUM(Sales)
FROM [Data Table]
WHERE [Data Table].Cust = Customers.Cust
AND [Data Table].Period = Periods.Period),0) AS Sales,
(SELECT COUNT(*)
FROM [Data Table]
WHERE [Data Table].Cust = Customers.Cust
AND [Data Table].Period <= Periods.Period) AS Orders
FROM Periods, Customers
ORDER BY Cust,Period;

The way it works is that the outer query returns the Cartesian product of
the Periods and Customers tables, joining each row in one to each row in the
other, i.e. every possible customer/period combination.

The first subquery returns the sum of the sales from the Data Table for the
period/customer currently returned by the outer query, with the Nz function
converting Nulls (no sales for that customer/period) to zeros. I'm not sure
you’re your post whether the combination of period/cust is unique or not in
the Data Table (your sample data suggests it is), i.e. whether there can be
more than one order per customer per period, but summing the sales allows for
this either way.

The second subquery counts all rows from the Data table for the outer
query's current customer where the period is earlier or equal to the outer
query's current period, thus returning the number of orders up to and
including the current period.

Ken Sheridan
Stafford, England