multiple query inaccuracies

[rdleigh]

It's been a while since I pestered anyone here, but once again, the body is attempting to do what the brain doesn't quite grasp, so here goes...

I need to create a report or series of reports that tell me clearly:
- the date of the first time a customer came to us and actually did business (i.e., our software assigns a Customer I.D. and a DOB of that ID number)
- how much that customer spent (both on his initial visit, as well as on subsequent visits)
- the source of that customer, as in the market trigger (for example, "Brochure Mailing 9/02" is one such criteria, out of a current possible 62 such categories)
- if that customer was, instead, a repeat customer...and therefore, his date of first visit, how much he spent then, and how much YTD and Lifetime totals
- and in the event that we neglected to enter the marketing criteria, I need to select those customers who probably have no value in that record field or a null value in that record field, then of course, the same info as above
- and I need to accomplish this with a database that has undergone at least four massive upgrades, including an annoying tendency to flutter between using mm/dd/yy, m/d/yy, and mm/dd/yyyy date formats during its' lifetime (began as DOS, went Win 3.11, Windows 95, Windows/Access 98)...

Having said all that, here are the (5) queries I have used so far with varying degrees of success in terms of accuracy:

I have created tables at each step with these make-table queries:

1. MATCH CUSTOMER TO HISTORY MAKE TABLE

SELECT Customers.cust_ID, History.cust_ID INTO [STEP 1 CUST TO HISTORY ID CROSSREF TABLE]
FROM Customers INNER JOIN History ON Customers.cust_ID = History.cust_ID
GROUP BY Customers.cust_ID, History.cust_ID
ORDER BY Customers.cust_ID;

Then...

2. BASIC CUSTOMER HISTORY QUERY MAKE TABLE

SELECT DISTINCTROW Customers.cust_ID, History.cust_ID, History.recno, History.dateposted, Customers.YTDTotal, Customers.LifeTotal, Market.DOB, Market.Ref1 INTO [STEP 2 BASIC CUSTOMER HISTORY TABLE]
FROM (Customers INNER JOIN History ON Customers.cust_ID = History.cust_ID) INNER JOIN Market ON Customers.cust_ID = Market.cust_ID
ORDER BY Customers.cust_ID;

Then...

3. STRINGS LEADSOURCE MAKE TABLE


SELECT Strings.datacode, Strings.longcode, Strings.datastring INTO [STEP 3 REFERRAL DESCRIPTIONS]
FROM Strings
GROUP BY Strings.datacode, Strings.longcode, Strings.datastring
HAVING (((Strings.datacode) Between [Enter LETTER] And [Enter LETTER]))
ORDER BY Strings.datacode;


Then...

4. 1ST OCCURRENCE MAKE TABLE QUERY


SELECT DISTINCTROW First([STEP 2 BASIC CUSTOMER HISTORY TABLE].Customers_cust_ID) AS Firstofcust_ID, [STEP 2 BASIC CUSTOMER HISTORY TABLE].DOB, [STEP 3 REFERRAL DESCRIPTIONS].datastring, History.dateposted, History.laboramt, History.partsamt, History.hazwasteamt, History.shopsuppliesamt INTO [STEP 4 1ST OCCURRENCE TABLE]
FROM ((Market INNER JOIN ([STEP 2 BASIC CUSTOMER HISTORY TABLE] INNER JOIN [STEP 1 CUST TO HISTORY ID CROSSREF TABLE] ON [STEP 2 BASIC CUSTOMER HISTORY TABLE].History_cust_ID = [STEP 1 CUST TO HISTORY ID CROSSREF TABLE].History_cust_ID) ON Market.cust_ID = [STEP 1 CUST TO HISTORY ID CROSSREF TABLE].Customers_cust_ID) INNER JOIN [STEP 3 REFERRAL DESCRIPTIONS] ON Market.Ref1 = [STEP 3 REFERRAL DESCRIPTIONS].longcode) INNER JOIN History ON Market.cust_ID = History.cust_ID
GROUP BY [STEP 2 BASIC CUSTOMER HISTORY TABLE].DOB, [STEP 3 REFERRAL DESCRIPTIONS].datastring, History.dateposted, History.laboramt, History.partsamt, History.hazwasteamt, History.shopsuppliesamt, [STEP 2 BASIC CUSTOMER HISTORY TABLE].Customers_cust_ID
ORDER BY [STEP 2 BASIC CUSTOMER HISTORY TABLE].Customers_cust_ID;


Then...

5. PUNT! NEW CLIENT MAKE TABLE


SELECT DISTINCTROW First(History.dateposted) AS Firstofdateposted, First(History.recno) AS FirstOfrecno, History.cust_ID, [STEP 3 REFERRAL DESCRIPTIONS].datastring, Customers.zip, First(History.laboramt) AS FirstOflaboramt, First(History.partsamt) AS FirstOfpartsamt, First(History.hazwasteamt) AS FirstOfhazwasteamt, First(History.shopsuppliesamt) AS FirstOfshopsuppliesamt, Customers.YTDTotal, Customers.LifeTotal INTO [STEP 5 PUNT VH 2001-2002 NEW CLIENTS]
FROM ((History INNER JOIN Market ON History.cust_ID = Market.cust_ID) INNER JOIN Customers ON History.cust_ID = Customers.cust_ID) INNER JOIN [STEP 3 REFERRAL DESCRIPTIONS] ON Market.Ref1 = [STEP 3 REFERRAL DESCRIPTIONS].longcode
GROUP BY History.cust_ID, [STEP 3 REFERRAL DESCRIPTIONS].datastring, Customers.zip, Customers.YTDTotal, Customers.LifeTotal
HAVING (((First(History.dateposted)) Between [enter FROM date] And [enter TO date]))
ORDER BY First(History.dateposted), First(History.recno), [STEP 3 REFERRAL DESCRIPTIONS].datastring, Customers.zip;


Whew!

So here's at least a part of the problem as my feeble mind understands it.

I have two separate locations to report these data for. One of these is newer, and the software did not go through the onsite evolution and upgrades that the original system has suffered. The data from that installation would seem to be far more accurate and trustworthy than from the original location, which is the one that includes the multiple date formats, for instance.

As an example, I run these make-table queries, then pull the resulting table data into Excel spreadsheets. Clumsy as it may seem, at least its' a known quantity to me and the sorting process with appropriate formulae seems inherently simpler to me. However, as you can see, I ask the database to give me the very first instance of a customer's visit, which it returns in the datasheet along with YTD and Lifetime spending. If, for instance, customer #3456 FIRST visit ever took place on Jan 3, 2003, and he has not been back a second time I should not see any Lifetime total in excess of his spending on that single first-time visit.

What I am seeing is the query returns ostensibly first-time clients, with totals, then shows me prior totals as well...obviously, my query is wrong.

The purpose of this exercise is primarily to track the effectiveness of our advertising and marketing programs, and thereby budget more effectively towards those programs that generate the highest returns. Gee, there's a novel idea...

I'll be happy to fill in more details if anyone feels the courage to step up and help me on this one. I'm so close, yet so far...

 

[rdleigh]

Wow, 21 views so far, no replies. Is it my breath? Or am I simply unworthy ;-)...?

As a follow-up question, given that the History table mentioned earlier violates First Normal Form by having multiple data types in the History.dateposted field as follows -

d/m/yy
dd/mm/yy
dd/mm/yyyy
dd/mm/yyyy hh/mm/ss am

- am I facing the need to separate this data by parsing it into a new table? How do I do that?

If that is in fact the next logical step, can I anticipate that after doing so I might get correct query results based upon running the Query #5 shown above?

Can anyone help, please? I know just enough to get into real trouble here...

Here's more info on the tables and fields that might make it easier to understand.


Table: Customers
Fields: 34 fields, of which only
cust_ID (numeric data, assigned sequentially in ascending order)
YTDTotal (dollar values to 2 decimal places)
LifeTotal (dollar values to 2 decimal places)
apply for purposes of this query

Table: History
Fields: 48 fields, of which only
cust_ID (same as above)
dateposted (the tough one - this field holds m/d/yy, m/d/yy, m/d/yyyy, m/dd/yy h:m:s AM, m/dd/yyyy h:m:s am etc.)
apply for purposes of this query

Table: Market
Fields: 17 fields, of which only
cust_ID (ditto)
Ref1 (text, the name of the source, i.e., Brochure)
DOB (mm/dd/yy, mm/dd/yyyy)
apply for purposes of this query

and

Table: Strings
Fields: 6 fields, of which only
datastring (text and numeric, but the name of the source i.e. Brochure is found in this field)
datacode (text, a reference letter or letters, in this case, "R" for "Referral")
longcode (numerical, the 4-digit code 1101 for Brochure, for example)
apply for purposes of this query.

Again, I simply want to construct a table that tells me the information listed in my first post, above.

I deeply appreciate your help.


Here's another question that perhaps only you congnoscenti can address... we have been told that Access ( in all it's incarnations to-date) is by virtue of poor design inherently susceptible to slowdown, and that it is not truly built for large multi-user applications. For instance, the software package we use is used in a 22-user network...but has recently experienced some slowdowns which the software vendor either insists are isolated/unusual/unique/nobody else ever has this problem or is the result of the environment (NT 4.0 server, etc.) in which we run it. Now, I don't mean to take my initial question about the database query problems off on a tangent, but does anyone know of the viewpoint some seem to hold that Access databases are seriously limited in any multi-user envrionment exceeding 6-8 users?