Results 1 to 3 of 3

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 manage to get the last collection dates for comapanies out, but cannot get just the LAST collection date for each company given the criteria i have set.

    Here is the SQL so far: -

    ---------------------------------------------------
    SELECT tblCollections.LastCollectionDate, 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))

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

    Does anyone know what i mean? Does anyone know how i can solve this problem?

    Any help on this would be much appreciated as its driving me barmy : )

  2. #2
    Join Date
    Dec 2002
    Posts
    60

    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 manage to get the last collection dates for comapanies out, but cannot get just the LAST collection date for each company given the criteria i have set.

    Here is the SQL so far: -

    ---------------------------------------------------
    SELECT tblCollections.LastCollectionDate, 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))

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

    Does anyone know what i mean? Does anyone know how i can solve this problem?

    Any help on this would be much appreciated as its driving me barmy : )
    Off the top of my head (which is not too reliable these days), make the follwing changes:

    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;

    Hope this works.

  3. #3
    Join Date
    Apr 2004
    Posts
    5
    It did work, Thanks a million!!

Posting Permissions

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