Group Query Results

[Lstclair55]

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]

[johnhutchison]

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.

[AndrewKKWalker]

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.