Access question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

angel

Thread Starter
Joined
Dec 2, 1998
Messages
2,736
I have a database that includes customer information and then service calls.
I need to setup a query that filters out only the customer information of those who have not had a service call in the last 2 years.

I can get the list of those who had service calls longer than 2 years ago but need to exclude those who also had calls within two years. And if a customer hasn't had one in the last two years, I don't want a list of all those they did have longer than two years ago, just once.

Hmmm... does that make sense?

Any suggestions?

Thanks!
 
Joined
Feb 12, 2001
Messages
3,420
But I think it would be easier if you listed your table structures, or at least the key field parts of them.
 

angel

Thread Starter
Joined
Dec 2, 1998
Messages
2,736
Sorry 'bout that. I'm doing this from memory at the moment. One table is Customers and contains the customer information (CustomerID, Name, address, additional contact information). Second table is WorkOrders and contains that information(contains the CustomerID, work order numbers, workorderdate, etc).

Does that help?
 
Joined
Jul 29, 2001
Messages
21,334
If it doesn't have to be to the day, just do a query and subtract the current dates year from workorderdate and if it's true return a 1 and if it's false return a 0.

Iif(Year(Date())-year([workorderdate])>=2,1,0)

Set the criteria for 1 and it will filter the records out that are 2 or more years old. If you want to get down to the day, just take the year out of the expression and put the number of days in 2 years is at.
 

angel

Thread Starter
Joined
Dec 2, 1998
Messages
2,736
I can get a list of all those customers who have work orders that are older than two years old but I need it broken down further. For ex. Let say you're a customer. You had service on 6/1998, 8/1999, 2/2000, and 6/2001. There will be 4 entries in the WorkOrders table. If I just filter by date, you will pop up because 6/1998 and 8/1999 are more than 2 years ago. However, I don't want you in my list at all because you also had service within the last two years.
I need to get a list of those customer who have had no service at all in the last two years - or termed a little differently, those that had service more than two years ago but not within the last two years.

Thanks!

:)
 
Joined
Jul 29, 2001
Messages
21,334
Now my head is spinning....LOL Maybe I haven't had enough coffee yet. You could do anoth query on yuor first query to find the information you are looking for, but it seems like you are looking for a value in the date field that is not there or blank. If they didn't have service at all wouldn't the date field be blank? Or maybe you need to do a min and max on the dates. I'll have to think about this one.....i think I am having an anurism!!
 

angel

Thread Starter
Joined
Dec 2, 1998
Messages
2,736
Sorry to make your head hurt!
:D

Actually, if they haven't had service at all, they simply wouldn't have an entry in the WorkOrders table. But I don't necessarily want those that have never had service, just a list of those Customer's names, ID, etc that haven't had service for the last two years.
 
Joined
Feb 12, 2001
Messages
3,420
I tested this on a basic version of your tables:

SELECT distinct Cust.CustName
FROM Cust
WHERE Cust.CustID in
(SELECT DISTINCT Work.CustID
FROM Work
WHERE Work.WorkDate <= DATEADD('yyyy',-2,date())) AND
Cust.CustID not in
(SELECT DISTINCT Work.CustID
FROM Work
WHERE Work.WorkDate >= DATEADD('yyyy',-2,date()));

Create a new query, change to SQL view and cut and paste the following. Make sure you change all necessary table and field names...

Hope that helps...
 

angel

Thread Starter
Joined
Dec 2, 1998
Messages
2,736
Thanks! I'll give it a shot when I get back there!
 

angel

Thread Starter
Joined
Dec 2, 1998
Messages
2,736
Well, I was finally able to get there and actually take a look at it and it seems I was a little incorrect with my information about the tables. There are actually three linked tables: dbo_WorkOrders, dbo_Locations, and dbo_Customers.

Customers table: CustomerID, Customer name, etc

Locations: contains the physical location information for each Customer. Will have at least one record for each Customer but a Customer could have several Locations. The Location is what the WorkOrders are tied to. This is really the table that includes most of the information. Example of fields: CustomerID, LocationID, LocationName, Address, etc.

WorkOrders: contains all work order information.

So really I'm not all that concerned with the Customers table, just the other two since it 'should' the information they need.

I'm going to see if I can figure out how to change what you put above but if you have any additional information, that would still be greatly appreciated! :D

I uploaded an image of the tables and fields if you think it'll help.
 

Attachments

Joined
Oct 13, 2000
Messages
941
Hi. th's solution is right, but not necessarily easy to understand right off the bat. A simpler way to see what you're doing is to use an "aggregate" or group-by query.

You build a query, add all three of your tables. Make sure the joins carry over (you ought to add referential integrity to the joins, but that's a separate issue) in the QBE window. Add the customerID field, whatever else you want from the dbo_Customers table. Add the TakenDate field from the dbo_WorkOrders table. Then click the little sigma button in the toolbar at the top (tooltip is "totals"). You'll see that it adds an extra row named Total: right under the Table: line of the bottom half of the QBE window. All fields will say Group By; this means return only one instance of the given field, and you want that for all the dbo_Customers fields; you'll get one record per customer. Under the TakenDate, however, you'll want to set it to "Max" instead of Group By.

If you run your query at this point (and you may as well, to see what I mean), you'll see that it returns the latest date a work order was taken for each customer. So now all you have to do is on the criteria line under the TakenDate column in query design, add the following:
Code:
<DateAdd("yyyy",2,Date())
which translated into English means "must be less than (or older than, in the case of a date) two years before today's date". That's it.
 

angel

Thread Starter
Joined
Dec 2, 1998
Messages
2,736
Thanks, Down. I think I was able to change th3856's suggestion around to work but I haven't had a chance to inspect the results yet. Also, it took FOREVER for the query to run (many, many hours). I'll give your suggestion a shot here in a little bit.

Thanks for your help guys!
 
Joined
Feb 12, 2001
Messages
3,420
Busy at work. Hope you got it figured out. If you understand using the QBE tool better than SQL, Down's explanation is a good one. I am just so used to working with SQL, that QBE confuses me.

Either way, I hope you get the solution that you want...
 
Joined
Oct 13, 2000
Messages
941
The criteria line isn't showing up right on my browser:confused:

Here it is again just in case :

Code:
< DateAdd("yyyy",2,Date())
Yes, and subqueries tend to be very slow.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top