Searching Multiple Columns in Acess

[JezLisle]

Hi,

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
example
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)

How can this be done?

Someone mentioned using and iif statement, but didnt explain it properly so
am usure about it.

iif(isnull(<mycolumn>,"",right$(<mycolumn>,10))

I tried this and keeps coming up with expression error

iif(isnull(<Appt1>,"",right$(<Appt1>,10))


Thanks

 

[Duane Hookom]

In the words of fellow MVP, Jeff Boyce, "You have committed spreadsheet". I
would change the table structure so that each appointment created a record in
a separate table.

If you can't change your table structure to be more normalized, you can
create a union query like:

SELECT AddressID, 1 as ApptNum, Appt1 as ApptDate
FROM tblA
WHERE Appt1 Is Not Null
UNION ALL
SELECT AddressID, 2 , Appt2
FROM tblA
WHERE Appt2 Is Not Null
UNION ALL
SELECT AddressID, 3 , Appt3
FROM tblA
WHERE Appt3 Is Not Null
UNION ALL
SELECT AddressID, 4 , Appt4
FROM tblA
WHERE Appt4 Is Not Null
UNION ALL
SELECT AddressID, 5 , Appt5
FROM tblA
WHERE Appt5 Is Not Null;

Then save this query as quniAddressAppts. Create a new query like:
SELECT AddressID, Max(ApptDate) as MaxDate
FROM quniAddressAppts
GROUP BY AddressID;

 

[JezLisle]

Thanks for the reply.

The problem is that I didnt build the databse, thats someone else handy work.
I can only report from it, I havent seen that problem before as all other DB
I worked on have done as we would think they are built

In the words of fellow MVP, Jeff Boyce, "You have committed spreadsheet". I
would change the table structure so that each appointment created a record in
a separate table.

If you can't change your table structure to be more normalized, you can
create a union query like:

SELECT AddressID, 1 as ApptNum, Appt1 as ApptDate
FROM tblA
WHERE Appt1 Is Not Null
UNION ALL
SELECT AddressID, 2 , Appt2
FROM tblA
WHERE Appt2 Is Not Null
UNION ALL
SELECT AddressID, 3 , Appt3
FROM tblA
WHERE Appt3 Is Not Null
UNION ALL
SELECT AddressID, 4 , Appt4
FROM tblA
WHERE Appt4 Is Not Null
UNION ALL
SELECT AddressID, 5 , Appt5
FROM tblA
WHERE Appt5 Is Not Null;

Then save this query as quniAddressAppts. Create a new query like:
SELECT AddressID, Max(ApptDate) as MaxDate
FROM quniAddressAppts
GROUP BY AddressID;

Hi,

[quoted text clipped - 21 lines]

Thanks

 

[Duane Hookom]

Were you able to implement my suggestion?
--
Duane Hookom
Microsoft Access MVP

Thanks for the reply.

The problem is that I didnt build the databse, thats someone else handy work.
I can only report from it, I havent seen that problem before as all other DB
I worked on have done as we would think they are built

In the words of fellow MVP, Jeff Boyce, "You have committed spreadsheet". I
would change the table structure so that each appointment created a record in
a separate table.

If you can't change your table structure to be more normalized, you can
create a union query like:

SELECT AddressID, 1 as ApptNum, Appt1 as ApptDate
FROM tblA
WHERE Appt1 Is Not Null
UNION ALL
SELECT AddressID, 2 , Appt2
FROM tblA
WHERE Appt2 Is Not Null
UNION ALL
SELECT AddressID, 3 , Appt3
FROM tblA
WHERE Appt3 Is Not Null
UNION ALL
SELECT AddressID, 4 , Appt4
FROM tblA
WHERE Appt4 Is Not Null
UNION ALL
SELECT AddressID, 5 , Appt5
FROM tblA
WHERE Appt5 Is Not Null;

Then save this query as quniAddressAppts. Create a new query like:
SELECT AddressID, Max(ApptDate) as MaxDate
FROM quniAddressAppts
GROUP BY AddressID;

Hi,

[quoted text clipped - 21 lines]

Thanks

 

[John Spencer]

Here is a function that I have built that will get the max date. Sometimes
you just gotta do what you gotta do and work around the bad data structure.

You can use it in as follows. You are limited to a maximum of 29 arguments
when you use this function in a query.

Field: LastDate: fGetRowMax(Appt1,Appt2, Appt3, Appt4, Appt5)

'------------- Code Starts --------------
'Version to handle dates, numbers, or text values

Public Function fGetRowMax(ParamArray Values()) As Variant
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Returns the Maximum of a group of values passed to it.
'Handles text, date, & number fields.

Dim i As Long, vMax As Variant
vMax = Null

For i = LBound(Values) To UBound(Values)
If IsNull(Values(i)) = False Then
If Values(i) <= vMax Then
Else
vMax = Values(i)
End If
End If
Next

fGetRowMax = vMax

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks for the reply.

The problem is that I didnt build the databse, thats someone else handy work.
I can only report from it, I havent seen that problem before as all other DB
I worked on have done as we would think they are built

In the words of fellow MVP, Jeff Boyce, "You have committed spreadsheet". I
would change the table structure so that each appointment created a record in
a separate table.

If you can't change your table structure to be more normalized, you can
create a union query like:

SELECT AddressID, 1 as ApptNum, Appt1 as ApptDate
FROM tblA
WHERE Appt1 Is Not Null
UNION ALL
SELECT AddressID, 2 , Appt2
FROM tblA
WHERE Appt2 Is Not Null
UNION ALL
SELECT AddressID, 3 , Appt3
FROM tblA
WHERE Appt3 Is Not Null
UNION ALL
SELECT AddressID, 4 , Appt4
FROM tblA
WHERE Appt4 Is Not Null
UNION ALL
SELECT AddressID, 5 , Appt5
FROM tblA
WHERE Appt5 Is Not Null;

Then save this query as quniAddressAppts. Create a new query like:
SELECT AddressID, Max(ApptDate) as MaxDate
FROM quniAddressAppts
GROUP BY AddressID;

Hi,

[quoted text clipped - 21 lines]

Thanks

 

[JezLisle via AccessMonster.com]

Yeah I tried it and works just fine. Thanks...

Were you able to implement my suggestion?

Thanks for the reply.

[quoted text clipped - 39 lines]