Date queries

[Guest]

I have tried all the angles I can think of to get this query(ies) to work but
to no avail.

I have tPtData and tVisit. I need information from both tables.

From tPtData: ingPtID, strLastName, strFirstName, strFirstSeen (This is a
date mm/dd/yyyy). From tVisit: dtmVisitDate (date field), sng#MissFng,
sng#MissToe, ysnLimbLoss.

Basically, what I need to know is what persons had sng#MissFng, sng#MissToe,
ysnLimbLoss at their first visit date which is (strFirstSeen). dtmVisitDate
should match strFirstSeen. My problem is that I am getting back all of the
patient's visits, not just the dates that match each other. I have tried
different joins and no luck. I only want the information from the patient's
first visit, no subsequent visits. All visit data is stored in tVisit. The
relationship between each tables is ingPtID. tPtData's primary key is
ingPtID, tVisit has multiple primary keys (ingPtID and dtmVisitDate).

I can't figure out how to set up my query to get the result of just which
people had limb loss at their first visit.

 

[John Spencer]

Since you didn't post your SQL statement and didn't post the relating fields the
following is a guess.


SELECT P.ingPtID, strLastName, strFirstName, strFirstSeen,
dtmVisitDate, sng#MissFng, sng#MissToe, ysnLimbLoss
FROM tPtData as P INNER JOIN tVisit as V
ON P.ingPtID = v.ingPtID
WHERE P.strFirstSeen = v.dtmVisitDate


This assumes that strFirstSeen is a DATE FIELD as is dtmVisitDate and it also
assumes that both tables have a PtID field for the relationship between the two tables.

IF the assumptions are incorrect, you might want to post back with more detail
and a copy of the query that is not working.

Possibly unneeded instructions follow
-- Open query in design mode
-- Select View: SQL from the menu
-- Copy the SQL statement
-- paste it into the message

 

[Douglas J. Steele]

While I'm not positive, I believe it may be necessary to put square brackets
around the field names that have special characters in them:

SELECT P.ingPtID, strLastName, strFirstName, strFirstSeen,
dtmVisitDate, [sng#MissFng], [sng#MissToe], ysnLimbLoss
FROM tPtData as P INNER JOIN tVisit as V
ON P.ingPtID = v.ingPtID
WHERE P.strFirstSeen = v.dtmVisitDate

Far better, of course, would be not to use the special characters in the
field names...

 

[John Spencer]

Yep, good catch. I always try to remember to do that even though in some cases
they are not needed.

While I'm not positive, I believe it may be necessary to put square brackets
around the field names that have special characters in them:

SELECT P.ingPtID, strLastName, strFirstName, strFirstSeen,
dtmVisitDate, [sng#MissFng], [sng#MissToe], ysnLimbLoss
FROM tPtData as P INNER JOIN tVisit as V
ON P.ingPtID = v.ingPtID
WHERE P.strFirstSeen = v.dtmVisitDate

Far better, of course, would be not to use the special characters in the
field names...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Since you didn't post your SQL statement and didn't post the relating
fields the
following is a guess.


SELECT P.ingPtID, strLastName, strFirstName, strFirstSeen,
dtmVisitDate, sng#MissFng, sng#MissToe, ysnLimbLoss
FROM tPtData as P INNER JOIN tVisit as V
ON P.ingPtID = v.ingPtID
WHERE P.strFirstSeen = v.dtmVisitDate


This assumes that strFirstSeen is a DATE FIELD as is dtmVisitDate and it
also
assumes that both tables have a PtID field for the relationship between
the two tables.

IF the assumptions are incorrect, you might want to post back with more
detail
and a copy of the query that is not working.

Possibly unneeded instructions follow
-- Open query in design mode
-- Select View: SQL from the menu
-- Copy the SQL statement
-- paste it into the message