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

View Replies


ADVERTISEMENT

Queries :: Hiding Records With Identical Address Details

Sep 11, 2013

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.

View 14 Replies View Related

Queries :: Mass Duplicate Main Records And Related Subform Records

May 29, 2014

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.

View 5 Replies View Related

Queries :: Selecting Most Current Record For Item That Has Multiple Records In A Query

Oct 31, 2014

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?

View 11 Replies View Related

Duplicate Records In Queries

May 10, 2006

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)

View 1 Replies View Related

Queries :: No Duplicate Records

Oct 25, 2014

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

View 14 Replies View Related

Queries :: Duplicate Records Do Not Show

Feb 20, 2014

How can I have this query to show no duplicate records.

I would like to only see the first record only of each stock code.

View 2 Replies View Related

Queries :: Removing Duplicate Records

Jul 23, 2015

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

View 2 Replies View Related

Queries :: Duplicate Records Show Only The Last One

Jun 24, 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.

View 3 Replies View Related

Queries :: Updating First Row Of Duplicate Records

Jan 30, 2014

I have two tables -say Table A and Table B.

Table A Table B

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:

Table A

ID userno ticketno Status

1 1211 YAB LIVE
2 1211 YAB
3 1232 ACE LIVE
4 1232 ACE
5 1232 ACE
6 1232 AAT INVALID

How to write the query.

View 3 Replies View Related

Retriving Duplicate Records With Multiple Queries

Aug 18, 2005

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

Thanks

View 1 Replies View Related

Queries :: Removing Duplicate Records In Query

Apr 24, 2014

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.

View 8 Replies View Related

Queries :: Remove Duplicate Records From Table

Jun 12, 2014

I have a table which has duplicate records so I want to write down the query that will remove the duplicate records from the table.

View 1 Replies View Related

Queries :: Duplicate Results For Some Of Records In A Query

Dec 3, 2013

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],

[Code] .....

View 1 Replies View Related

Queries :: How To Remove Duplicate Records From A Query

Feb 11, 2014

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.

View 3 Replies View Related

Duplicate Form After Selecting Value From Combobox

Jul 7, 2012

I'm pretty new to Access 2007. I have a form and in it there are data entry records including a combobox.

I'd like to select a value from the combobox, say "1" and when I do I would like all the information on that form to be duplicated in a new form.

I'd also like a message to pop up saying that info has been duplicated and that the user must update some of the fields.

View 1 Replies View Related

Queries :: Find Duplicate Records In Table With Two Fields

Aug 29, 2013

I want query to find duplicate records, i have two field in one table

Cusip and category

cusip and category are many or duplicates

but in one cusip category should be the same if not then provide the cusip which has different category used

like this
CusipCategory
123R
456P
123R
456P
678Q
678Q
123A

result should be

CusipCategory
123R
123R
123A

View 6 Replies View Related

Queries :: Insert Into Query - Duplicate Records In A Subform To New Form

Jun 4, 2013

I'm trying to duplicate the records in a subform to a new form but keep getting a too few parameters error.

Code:
strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
"SELECT " & lngID & " As NewOrderID, ProductID, Quantity, DiscountPercentage " & _
"FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"

The debug.print comes out as below:

INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) SELECT 49 As NewOrderID, ProductID, Quantity, DiscountPercentage FROM [OrderDetailT] WHERE OrderT!OrderNumber = 11;

View 4 Replies View Related

Queries :: Get Rid Of Duplicate Records That Show Different Data In Multiple Columns

Sep 8, 2013

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:

Quote:

SELECT [Employee #], ConcatRelated("[Courses]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Start Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl]
SELECT [Employee #], ConcatRelated("[Completion Date]", "[Courses tbl]", "[Employee #] =" & [Employee #]) from [Employee Tbl];

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, _

[code]....

View 4 Replies View Related

Queries :: SELECT DISTINCT Not Removing Duplicate Records In Staging Table

Jan 29, 2015

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?

View 14 Replies View Related

Queries :: Table With Duplicate Records - Return Distinct Data For Each Client

Jan 17, 2014

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?

View 5 Replies View Related

Setting Up Basic Hospital Record System - Linking Forms With Patient ID Or Name?

Aug 21, 2015

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.

Database draft.zip

View 6 Replies View Related

Forms :: Count Age From DOB To Admission Date

May 12, 2013

How to count the age from DOB to Admission date.

Please see the attachment and if possible add your formula.

View 5 Replies View Related

Queries :: One To Many Relationship Between Tables Containing Membership Details

Nov 25, 2014

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.

View 2 Replies View Related

Queries :: Show Unfiltered Details Of Field

Oct 25, 2014

The programmer who we had used is not available and I would like to see the Unfiltered Details of the field

Code was : =DSum("Deposit_Amount","c_Deposit_Slip_Lines","Can celed=False and c_Deposit_Slip_Header_ID=" & [ID])

I want to see the Deposit amount unfiltered

i.e.: Deposit amount $1,234.56

View 2 Replies View Related

Queries :: Auto Filling User Details

Nov 4, 2013

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.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved