If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"OR" trouble.
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? |
#2
|
|||
|
|||
"OR" trouble.
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. |
#3
|
|||
|
|||
"OR" trouble.
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; -- KARL DEWEY Build a little - Test a little "Brad Granath" wrote: 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. |
#4
|
|||
|
|||
"OR" trouble.
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 '================================================= === KARL DEWEY wrote: 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; |
#5
|
|||
|
|||
"OR" trouble.
The IIF statement worked for me - Access 2003 SP2 Windows XP.
-- KARL DEWEY Build a little - Test a little "John Spencer" wrote: 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 '================================================= === KARL DEWEY wrote: 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; |
#6
|
|||
|
|||
"OR" trouble.
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. |
#7
|
|||
|
|||
"OR" trouble.
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 .. "Brad Granath" wrote in message ... 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. |
#8
|
|||
|
|||
"OR" trouble.
Yep, that's it. Thanks a lot.
|
Thread Tools | |
Display Modes | |
|
|