I am trying to workout a way that I can run a query that will search a number of columns and bring me the latest date back for that id number
AddressID Appt1 Appt2 Appt3 Appt4 Appt5
101 07/04/2008 14/04/2008 22/04/2008 01/05/2008
102 06/04/2008 13/04/2008 21/04/2008 23/04/2008 08/05/2008
So what I am trying to understand is in my query I want to add another column thats say MaxAppt and that would look accross the fileds and pick out the latest Appt dates... (in this case would be 01/05/2008 & 08/05/2008)
are they always in ascending date order?
can you always rely on the last 10 digits being the last gdfate the prioperty was viewed
if so you could do somethign similar to
its not as robust As I'd like it but....
the isnull caters for properties with no appointments the right$ chops off the last 10 characters.
incidentally, part of the reason why you are struggling with this is that in my books the design is flawed...
one you are storing dates as text.. never a good idea, what happens if theres a typo, or if say da boss suddenly wants to know what properties haven't had an appointment in n days/weeks/months
you design is not open ended.. what happens if say there are more than than say 22 appointments if you are suing a text field.
it would nmake more sense in my books to have appointments as a sub table hanging off a specific property, possibly linked with the agent who took the prospective customers round. so you could identify which agent had done which viewings, which properties a customer has looked at....
just my tuppeny ha'porth
The dates are always in ascending order with Appt1 being the first appointment. I can rely on this as its a application instruction from the front end DB that inputs into the field. So it will always be a date that gets entered and in the same format
Using this iif(isnull(<mycolumn>,"",right$(<mycolumn>,10))
What happens when there is only 1 appointment made and the Appt2 to 5 are blank?
Can you explain that formula a bit more I dont understand it, I guess I'm inputting it this way?
Its not open ended there are only a maximum of 5 appointments that can be made
I know it would have made more sense to make this a different way by a seperate table and using it as a listing. That would been far easier but I only report from the DB, I didnt build it! unfortuately!!
I cant amend the Database, I dont own the DB. All I have is an ODBC link tables Access Database to query the tables. If I created a table that would put each appointment on a single row, so instead of having 5 columns of dates how easy would it be to replicate it and have 5 rows?