?
Solved

How to create a lost clients dynamic query in MS Access

Posted on 2009-04-29
9
Medium Priority
?
255 Views
Last Modified: 2013-11-28
I am looking to create a query that will determine if a client was lost during a specified time period.  Loosing a client is defined as having revenues for that client go to zero and stay at zero for the duration of the user specified time period.

The query will be based on a data table that holds client details and revenue info with a column for each month dating back to 2007.  Each month, this table gets refreshed and another revenue month column is refreshed with data.

Any thoughts of how I could start building this thing.  Assume I am starting with a form and command button.
0
Comment
Question by:pgerman
  • 5
  • 4
9 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 2000 total points
ID: 24268128
Repeating columns is not a good design and makes the query you want much harder to build.
Try this instead:
Table Revenue(ClientID, YearMonth, Amount)

The Query for your form then becomes:

Select cd.* From ClientDetails as cd Inner Join (Select ClientID From Revenue Where YearMonth between StartMonth and EndMonth Group by ClientID Having Sum(Amount) = 0) as Lost on Lost.ClientID = cd.ClientID
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24268137
I should have added that StartMonth and EndMonth should be pointed to the controls on your form in which the user will enter the values
0
 

Author Comment

by:pgerman
ID: 24268696
I realize the design of the table isn't optimal, but I dont have a choice, are there any other options?  This is what the table looks like:
(see attachment) refer to ' masterdata' table
sample.zip
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:pgerman
ID: 24268711
what is 'cd'
0
 

Author Comment

by:pgerman
ID: 24268758
and would the query you wrote capture a client that had revenue in the first few months of the selected time period but then 0 in the last couple?  Also what about a client that has revenue then 0 then revenue, in this case they should not be considered lost
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24269065
The query example groups the records for a ClientID for the months specified into a single result and selectss only those ClientIDs where the sum of the Amount is zero, thus only the ones with no revenue at all are included.
"cd" is the Alias name I've given the table "ClientDetails". Since there are two row sets in the query, access needs to be told which one you are refering to.
... ClintDetails as cd ...
You can leave out "as cd" and replace all occurances of "cd." by "ClientDetails."

With the current structure of mastertable it will require some code to create a solution. In the end it would be much better to split into several Normalized tables - you say that the table is refreshed every month, that could be a time to populate a set of better designed tables.
0
 

Author Comment

by:pgerman
ID: 24270346
For the code logic I was thinking of something that would take in to account the user defined time period, then loop through the table for that given time period an d assign a value for the sum of revenues by account true or false.  These values would get written into a temp table with the fields being all the possile months in the selected time period.  Then the function would have to go through the temp table and pick out only those records that have one or more trues followed by all falses.   Does this make sense or seem possible?
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24270631
Possible? Yes, certainly.
0
 

Author Comment

by:pgerman
ID: 24270773
the problem is I am very new to VBA, so I am not exactly sure how to get started.  I will try to start some things and then maybe post them if you are still interested in giving me a hand.  Thanks for the time
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question