Select Unique records by a single field

[jh88mb30]

Hi all,

First time posting here. I hope to be as detailed as possible. I'm still learning MS Access (using 2007 right now) and I am stuck trying to do a task and need some guidance.

I have a table that has the following fields
1. ID (primary key)
2. Patient ID
3. Encounter ID
4. Date
5. Procedure Code

What I am trying to do is to create a query that shows me all the unique patients in this table.

The scenario in this table is that, there are patients who have had multiple encounters on the same/different dates with same/different procedure codes.

I tried using the totals query but it's not giving me the results I am trying to get. I tried playing around with the unique values/records with no luck.

Please assist! Thank you.

[zmbd]

Welcome to BYTES!
Rest assured that someone here will be able to push you in the right direction.

Sorry that you're in 2007 ( evil laugh )

Be aware that in the future we'll want you to show your work first. Please remember to use the <CODE/> formating button when posting VBA/HTML/XML. Also, The way you worded the question is fairly close to the rules. (FAQ)

The totals query is where you need to go..
If you just need a list of the patients then open a query, show the table, drag just the [Patient ID] down to the grid, click on the totals icon in the ribbon. In the grid, in the total row, under the [Patient ID] select groupby.

This will return only the [Patient ID] values, one per unique value.

Some free advise:
Google a list of reserverd words for VBA and SQL. Do not use these as names for fields or variables ( i.e. Date, Month, etc..)
Try not to use anything except alphanumeric and the underscore in your variable and field names. That means no spaces ( [Patient ID] prefer [PatientID] or [Patient_ID] )
Using other charaters and spaces can cause you issues in the future with SQL and with VBA.
Normalize your database... articles here about this under the insights tab.
Go here and read thru:http://www.applecore99.com/tut/tutindex.asp

[jh88mb30]

Thanks zmbd for the response.

Yes, the names of the fields are not the ones I posted. Thanks for reminding though. It's been a some time since I have taken DB courses.

I have a followup question...
I want to filter this table by procedure code and only unique patients so that I can see how many unique patients had a certain type of procedure done.

Do I have to run a query for unique patients first, and then run a query to find the procedure code? Or is there a way to this all in one shot?

Thanks.

[jh88mb30]

zmbd,

I want to see all the other fields too and not just the unique patient ID. It's kind of tricky with the other fields because there could be same dates and same procedure codes. I'm not sure what to group them by.

[zmbd]

The orginal question was to show a unique list of patients.
Has that been answered?

Follow-up questions need to be asked in a new thread... sorry, guidlines for the site. :)

[TheSmileyCoder]

All versions of access from at least 2000 (Sorry I don't go further back :P) have a query wizard to help you setup a query to find duplicates.

To just get a list of unique patient ID's from the table, you can either manually edit the SQL to add a DISTINCT keyword after select like so:

SELECT DISTINCT PatientID from TableName

The same will happen if you edit the query properties and set the Unique Values to true.

Be aware that if you want unique results in this case you can only include the PatientID in the select, not other fields.


Other options again could be do use the aggregate query, where you group by the PatientID and count the number of encouters if that is your desire. As long as the only field you group by is the patientID you should get unique values returned for that field.

[TheSmileyCoder]

Also a welcome to Bytes from me.

As zmbd mentioned new question=new thread. It makes it easier for search engines and thus other Access developers to find the correct thread. If required you can post a link back to this thread, but if possible try to keep thread separated.

I find it nice that you state that your new to access. Its always nice for us to know what the starting point is. No point in starting with the bees if you already have babies, and no point explaining details of babies if you don't yet know about the bees. Letting us know is a great step in allowing us to provide the best possible feedback.

EDIT: I have changed the title of your thread to what I believe is more appropriate.