Queries :: Admission Details Of Patients Admitted To Hospital - Selecting Duplicate Records
Feb 3, 2014
I'll first of all explain the purpose of the query. I've built a database to record the admission details of patients admitted to hospital. Amongst other things, the database captures date/time of admission and date/time of discharge.
The query I am building needs to show patients with multiple admissions, and in particular, the number of days that have elapsed since they were last discharged. Within my query is the following subquery that I had written to show this aspect of it:
(SELECT TOP 1 Dupe.[Discharge Date]
FROM [Inpatient Database] AS Dupe
WHERE Dupe.[CHI Number] = [Inpatient Database].[CHI Number]
AND Dupe.[Discharge Date] <= [Inpatient Database].[Admission Date]) AS [Previous Discharge],
Note: "CHI Number" is a unique reference number assigned to every patient.
I then display in a column [Admission Date] - [Previous Discharge].
This works fine where a patient only has one previous admission. However, where a patient has multiple admission, the subquery always returns that patient's first discharge date instead of their most recent discharge date (because I have used the "TOP 1" predicate). It seems that it should be straightforward enough, however I can't seem to work around it. Anything I try results in errors, and so I keep defaulting back to my "TOP 1" solution.
i.e. What I want it to show is:
Name.......Admission Date...Discharge Date.... Previous Discharge... Days Between Admissions
Joe Bloggs ..01/01/2014 .......10/01/2014
Joe Bloggs ..15/01/2014 .......20/01/2014 .......10/01/2014 ..............5
Joe Bloggs ..27/01/2014 .......01/02/2014 ........20/01/2014 .............7
However, what it actually shows is:
Name ......Admission Date ...Discharge Date ...Previous Discharge ...Days Between Admissions
Joe Bloggs ..01/01/2014 ......10/01/2014
Joe Bloggs ..15/01/2014 ......20/01/2014 ........10/01/2014 .............5
Joe Bloggs ..27/01/2014 ......01/02/2014 ........10/01/2014 .............17
I have written a simple query for address label printing purposes, but I do not want to print labels to identical postal addresses.This sounds such a simple requirement but my efforts to write a simple 'criteria' in the Address1 Field have been unsuccessful.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.
LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)
I have been hitting my head against the wall to have my query not show any DUPLICATE STOCK CODES in my report..I have two "AQN.PR.A" records, I only want my report to print one "AQN.PR.A"...I have three "ALA" records, I only want my report to print one "ALA"AQN.PR.A
I'm having a problem removing duplicate records from a table. It should be easy but I can't suss it.This is an example of the data in my table:
Code:
RefDateStatus 113007111/06/2015Do Not Pay 114454306/07/2015Do Not Pay 115760714/05/2015Do Not Pay 116520705/05/2015Do Not Pay 117670108/05/2015Do Not Pay 118036218/05/2015Do Not Pay 118517015/05/2015Do Not Pay 178734020/07/2015Do Not Pay 182809915/07/2015Do Not Pay 184226010/07/2015Payment Due 184226022/07/2015Payment Query
As you can see, there are 11 records here but the last two records have the same reference number. I need my query to show the first 9 records + the record from the bottom two with the latest date (22/07/2015).
I have a table and there are so called duplicate records (not entirely duplicate because there is field with the date of creation and other differences in a few fields, but from my point of view they are duplicates.). I d like to make a query which shows me only the newest record in case of duplicity.
ID userno ticketno Status userno ticketno Status 1 1211 YAB 1211 YAB LIVE 2 1211 YAB 1232 ACE LIVE 3 1232 ACE 1232 AAT INVALID 4 1232 ACE 5 1232 ACE 6 1232 AAT
I am trying to write an update query in MS Access that will update only the first record of any custno with the status so that my final table A looks like this:
when running the attached report I am getting duplicate records. If using the protocol specified tc02026 there should be 8 records total. When running the report it pulls from both queries which have select criteria. How do I limit the report to only give me the 8 records and eliminate the duplicates? When I run each query it only gives me the 8 records I am looking for. Each Sample# in the report is a unique number(to help see the replicates).
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is: tblMEMR Movement requests details tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request. tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
Why I am getting duplicate results for some of my records in a query. I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. SQL is below.
SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number], [tbl_Rewards Activity Report - By Member Number].[Last Four], [tbl_Rewards Activity Report - By Member Number].[BAL ID], [tbl_Rewards Activity Report - By Member Number].[Primary Name],
I have this small database, I would like to have your support to setup this query "QryResults" in order to remove the duplicate records, I can't find a way to get shown only true records, for some reason I'm getting duplicate rows and fake values, the query is calculating operations from two different queries and a table.
I have 10 tables linked in one query. 9 tables are linked to one main table (one to many relationship).I want to get rid of duplicate records that show different data in multiple columns. I want only one record of this but retain with different data under different columns to be separated by commas. For example: I want this...
Code: Employee # Name Course Start Date Completion Date 1 John Smith MS Office Training 1/1/2010 5/1/2010 1 John Smith Python Training 1/30/2011 4/1/2011 1 John Smith Leadership Development 6/27/2013 9/1/2013 1 John Smith Sensitivity Training 9/5/2010 - 2 Hank Joel MS Office Training 8/1/2010 10/1/2010 2 Hank Joel Sensitivity Training 8/1/2010 10/1/2010 2 Hank Joel WHMIS Training 11/15/2010 12/1/2010 3 Jane Doe Leadership Training 7/18/2011 9/26/2012
To turn like this:
Code: Employee # Name Course Start Date Completion Date 1 John Smith MS Office Training, Python Training, Leadership Development, Sensitivity Training 1/1/2010, 1/30/2011, 6/27/2013, 9/5/2010 5/1/2010, 4/1/2011, 9/1/2013, - 2 Hank Joel MS Office Training, Sensitivity Training, WHMIS Training 8/1/2010, 8/1/2010, 11/15/2010 10/1/2010, 10/1/2010, 12/1/2010 3 Jane Doe Leadership Training 7/18/2011 9/26/2012
I am using two tables to find the data (main - "employee tbl" and "courses tbl")I have been trying to follow Allen Browne`s method, but I`m unsuccessful. This is the code I've put in SQL of this query:
However, I am prompt with "Syntax error in query expression".It also prompts another expression to be in error when I include the above but it runs okay when I don't do the above:
Quote:
WHERE (([Employee Tbl].[Employee #]) Like [Enter Employee ID or leave blank for ALL employees] & "*")
I placed the following in VBA module:
Code: Public Function ConcatRelated(strField As String, _ strTable As String, _ Optional strWhere As String, _ Optional strOrderBy As String, _
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code: Public lngNewBalancesTBI As Long ' Defined in a separate module... ------- Dim dbs As Database Dim rst As Recordset Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
I work for a charity in the Pacific. I am trying to develop a very basic patient electronic health record for a new hospital, to be used until we get a more comprehensive patient management system in place.
I have set up basic tables, forms, and a welcome screen.
The idea is that the doctor or nurse will start at the welcome screen. From there, they can either enter a new patient, or add a new patient encounter.
There are four types of patient encounters: assessment, treatment/surgery, refraction, and follow-up. I have made tables and forms for these encounters.
My issue is finding an easy way for the doctor or nurse to quickly and easily make sure that the encounter form they are filling relates to the appropriate patient. I already have a patient form, and patient ID field across the tables. But I would like to be able to add a 'search for patient' button on each of the encounter forms (or any other relatively easy method) to make sure everything lines up.
I have a table with name, club members details ID etc. This is linked by a 1 to many link (ID) to a table containing details of membership subscription payments. One entry/row per membership period. This second table has DatePaid, Paid (Yes/No), Period. Period contains 2013-14, 2014-15 etc.
I can do a query for those that have Paid (Yes) but when I try one for those that have not Paid (No) or <>Yes I get no result. I only enter members when they have paid. I need a query to display those who have not paid for the 2014-15 period.
I can do it a long winded way copying 2 lists into Excel. One all members. One those who have paid. Then remove duplicates and those paid in 2013-14 leaves those not paid in 2014-15.
I have been managing a database system for a homeless shelter in my volunteer role. There is a certain feature that would improve accuracy of the inputted records, as well as freeing up time for staff. That is - auto filling a user's Gender and Nationality when inputting a record for the drop-in centre.
Currently, when a forename and surname is inputted in the "DropIn_Input" form, it is checked against the stored 'service users' table and checks for the name combination. If the name combination is not present then the forename and surname fields change to 'New'.
What I would like is to keep this, but if the service user name is stored in the 'Service User's table then I would like their Gender and Nationality to be automatically filled into the fields on the 'DropIn_Input' from the relevant information in the 'Service Users' table.