"OR" trouble.

[Guest]

I have two tables. One, "Units," contains an inventory of units (primary key
is a "Unit ID") and the other, "Inspections," is a record of inspections
performed on the units (primary key is "Inspection ID" and the field also
contains the Unit ID of the unit to relate the tables to each other. The
unit may have been inspected more than once, so there are multiple occurances
of the "Unit ID" in the inspections table.

I'd like to create a query that contains the following:

1) One (and only one) instance of each unit in inventory.
2)
-a- Only the LAST date that the unit was inspected, if it has been inspected.
-b- The string, "Not Inspected Yet", if it hasn't been inspected

I'm used to using php and mySQL, and I'm a newbie at Access. In php, I'd
create a primary query and a while loop to populate an array with the units,
and then a secondary query and loop with a conditional in it to determine the
inspection status.

How to I accomplish this in Access? Have I designed the database incorrectly?

 

[Guest]

primary key is "Inspection ID" and the field also

contains the Unit ID

I didn't make that clear. "Inspection ID" and "Unit ID" are separate
columns in the Inspections table.

 

[Guest]

Try this ---
SELECT UNITS.[Unit ID], UNITS.X, Max(IIf([InspDate] Is Null,"Not Inspected
Yet",[InspDate])) AS [Last Inspection]
FROM UNITS LEFT JOIN Inspections ON UNITS.[Unit ID] = Inspections.[Unit ID]
GROUP BY UNITS.[Unit ID], UNITS.X;

 

[John Spencer]

Shouldn't that be

SELECT UNITS.[Unit ID], UNITS.X
, Nz(Max([InspDate]),"Not inspected") AS [Last Inspection]
FROM UNITS LEFT JOIN Inspections
ON UNITS.[Unit ID] = Inspections.[Unit ID]
GROUP BY UNITS.[Unit ID], UNITS.X;

or

SELECT UNITS.[Unit ID], UNITS.X
, IIF(Max([InspDate]) Is null,"Not inspected",Max(InspDate))
AS [Last Inspection]
FROM UNITS LEFT JOIN Inspections
ON UNITS.[Unit ID] = Inspections.[Unit ID]
GROUP BY UNITS.[Unit ID], UNITS.X;
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

The IIF statement worked for me - Access 2003 SP2 Windows XP.
--
KARL DEWEY
Build a little - Test a little

Shouldn't that be

SELECT UNITS.[Unit ID], UNITS.X
, Nz(Max([InspDate]),"Not inspected") AS [Last Inspection]
FROM UNITS LEFT JOIN Inspections
ON UNITS.[Unit ID] = Inspections.[Unit ID]
GROUP BY UNITS.[Unit ID], UNITS.X;

or

SELECT UNITS.[Unit ID], UNITS.X
, IIF(Max([InspDate]) Is null,"Not inspected",Max(InspDate))
AS [Last Inspection]
FROM UNITS LEFT JOIN Inspections
ON UNITS.[Unit ID] = Inspections.[Unit ID]
GROUP BY UNITS.[Unit ID], UNITS.X;
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Try this ---
SELECT UNITS.[Unit ID], UNITS.X, Max(IIf([InspDate] Is Null,"Not Inspected
Yet",[InspDate])) AS [Last Inspection]
FROM UNITS LEFT JOIN Inspections ON UNITS.[Unit ID] = Inspections.[Unit ID]
GROUP BY UNITS.[Unit ID], UNITS.X;

 

[Guest]

Both of your responses are helpful, but both of them are causing Access to
throw an error:

You tried to execute a query that does not include the specified expression
'X' as part of an aggregate function.

(where 'X' is another column I'm trying to select from the Units table)

Any ideas? If I take Units.X out of the query, it runs just fine.

 

[John Spencer]

Post what is NOT working.

I suspect that you need to add Units.X to the GROUP BY clause.

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

 

[Guest]

Yep, that's it. Thanks a lot.