Results 1 to 2 of 2

Thread: Filtering data

  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Unanswered: Filtering data

    Hi,

    I have two tables, one that stores business data, one for collections that we make from the businesses - a one-to-many (one business has many collections).

    I am trying to obtain a list of companies and thier LAST collection date (within certain criteria) so that I can determin when to collect again from them.

    Currently i can get the last collection that are a week or older but this is REGARDLESS of wether they have had a collection within the last week.

    What i really need is to display the last collection (along with the details) for the companies that have NOT HAD a collection in the last week.

    Can anyone help me with this please?

    Many thanks,

    Nick.


    Here is the SQL so far: -

    --------------------------------------------------------
    SELECT Max(tblCollections.LastCollectionDate) AS MaxOfLastCollectionDate , tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency

    FROM tblBusiness INNER JOIN tblCollections ON

    tblBusiness.BusinessID=tblCollections.BusinessID

    WHERE (((Item1='Cans') Or (Item3='Cans') Or (Item2='Cans')) And (Frequency='Weekly') And (tblCollections.LastCollectionDate<=Date()-7))

    GROUP BY tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency

    ORDER BY tblBusiness.BusinessName;
    ------------------------------------------------------------

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Re: Filtering data

    Originally posted by mrnicknick
    Hi,

    I have two tables, one that stores business data, one for collections that we make from the businesses - a one-to-many (one business has many collections).

    I am trying to obtain a list of companies and thier LAST collection date (within certain criteria) so that I can determin when to collect again from them.

    Currently i can get the last collection that are a week or older but this is REGARDLESS of wether they have had a collection within the last week.

    What i really need is to display the last collection (along with the details) for the companies that have NOT HAD a collection in the last week.

    Can anyone help me with this please?

    Many thanks,

    Nick.


    Here is the SQL so far: -

    --------------------------------------------------------
    SELECT Max(tblCollections.LastCollectionDate) AS MaxOfLastCollectionDate , tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency

    FROM tblBusiness INNER JOIN tblCollections ON

    tblBusiness.BusinessID=tblCollections.BusinessID

    WHERE (((Item1='Cans') Or (Item3='Cans') Or (Item2='Cans')) And (Frequency='Weekly') And (tblCollections.LastCollectionDate<=Date()-7))

    GROUP BY tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency

    ORDER BY tblBusiness.BusinessName;
    ------------------------------------------------------------
    Hi

    Not sure if I read what you are trying to get correctly, but would:

    SELECT Max(tblCollections.LastCollectionDate) AS MaxOfLastCollectionDate , tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency

    FROM tblBusiness INNER JOIN tblCollections ON

    tblBusiness.BusinessID=tblCollections.BusinessID

    WHERE (((Item1='Cans') Or (Item3='Cans') Or (Item2='Cans')) And (Frequency='Weekly') And (tblCollections.LastCollectionDate<=Date()-7))

    GROUP BY tblBusiness.BusinessName, tblBusiness.Address1, tblBusiness.Address2, tblBusiness.Town, tblBusiness.WorkPhone, tblBusiness.ContactName, tblBusiness.Frequency

    HAVING (((Max(tblCollections.LastCollectionDate)) Not Between Date() And (Date()-7)))

    ORDER BY tblBusiness.BusinessName;

    sort it out?

    Hope this helps.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •