Finding New Customers in Single Table

[GoatieEddie]

Morning all,

I'm struggling with the syntax of something I thought would be very simple.

I have a table that everyday is appended with new values for each client (from an external DB). The column headings are:

Cust # Amount Date


What I am trying to do is write a query that returns all Customers that exist on say 30/6/05 but not 1/6/05 (ie new customer added over the period). I know I need to use nulls and have done it when comparing two separate tables with only one date each but can't work out the design when just using the single concatenated table. Can someone give me a nudge?

Thanks,
GE

 

[lupins46]

Is the date field holding the date the record is added?

 

[GoatieEddie]

Yep.

 

[lupins46]

Change the query below to use your own table and fieldnames and drop it into the SQL view of a new query.
It will ask you for the two dates and report all records added between the two dates (including those dates).

Select * from tblcustomers where [Date] Between [Enter Start Date] and [Enter End Date]

 

[GoatieEddie]

I don't think this does what I want. Surely this just lists those accounts that exist in between those dates. What I want is to show customers that exist on one day and not on another... Any other ideas?

 

[lupins46]

You have said that you want:
"all Customers that exist on say 30/6/05 but not 1/6/05 (ie new customer added over the period). "

and that the date in the record is the date the record was added.

On this basis:
The accounts that exist on 30/06/2005 that did not exist on 01/06/2005 must be those accounts with a 'date added' between these two dates.

If you think this is not correct then there must be more to your question and/or data than you said so far.

 

[GoatieEddie]

Well when I run it, it produces a list showing every client x times where x is the number of dates between start and end. And from my little knowledge of SQL that doesn't surprise. But that isn't what is required! You have understood the question perfectly but your code doesn't produce the right outcome. Surely I need to use a null somewhere to show the customer didn't exist on that date?

 

[lupins46]

Are you saying that there is one record PER DAY for each customer who exists on that day?

 

[lupins46]

If that is the case then do you want to ignore situations where records exist for say, 12,13,14,15th June but not at the start or end of the period in question?

Select q1.* from
(select * from tblCustomers where [date] =#2005-06-30#) as q1
left join (select * from tblCustomers where [date] =#2005-06-01#) as q2
on q1.cust# = q2.cust#
where q2.cust# is null

 

[PHV]

A starting point:
SELECT A.*
FROM tblCustomers As A LEFT JOIN tblCustomers As B
ON A.[Cust #] = B.[Cust #] AND A.Date = B.Date + 30
WHERE A.Date = #2005-06-30# AND B.Date Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GoatieEddie]

Thanks Lupins for your input
Thanks PHV. Can you explain the "AND A.Date = B.Date + 30" bit? Looking pretty good though!