Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have three tables. A viewing table labelled 'tblViewing' which contains lots of records about viewings of properties that are going to take place.

    The second table, is 'tblProperty', which has information on a record by record basis of individual properties for sale.

    The viewing table can have may references to tblProperty for different viewings that have occured at a property, based upon the 'PropertyID' field.

    In the query, on a particular day I want to show which properties are being viewed. THerefore if a property is being viewing three times, I do not want it showing up three times, but only once. The list needs to be organised wtih the viewing time arrange in ascending order. Obviously where there are multiplae times for the property, it needs to use the first one.

    I cannot seem to get this work. I can get it to work using 'Unique Values', but this does not work when the time field is introduced as obviously each record is not unique (as it has different times). A snap shot of the query screen is attached to help if needed.

    [attachment=87676:viewings.gif]
    Attached Images Attached Images
    Best Regards,

    Luke

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I am not sure what the third table is, and what effect that has, but

    I think you want the Min of AppTime when you group by Date and Property.

    You also want to Sort Ascending by Apptime.

    But it is not obvious (to me) why you only want the first appointment for each property.
    Regards
    John



  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I am guessing that if as John says you are going to use the MIN of the time rather than grouping by it,
    you might also want to put a count in so although you are going to see only the first viewing time
    you will find out if it is the only viewing.
    Indeed, why not have another column with MAX of the time, so you can see first and last times as well.
    Other than that we would need table data examples to see what is going on.
    Andrew

Posting Permissions

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