Excel MS Query - Select No. of Visits before and after contact 2

[BxWill]

Have a 14 column Excel 2007 worksheet with columns titled;

CustomerName
DateContacted
Jan2009
Feb2009
Mar2009
Apr2009
May2009
Jun2009
Jul2009
Aug2009
Sep2009
Oct2009
Nov2009
Dec2009

Within each row for the date columns, are the number of customer visits.

Objective is to compare the number of visits after the date the customer was contacted relative to the number of visits before the customer was contacted.

Currently experimenting with MS Query but haven't had much success.

Any suggestions as to how the objective can be accomplished is appreciated.

 

[SkipVought]

Hi,

Your data structure is realy bad. You have, what is referred to as non-normalized data. It is a common mistake that novice spreadsheet users make.

Furthermore, you have no real dates in your data. Month and Year do not a date make.


With these two glaring errors in your design, you will be hard pressed to find you data useful in Excel.

Here's is what you need to do.

1) In the Jan2009 cell, enter Jan 2009 and hit ENTER. Then grab the Fill Handle and drag across for 12 columns. (Check Excel HELP on Turn on or off drag-and-drop editing) This will place REAL DATES, the first of every month, in your 12 headings.

2) use this technique to NORMALIZE your data, making it possible to Query and obtain the information you need. faq68-5287

To open the PT Wizard in 2007, alt+D P




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

What was previously posted was the result of a pivot of the data and the dates were grouped by month/year.

Data initially displayed as;

Customer Name
Date Contacted
Month
Number of Visits

 

[SkipVought]

Your PT source data is what you ought to query and analyze.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Is anyone able to provide a sql-based solution to the problem assuming that the data is formatted as displayed below?

Trying to assess the "productivity" of the outreach calls in minimizing the number of visits...

Now experimenting with the use of sumproduct and the subtotal function.

Thanks in advance.


CustomerName--DateContacted--VisitDate---Visits
JaneSmith-----5/15/2009------1/2/2009-----1.0
JaneSmith-----5/15/2009------2/20/2009----1.0
JaneSmith-----5/15/2009------3/5/2009-----1.0
JaneSmith-----5/15/2009------4/23/2009----1.0
JaneSmith-----5/15/2009------5/24/2009----1.0
JaneSmith-----5/15/2009------6/15/2009----1.0
JaneSmith-----5/15/2009------7/12/2009----1.0
.
.
.

 

[SkipVought]

SELECT
  CustomerName
, DateContacted
, SUM(IIF(VisitDate<DateContacted, Visits,0)) AS 'PriorVisits'
, SUM(IIF(VisitDate>DateContacted, Visits,0)) AS 'AfterVisits'

FROM `Sheet1$` 

GROUP BY
  CustomerName
, DateContacted

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Well, if you can't normalize the data, you would do this:

SELECT v.Name, v.ContactDate, vb.VisitsBefore, va.VisitsAfter
FROM `X:\Path\visits`.`visits$` v, (SELECT Name, COUNT(Visits) AS VisitsBefore FROM `X:\Path\visits`.`visits$` WHERE VisitDate < ContactDate GROUP BY Name) AS vb, (SELECT Name, COUNT(Visits) AS VisitsAfter FROM `X:\Path\visits`.`visits$` WHERE VisitDate > ContactDate GROUP BY Name) AS va
WHERE v.Name = vb.Name AND v.Name = va.Name GROUP BY  v.Name, v.ContactDate, vb.VisitsBefore, va.VisitsAfter

 

[SkipVought]

He's got the normalized data, is seems.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Assume that I can create the sql using MS Query since the data is within Excel.

 

[SkipVought]

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}