call list with > 30 days but < 365 days

[MLHab306]

Happy New Year to the best Microsoft Access 2k on the web.

I am creating a scheduling book in access 2k. I am trying to create a call list for clients that have not been done in a minimum of 30 days up to 365 days. When I run the query I still on occasion get those done within the last 30 days or the output will include regular clients as well as once or three times a year client. I have included the sql. I have been working thru the design grid and know nothing about sql. The query also has other parameters to deal with also.

Thank you for your suggestions.
Mark

SELECT DISTINCTROW [cust2 table dogs].Caution, Customers.CustomerID, Date_last_done.Date_last_done, Date_last_done.Holiday, Date_last_done.Comments, Date_last_done.Payment_type, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.Address, Customers.City, Customers.HomePhone, Customers.FaxNumber, Customers.East_west, Customers.North_South, Customers.[Parking Restrictions], Date_last_done.Dog_1_charges, Date_last_done.Dog_2_charges, Date_last_done.Dog_3_charges, Date_last_done.Dog_4_charges, Date_last_done.Dog_5_charges, [cust2 table dogs].Dog_1_Name, [cust2 table dogs].Dog_2_Name, [cust2 table dogs].Dog_3_Name, [cust2 table dogs].Dog_4_Name, [cust2 table dogs].Dog_5_Name, [cust2 table dogs].Dog_Breed_1, [cust2 table dogs].Dog_2_Breed, [cust2 table dogs].Dog_3_Breed, [cust2 table dogs].Dog_4_Breed, [cust2 table dogs].Dog_5_Breed
FROM (Customers INNER JOIN [cust2 table dogs] ON Customers.CustomerID = [cust2 table dogs].CustomerID) INNER JOIN Date_last_done ON Customers.CustomerID = Date_last_done.Customer_ID
GROUP BY [cust2 table dogs].Caution, Customers.CustomerID, Date_last_done.Date_last_done, Date_last_done.Holiday, Date_last_done.Comments, Date_last_done.Payment_type, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.Address, Customers.City, Customers.HomePhone, Customers.FaxNumber, Customers.East_west, Customers.North_South, Customers.[Parking Restrictions], Date_last_done.Dog_1_charges, Date_last_done.Dog_2_charges, Date_last_done.Dog_3_charges, Date_last_done.Dog_4_charges, Date_last_done.Dog_5_charges, [cust2 table dogs].Dog_1_Name, [cust2 table dogs].Dog_2_Name, [cust2 table dogs].Dog_3_Name, [cust2 table dogs].Dog_4_Name, [cust2 table dogs].Dog_5_Name, [cust2 table dogs].Dog_Breed_1, [cust2 table dogs].Dog_2_Breed, [cust2 table dogs].Dog_3_Breed, [cust2 table dogs].Dog_4_Breed, [cust2 table dogs].Dog_5_Breed

HAVING ((([cust2 table dogs].Caution) Is Null) AND ((Customers.CustomerID)<>1) AND ((Date_last_done.Date_last_done) Between Date()-365 And Date()-30) AND ((Date_last_done.Holiday) Is Null) AND ((Date_last_done.Comments) Not Like &quot;xmas*&quot AND ((Date_last_done.Payment_type)=1 Or (Date_last_done.Payment_type)=2 Or (Date_last_done.Payment_type)=3 Or (Date_last_done.Payment_type)=4 Or (Date_last_done.Payment_type)=5 Or (Date_last_done.Payment_type)=13 Or (Date_last_done.Payment_type)=20 Or (Date_last_done.Payment_type)=22))
ORDER BY Customers.CustomerID, Date_last_done.Date_last_done;

 

[rac2]

After a quick look, it doesnt seem like you need to use a GROUP BY query to get the list of customers who need your service.

Try a simplified approach with just a few columns to figure out how to get just the rows you want. Then go back and add in the columns you need to make a customer call.

SELECT Customers.CustomerID,
       [cust2 table dogs].Dog_1_Name,
       Date_last_done.Date_last_done

FROM Customers
JOIN [cust2 table dogs] ON 
[cust2 table dogs].CustomerID = Customers.CustomerID
JOIN Date_last_done ON Date_last_done.Customer_ID = Customers.CustomerID

WHERE Date_last_done.Date_last_done BETWEEN ( Date()-365 ) AND ( Date()-30 )

Try this and see how close it comes.

Add the conditions for holidays and comments.



The conditions for payment type can be written like this-

 . . .
WHERE Date_last_done.Payment_type IN (1,2,3,4,5,13,20,22)

That will be a little bit easier to read and easier to change later on.



I strongly suggest you redefine the [cust2 table dogs] table to have one row for each dog and with these columns

DogID,CustomerID, . . .more columns for Dog features . . .

And redefine the Date_last_done table to be a table of Services with these columns
CustomerID, DogID, DateOfService, ServiceDesription

This will give you more flexibility as your database is extended and will permit easier retrieval of information.



Finally, dont be fooled into thinking that Access is a trivial tool that can be used effectively without understanding. It is easy to point and click your way into a big mess. Pick up a couple of books about relational databases and learn SQL. It is actually pretty easy. And the database concepts are pretty simple too. This will save you a lot of frustration.

Good luck.

 

[MLHab306]

Thank you for responding so quickly. I'll print up your response and give it a try.

Mark