Solved: Join help

[calenger]

Hi all

Thanks for reading

I am stuck. I have 2 table in access 2000. the first is demographics for patients and the second is a list of medications they recieved. I need to create a query that will show specific patients from the demographic table (based on criteria) with only the medication recieved after the arrival time in the Demographics table. This is working.

What I am having a problem with is in joiniing the second table. once i create a join it shows me multiple instances of the patients for each mediacation recieved. I have tried using the unique property in the proprety sheet of the query. No luck. still getting multiples. Also have tried first of. somwhat worked but because the first med can be before arrival I can't use it.

Any ideas?

[xCav8r]

calenger, :hi:

I don't think I understand the specifics well enough to offer any advice. Could you, perhaps, rephrase? And how about a non-confidential attachment for us to work with? :)

[chocobochick]

Offhand, without being able to see for myself, it almost sounds like you have no relationship between the two tables. In Access, designing a query with two tables but forgetting to join your related fields will often produce this kind of result.

Do both these two tables have a common field that uniquely identifies the customer (like an account number)? In the design view of your Access query, there should be a line that "connects the dots" between the two table windows in the upper half of your screen. If there is no line, then drag and drop one of the field names onto the corresponding field name on the other table.

If that isn't the problem, then we'll either need an example attachment or more detailed information of how you tried to put this query together, step by step.

[calenger]

Hi Again

I attached a new db with the 2 tables.

I need a query that shows each record in the PN Mem Core Pre and the first instance of an antibiotic (from PN Mem Anti pre) that is after the arrival date and time in the PN Mem core Pre.

Hope I am making sense.

[xCav8r]

*cough* *cough*

[calenger]

Try again

[calenger]

Hows the dog?

[calenger]

Hi

Just got back from lunch and relized I forgot to add the join in the post ver. The medrecnum are the join fields.

[xCav8r]

Not all the patients have arrival dates and times.

[calenger]

Hi

yes sorry! All except where arrdate or arrtime is not null

[xCav8r]

101 records corresponding to 101 distinct MedRecNum's in [PN Mem Anti Pre]. I included the arrival date and time for reference. Insert SQL below into a new query in Access.


SELECT [PN Mem Anti Pre].PatientID, Min([AntiDate] & " " & [antitime]) AS AntiDateTime,
[arrdate] & " " & [arrtime] AS ArrDateTime
FROM [PN Mem Core Pre] INNER JOIN [PN Mem Anti Pre] ON [PN Mem Core Pre].MedRecNum =
[PN Mem Anti Pre].MedRecNum
GROUP BY [PN Mem Anti Pre].MedRecNum, [PN Mem Anti Pre].PatientID, [arrdate] & " " & [arrtime]
ORDER BY [PN Mem Anti Pre].PatientID, Min([AntiDate] & " " & [antitime]);

[calenger]

Thanks

[chocobochick]

The above example only selects the earliest date/time value from the Anti table, regardless of the Arrival date. If you look at PatientID 33041, you'll see that the Anti entry was recorded about 6 hours before the Arrival time. If you want to fix this, or if you wish to include additional information from the Anti table based on the earliest applicable record, you'll probably need to establish this as criteria in a WHERE clause.

Attached is an example of my approach to the problem. I made two "wrapper queries" to make virtual tables with the date and time compiled into a single field. Then I used the DMin function as the criteria for the new AntiDateTime field, specifying operations to MedRecNum and ArrDateTime as criteria within the function to limit the records properly and return the correct result. This way, we can pull additional information such as AntiName as it directly relates to that earliest applicable Anti table entry.

[xCav8r]

Yup, I took the lazy approach. Good catch. Dmin is the approach he wants, but maybe my laziness will help him locate records with bad data. Patient 33041 only has one record in the Core Pre table. ;)