1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access question

Discussion in 'Business Applications' started by angel, Jan 10, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. angel

    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!
     
  2. THoey

    THoey

    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.
     
  3. angel

    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?
     
  4. Rockn

    Rockn

    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.
     
  5. angel

    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!

    :)
     
  6. Rockn

    Rockn

    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!!
     
  7. angel

    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.
     
  8. THoey

    THoey

    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...
     
  9. angel

    angel Thread Starter

    Joined:
    Dec 2, 1998
    Messages:
    2,736
    Thanks! I'll give it a shot when I get back there!
     
  10. THoey

    THoey

    Joined:
    Feb 12, 2001
    Messages:
    3,420
    Did that query help?
     
  11. angel

    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.
     

    Attached Files:

  12. downwitchyobadself

    downwitchyobadself

    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.
     
  13. angel

    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!
     
  14. THoey

    THoey

    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...
     
  15. downwitchyobadself

    downwitchyobadself

    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.
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/64544

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice