help with query

[Daniel M]

I currently need help with a sql statement.
I have a history table (boardid) with columns id, boardid, dateinput,
component reference and a couple more less important columns.

I have 1 query written in 2 parts that goes through the table and pulls the
last time any of the boardid's show the battery being changed.

SELECT boardid, max(dateinput) AS LastDate, max(id) AS LastID
FROM boardid
WHERE [component reference]='battery'
GROUP BY boardid;

and

SELECT *
FROM boardid AS t1, [Batterycheck pt2] AS t2
WHERE t1.boardid=t2.boardid and t1.dateinput = t2.Lastdate and
t1.id=t2.Lastid and t1.[component reference]='battery'
ORDER BY t1.boardid;

I now need to do the same thing but pull the first time the boardid had a
"NEW" entry in the component reference field. This give me the units born
date. I can do this similarly to how i did the last 2 queries. The problem is
i now need to write a query that will combine the tables but give me the
battery info first and if the unit does not have battery info it pulls the
born date entry. So i need to filter both queries on either a battery or born
new date entry but not both. Can someone give me some ideas on how to do this?

I tried select * where query1.boardid<>query2.boardid but it took too long
so i had to cancel the run.

thanks.

 

[Ken Snell [MVP]]

This query (using generic names for your two other queries) should do what
you seek. It uses the Nz function to replace a NULL value for the "last
date" from your "MAX" query with the "new date" from your "New" query:

SELECT boardid.boardid, Nz(MaxQueryName.LastDate,
NewQueryName.NEWDateField) AS LastDate
FROM (boardid LEFT JOIN MaxQueryName
ON boardid.boardid = MaxQueryName.boardid)
LEFT JOIN NewQueryName ON
boardid.boardid = NewQueryName.boardid;