Do...loop statement

  • Thread starter Thread starter Dorene
  • Start date Start date
D

Dorene

I've created a query with the following fields: Client,
Month, TotalSales, and BaselineTotal. I need to populate
BaselineTotal based on the information outlined below:

There are many clients. Each client can have data for one
or more months. For example Client A may have sales data
for june, july and august, while Client B may only have
data for june. Based on the number of months the client
has data associated with it, we want to multiply
TotalSales by a unique multiplier and place that value in
BaselineTotal. For example, clients with data only in
June would have a multiplier of 1, while clients with data
for June and July would have a multiplier of 1/2.

How can I loop through each record, one client at a time
and determine how many months of data are associated with
each client to create this expression? Any help would be
most appreciated!
 
Dorene,

I haven't tested this, but it should show the number of months in the
current year, that each client has sales data for.

SELECT Count(X.SaleMonth) As MonthCount, X.ClientID
FROM (SELECT DISTINCT Month(SalesDate) As SaleMonth, ClientID
FROM tblMyTable
WHERE Year(SalesDate) = Year(Date())) As X
GROUP BY X.ClientID

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top