Selecting the First Occurrence Query help

[rdleigh]

Hello,

I need to track new customers by the first visit to our facility, and by the type of trigger ( that is to say, there is a Reference field within the database containing 24 different triggers, to coina phrase, such as by brochure, referral, drive-by, Yellow Pages, etc.).

I have tried the following SQL query statements (two different tables created) but find that it not only does not eliminate subsequent date visits, but does not correctly report the very first visit of that customer within (example) 2001 if the customer actually has a prior history with us.

*********
1st Time Customer Query GV 2001 : Make Table Query (creates 1st Time Client Table)

SELECT DISTINCTROW Customers.lastname, Customers.firstname, Customers.address, Customers.city, Customers.state, Customers.zip, First(Payments.PayDate) AS FirstofPayDate, Payments.PayAmt, LEADSOURCE.longcode, LEADSOURCE.datacode, LEADSOURCE.datastring INTO [1st TIME CLIENT TABLE]
FROM ((Customers INNER JOIN Payments ON Customers.cust_ID = Payments.cust_ID) INNER JOIN Market ON Customers.cust_ID = Market.cust_ID) INNER JOIN LEADSOURCE ON Market.Ref1 = LEADSOURCE.longcode
GROUP BY Customers.lastname, Customers.firstname, Customers.address, Customers.city, Customers.state, Customers.zip, LEADSOURCE.longcode, LEADSOURCE.datacode, LEADSOURCE.datastring, Payments.PayAmt
HAVING (((First(Payments.PayDate)) Between [Enter FROM Date] And [Enter TO Date]))
ORDER BY Customer.lastname;

PROBLEM – THIS CREATES DUPLICATES, I.E., ALL VISITS BY THAT CUSTOMER DURING THAT YEAR SHOW UP…AND IF CUSTOMER HAD PRIOR HISTORY, HE SHOULD NOT BE REPORTED IN THIS QUERY/TABLE.

*********************************************
GLENVIEW NEW CLIENT 2001 QUERY MT : Make Table Query (creates GLENVIEW NEW CLIENTS 2001 table)

SELECT DISTINCTROW Customers.lastname, Customers.firstname, Customers.address, Customers.city, Customers.state, Customers.zip, First(Payments.PayDate) AS FirstofPayDate INTO [GLENVIEW NEW CLIENTS 2001]
FROM Customers INNER JOIN Payments ON Customers.cust_ID = Payments.cust_ID
GROUP BY Customers.lastname, Customers.firstname, Customers.address, Customers.city, Customers.state, Customers.zip
HAVING (((First(Payments.PayDate)) Between [enter FROM date] And [enter TO date]))
ORDER BY Customers.lastname, Customers.zip;

Can someone explain how to find the very first occurrence (visit) of a customer (in this case, for 2001 in a database that goes back to 1995 and has tens of thousands of records,) then reports the source of that client (trigger) and the sales volume so generated?

The goal is to make informed decisions on our marketing and advertising programs. Thank you for your help.

 

[David R]

Did you try the TOP predicate?

SELECT TOP
...
ORDER BY [DateField]

A WHERE clause will restrict the data to the year in question.

HTH,
David R

P.S. Are you sure you need to create tables for this? Access can read the queries in most any instance it would read the table for, unless you need a snapshot of the data as of today. Otherwise your data is duplicated and will likely get out of sync at some point.

 

[rdleigh]

David,

Thanks so much for the help. However, I find that "Top" is a reserved word or function and I cannot find any sample SQL expressions that use the term. I do not understand the Microsoft Jet application, and do not even see how to install it in my system, although the system info on the pc indicates the following software environment; MS Windows 2000 Pro v.5.0.2195 Service Pack 2, Access 2000 (9.0.2720), Jet version 4.0. I'm in way over my head...

You might reasonably conclude by this time that I am a novice, not a professional programmer. This would be accurate, but shhhhh don't tell anyone ;-). I inhabit that lower echelon of non-power users where I'm barely informed and everyone else here has even less of a clue... that's the scary part. The boss is still looking to me for info and answers. I can rather clumsily get around in MS Excel and get useful data, but Access is still Swahili to me...(given that I do understand some of the basics...)

Perhaps you could direct me to a good tutorial on MS Jet 4.0? I promise not to pester anyone too much on this forum, and will try to bring better questions next time. Thanks very much for your help.

 

[David R]

LOL, no worries. Take a look here if you want some perspective on where I started (also in the "I am the one eyed man in the kingdom of the blind" role): http://www.access-programmers.co.uk/ubb/Forum3/HTML/002378.html

3 months and 160-some posts later .. I now can form questions that are much more arcane. This is progress.. I think.

But back to your question. This article from the Microsoft knowledge base may help.
Q153747 - ACC How to Create a Top N Values per Group Query http://support.microsoft.com/default.aspx?scid=kb;en-us;Q153747

And this article has some example databases you can use to build from. I think you have a pretty good grasp on the SQL, just follow the examples.
You can also get quite a few good ideas from users here by searching for 'select top' in the Queries Forum.

HTH,
David R