Query that eliminates an entry if another one is retieved

G

Guest

Sorry for the poor title wording, I have two tables to make one query, one is
simply for convienence, here is the layout.

TABLE -TBLRideAlongMain
FIELD (primary key) -intRideAlongID
FIELD - strRideAlongName

TABLE - TBLRideAlongHistory
FIELD (foreignKey)- intRideAlongID (primary key is an autonumber and not
important)
FIELD - intActivity
FIELD - dtmDateofActivity

Example
TBLRideAlongMain
intRideALongID - 120
strRideAlongName - Joe Blow

TBLRideAlongHistory
Autonumberfield, intRideAlongID intActivity dtmDateofActivity
1 120 (Joe Blow), 12 01/01/2004
2 120 (Joe Blow), 13 01/01/2005
3 121 (John Smith), 12 01/01/2004

What i need to be able to do is query all activity 12 but only if there is
not a separate entry for Activity 13 for the same person, and activity 12
must have occurred more than 365 days prior, a simple >Date()-365 in the
criteria section

Any help would be appreciated
 
M

MGFoster

BDDewell408 said:
Sorry for the poor title wording, I have two tables to make one query, one is
simply for convienence, here is the layout.

TABLE -TBLRideAlongMain
FIELD (primary key) -intRideAlongID
FIELD - strRideAlongName

TABLE - TBLRideAlongHistory
FIELD (foreignKey)- intRideAlongID (primary key is an autonumber and not
important)
FIELD - intActivity
FIELD - dtmDateofActivity

Example
TBLRideAlongMain
intRideALongID - 120
strRideAlongName - Joe Blow

TBLRideAlongHistory
Autonumberfield, intRideAlongID intActivity dtmDateofActivity
1 120 (Joe Blow), 12 01/01/2004
2 120 (Joe Blow), 13 01/01/2005
3 121 (John Smith), 12 01/01/2004

What i need to be able to do is query all activity 12 but only if there is
not a separate entry for Activity 13 for the same person, and activity 12
must have occurred more than 365 days prior, a simple >Date()-365 in the
criteria section
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First off, Primary Keys (PK) ARE important. If you want to make the
AutoNumber columns in your tables usable as Foreign Keys, just place a
Unique index on them. BTW, the PK for the table "TBLRideAlongHistory"
are the columns Activity and DateOfActivity. A PK on those 2 columns
will restrict 1 type of activity per date. If you wanted more than 1
type of activity per date you'd have to add the Time value to the date.

Something that "occurred more than 365 days prior [to today]" is

the_date < (Today - 365)

Your query - try this:

SELECT M.intRideAlongID, H.intActivity, H.dtmDateOfActivity

FROM tblRideAlongMain As M INNER JOIN tblRideAlongHistory As H
ON M.intRideAlongID = H.intRideAlongID

WHERE H.intActivity = 12
AND H.dtmDateOfActivity < Date() - 365
AND NOT EXISTS (SELECT * FROM tblRideAlongHistory
WHERE intRideAlongID = H.intRideAlongID
AND H.intActivity = 13)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi4R1oechKqOuFEgEQKhJACfRgcdzXykJqBFM1rF890hdx7w0+sAoJbw
XudtG4ZbXklBmSarctCJJVdU
=WhC1
-----END PGP SIGNATURE-----
 
G

Guest

That got me half way there, it is still returning an entry for a person who
performed activity 12 and 13. BTW, I mislabeled one of my fields


dtmDateofActivity - Should be dtmDateConducted

I fixed that in the code you wrote, copied and pasted into a blank query in
SQL View, and got the results I mentioned above, any other thoughts.

BTW again, dtmDateConducted and intActivity can possibly occur and
frequently do occur on the same date for the same person, because at present
their are approximately 13 Activities associated with this table, not all of
them are used per person, but at least 3 of them usually occur on the same
day, that is why I used an autonumber field as the primary key, was that a
bad thing???


MGFoster said:
BDDewell408 said:
Sorry for the poor title wording, I have two tables to make one query, one is
simply for convienence, here is the layout.

TABLE -TBLRideAlongMain
FIELD (primary key) -intRideAlongID
FIELD - strRideAlongName

TABLE - TBLRideAlongHistory
FIELD (foreignKey)- intRideAlongID (primary key is an autonumber and not
important)
FIELD - intActivity
FIELD - dtmDateofActivity

Example
TBLRideAlongMain
intRideALongID - 120
strRideAlongName - Joe Blow

TBLRideAlongHistory
Autonumberfield, intRideAlongID intActivity dtmDateofActivity
1 120 (Joe Blow), 12 01/01/2004
2 120 (Joe Blow), 13 01/01/2005
3 121 (John Smith), 12 01/01/2004

What i need to be able to do is query all activity 12 but only if there is
not a separate entry for Activity 13 for the same person, and activity 12
must have occurred more than 365 days prior, a simple >Date()-365 in the
criteria section
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First off, Primary Keys (PK) ARE important. If you want to make the
AutoNumber columns in your tables usable as Foreign Keys, just place a
Unique index on them. BTW, the PK for the table "TBLRideAlongHistory"
are the columns Activity and DateOfActivity. A PK on those 2 columns
will restrict 1 type of activity per date. If you wanted more than 1
type of activity per date you'd have to add the Time value to the date.

Something that "occurred more than 365 days prior [to today]" is

the_date < (Today - 365)

Your query - try this:

SELECT M.intRideAlongID, H.intActivity, H.dtmDateOfActivity

FROM tblRideAlongMain As M INNER JOIN tblRideAlongHistory As H
ON M.intRideAlongID = H.intRideAlongID

WHERE H.intActivity = 12
AND H.dtmDateOfActivity < Date() - 365
AND NOT EXISTS (SELECT * FROM tblRideAlongHistory
WHERE intRideAlongID = H.intRideAlongID
AND H.intActivity = 13)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi4R1oechKqOuFEgEQKhJACfRgcdzXykJqBFM1rF890hdx7w0+sAoJbw
XudtG4ZbXklBmSarctCJJVdU
=WhC1
-----END PGP SIGNATURE-----
Click to expand...
 
M

MGFoster

BDDewell408 said:
That got me half way there, it is still returning an entry for a person who
performed activity 12 and 13. BTW, I mislabeled one of my fields


dtmDateofActivity - Should be dtmDateConducted

I fixed that in the code you wrote, copied and pasted into a blank query in
SQL View, and got the results I mentioned above, any other thoughts.

BTW again, dtmDateConducted and intActivity can possibly occur and
frequently do occur on the same date for the same person, because at present
their are approximately 13 Activities associated with this table, not all of
them are used per person, but at least 3 of them usually occur on the same
day, that is why I used an autonumber field as the primary key, was that a
bad thing???
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In my, and others, opinion it is a bad thing to use the AutoNumber
column as a PK, and there are many differing opinions. I really don't
want to get into it here. You can find numerous references to this
argument in the archives (Google's Groups).

I made an error in suggesting the PK for the table
"tblRideAlongHistory." It should be all 3 columns, since the ID column
is an FK. As I posted previously, "If you wanted more than 1 type of
activity per date you'd have to add the Time value to the date." That
should be the "...to the date column." That PK would allow the person
to have the same activity many times during the day. But, it really
doesn't matter 'cuz not having a PK would have the same effect if the PK
included a data/time column.

The Query:

The criteria on the query translates to:

"Return data where a person (intRideAlongID0 had an Activity of 12 that
occurred 365 days before today's date and that person has never had an
Activity 13."

Is this the correct criteria?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi4c+IechKqOuFEgEQKgeACgk+iy8bE6tvZKCqggvm1EX8sXF8gAnRpG
nCdLIXD8GD4IN/SINA9c9fTg
=+mMr
-----END PGP SIGNATURE-----
 
Top