Max Min Queries

[ashley25]

I am trying to compare max and min customer visit dates to determine whether a re-visit has occurred; so, if the max visit date is > than the min then a re-visit has occurred etc.

I could do this by writing a query to determine the max date for each customer, another to determine the min date, then bring the results together, before inserting them into the main table.

However, is there a more streamlined way to do this? I did try to use an IIF In statement but this didn't work.

 

[jdraw]

Could you please describe the purpose of your database in terms of what your business is?
What are you recording re CustomerVisit? If a Customer visits say, 4 times --do you really want Min and Max? If a Customer has 2 visits recorded, wouldn't that indicate a revisit? Perhaps you could tell us more about a revisit --what is it really? Why is it important?...

Just some thoughts to consider.
Good luck.

 

[namliam]

You can do the min and max in one query to start with...
Also a simple count might do as well as suggested by JDraw
Never and I do mean NEVER should we actually store this information in any table... since it is already available thru a simple query and we NEVER (well almost never) duplicate data.