need help with a sql statement

[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.

 

[June7]

Do these queries work? I see structure that I would expect to fail. Did you
use Query designer to help build them and have you saved them or were they
written freestyle in code?

 

[Daniel M]

Yes both of these queries work. Access does some strange things to them in
the designer so they had to be written by hand. If i try to combine them with
a nested select access keep modifying the ' and [ in spots and breaks them.
if i split them and put them in just like this, each one works.

Do these queries work? I see structure that I would expect to fail. Did you
use Query designer to help build them and have you saved them or were they
written freestyle in code?

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.

 

[John W. Vinson]

Yes both of these queries work. Access does some strange things to them in
the designer so they had to be written by hand. If i try to combine them with
a nested select access keep modifying the ' and [ in spots and breaks them.
if i split them and put them in just like this, each one works.

Access (at least some versions) will put subqueries inside square brackets,
e.g.

SELECT this, that, theother, [SELECT Count(*) FROM secondtable]. AS TheCount
FROM firsttable;

Note the brackets and the odd looking period after the close bracket.

This works, but it breaks bigtime if the subquery itself contains anything in
square brackets! This puts you in a dilemma if you unwisely use blanks or
special characters in fieldnames: you must use square brackets to use the
fieldname, and you must NOT use square brackets for the subquery.

 

[June7]

JWV has a point about the spaces. I avoid spaces in any names - fields,
tables, queries, forms. I use upper and lower case to make readable, ex.:
DateEnter. If you really want to spread out the words, use underscore, ex.:
Date_Enter; I don't like having to type punctuation, increases my typo
frequency. Does JWV suggestion get you any closer to where you need to go.
I am having hard time following what you need without have data to look at.

Yes both of these queries work. Access does some strange things to them in
the designer so they had to be written by hand. If i try to combine them with
a nested select access keep modifying the ' and [ in spots and breaks them.
if i split them and put them in just like this, each one works.

Access (at least some versions) will put subqueries inside square brackets,
e.g.

SELECT this, that, theother, [SELECT Count(*) FROM secondtable]. AS TheCount
FROM firsttable;

Note the brackets and the odd looking period after the close bracket.

This works, but it breaks bigtime if the subquery itself contains anything in
square brackets! This puts you in a dilemma if you unwisely use blanks or
special characters in fieldnames: you must use square brackets to use the
fieldname, and you must NOT use square brackets for the subquery.

 

[June7]

Part of what has me confused is you say 'combine the tables' but the queries
each concern the same table. You possible meant 'combine the queries'? You
will need to save the queries first, even if you have to modify table fields
to do it or do more intermediate queries. Eventually, you will have a query
that can include a field to do something like:
Iif(IsNull(battery), born, battery) As StatusDate
Or equivalent
Nz(battery, born) As StatusDate

JWV has a point about the spaces. I avoid spaces in any names - fields,
tables, queries, forms. I use upper and lower case to make readable, ex.:
DateEnter. If you really want to spread out the words, use underscore, ex.:
Date_Enter; I don't like having to type punctuation, increases my typo
frequency. Does JWV suggestion get you any closer to where you need to go.
I am having hard time following what you need without have data to look at.

Yes both of these queries work. Access does some strange things to them in
the designer so they had to be written by hand. If i try to combine them with
a nested select access keep modifying the ' and [ in spots and breaks them.
if i split them and put them in just like this, each one works.

Access (at least some versions) will put subqueries inside square brackets,
e.g.

SELECT this, that, theother, [SELECT Count(*) FROM secondtable]. AS TheCount
FROM firsttable;

Note the brackets and the odd looking period after the close bracket.

This works, but it breaks bigtime if the subquery itself contains anything in
square brackets! This puts you in a dilemma if you unwisely use blanks or
special characters in fieldnames: you must use square brackets to use the
fieldname, and you must NOT use square brackets for the subquery.

 

[June7]

Are you familiar with Access table Domain functions (DLookup, DMin, DMax,
DFirst, etc)? They can be used in textboxes, macros, VBA, queries. Example:
SELECT boardid, Iif(IsNull(DLookup([batterycheck pt2], 'boardid', [component
reference]=’battery’)), DMin('dateinput', 'boardid', [component
reference]='battery’)) As DateStatus
FROM boardid
GROUP BY boardid

I am not sure how to handle the punctuation because of the spaces and [] in
field names. These expressions act as queries, so the same delimma presents
when nested. Again, they might pose problem in Query designer as well.
However, I have used the DLookup function extensively in my project,
particularly when I only need one specific value, not a set of records. Used
in VBA (coded queries as well as standalone expressions, textboxes, and saved
queries.

Part of what has me confused is you say 'combine the tables' but the queries
each concern the same table. You possible meant 'combine the queries'? You
will need to save the queries first, even if you have to modify table fields
to do it or do more intermediate queries. Eventually, you will have a query
that can include a field to do something like:
Iif(IsNull(battery), born, battery) As StatusDate
Or equivalent
Nz(battery, born) As StatusDate

JWV has a point about the spaces. I avoid spaces in any names - fields,
tables, queries, forms. I use upper and lower case to make readable, ex.:
DateEnter. If you really want to spread out the words, use underscore, ex.:
Date_Enter; I don't like having to type punctuation, increases my typo
frequency. Does JWV suggestion get you any closer to where you need to go.
I am having hard time following what you need without have data to look at.

On Sat, 6 Jun 2009 09:17:01 -0700, Daniel M

Yes both of these queries work. Access does some strange things to them in
the designer so they had to be written by hand. If i try to combine them with
a nested select access keep modifying the ' and [ in spots and breaks them.
if i split them and put them in just like this, each one works.

Access (at least some versions) will put subqueries inside square brackets,
e.g.

SELECT this, that, theother, [SELECT Count(*) FROM secondtable]. AS TheCount
FROM firsttable;

Note the brackets and the odd looking period after the close bracket.

This works, but it breaks bigtime if the subquery itself contains anything in
square brackets! This puts you in a dilemma if you unwisely use blanks or
special characters in fieldnames: you must use square brackets to use the
fieldname, and you must NOT use square brackets for the subquery.