Date queries

W

woods1119

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

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
 
D

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

John Spencer

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

Douglas J. Steele said:
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!)

John Spencer said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top