Hello,
I have a query listed below which works but...
The data below is what the query displays. There are hundreds of records I am bringing up and each one has between 3-6 entries displayed in this general format. The LastPFProtocolS tageIndex (level reached) column will always reflect the same number for the patient. Based on the number in the first column I would like the query to display the first duplicate occurance and nothing more.
The overall project is to have a printout of this data to be turned in at the end of each month for clinic stats. Right now I take this nice long list, put it through some excel paces and in a hour or so I can have what I want.
My initial question was if anybody could tell me how to structure the query to display only the first entry of the duplicate series, but if anybody wants to go a bit farther and point out any possibilies for adding a count for the various columns (FVL, DLCO, Pleth) for each that is listed true...
Thank you!
level reached Last Name Date FVL DLCO Pleth Protocol
6 Doe 09/02/08 09:05 AM TRUE FALSE FALSE Pre
6 Doe 09/02/08 09:05 AM TRUE FALSE FALSE Pre
6 Doe 09/02/08 09:05 AM TRUE FALSE FALSE Pre
SELECT PatVisit.LastPF ProtocolStageIn dex, Patient.Patient LastName AS LastName, PatVisit.VisitD ateTime, PatVisit.FVLTes t AS FVL,
PatVisit.DLCOTe st AS DLCO, PatVisit.PlethT est AS PLETH, PFProtocolStage .PFProtocolStag eLabel AS ProtocolState3
FROM PatVisit INNER JOIN
Patient ON PatVisit.Patien tGUID = Patient.Patient GUID INNER JOIN
PFProtocol ON PatVisit.PFProt ocolGUID = PFProtocol.PFPr otocolGUID INNER JOIN
PFProtocolStage ON PFProtocol.PFPr otocolGUID = PFProtocolStage .PFProtocolGUID
ORDER BY PatVisit.VisitD ateTime DESC
3 2804 Delerna 1,134
Recognized Expert Top Contributor
Not sure if this is what you are after but with a bit of experimentation and/or more questions, we should be able to arrive at what you are after. -
SELECT a.LastPFProtocolStageIndex,
-
b.PatientLastName AS LastName,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel AS ProtocolState3,
-
count(a.LastPFProtocolStageIndex) as NumDups
-
FROM PatVisit a
-
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID
-
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID
-
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
-
GROUP BY a.LastPFProtocolStageIndex,
-
b.PatientLastName ,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel
-
ORDER BY a.VisitDateTime DESC
-
and if you only want a list of the duplicate records then something like this -
SELECT a.LastPFProtocolStageIndex,
-
b.PatientLastName AS LastName,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel AS ProtocolState3,
-
count(a.LastPFProtocolStageIndex) as NumDups
-
FROM PatVisit a
-
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID
-
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID
-
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
-
GROUP BY a.LastPFProtocolStageIndex,
-
b.PatientLastName ,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel
-
HAVING count(a.LastPFProtocolStageIndex)>1
-
ORDER BY a.VisitDateTime DESC
-
The only difference between the two queries is the HAVING clause (2nd last line).
I have added table aliases (a,b,c,d) only to make the code a bit more readable
in the limited width code window. You can choose to keep them or not.
The code is almost identical to yours. I have added 1 colmn in the select clause to count the duplicate rows, and a GROUP BY clause.
Not having your tables and their data I cannot test it so I hope I didn't make any syntactical errors?
That you for your suggestion. It looks like I made this a bit harder than it should have been. Since all the entries per patient were identical except the stagelabel part, I simply omitted that and now I'm down to only one row per patient. I have included a few rows of the result below (no info except for last partial names)
The system I am working on is a patient database terminal which has no network access. So getting access to the patient data would not be the best thing :-) -
SELECT PatVisit.LastPFProtocolStageIndex,
-
Patient.PatientLastName AS LastName,
-
PatVisit.VisitDateTime,
-
PatVisit.FVLTest AS FVL,
-
-
PatVisit.DLCOTest AS DLCO,
-
PatVisit.PlethTest AS PLETH
-
FROM PatVisit
-
INNER JOIN Patient ON PatVisit.PatientGUID = Patient.PatientGUID
-
INNER JOIN PFProtocol ON PatVisit.PFProtocolGUID = PFProtocol.PFProtocolGUID
-
INNER JOIN PFProtocolStage ON PFProtocol.PFProtocolGUID = PFProtocolStage.PFProtocolGUID
-
-
GROUP BY PatVisit.LastPFProtocolStageIndex,
-
Patient.PatientLastName,
-
PatVisit.VisitDateTime,
-
PatVisit.FVLTest,
-
PatVisit.DLCOTest,
-
PatVisit.PlethTest
-
HAVING (PatVisit.VisitDateTime > CONVERT(DATETIME, '2008-09-01 00:00:00', 102))
-
-
ORDER BY PatVisit.VisitDateTime DESC
-
As you see I've added a limit on the date.
Now the only thing I can see from here is slight cosmetic changes. The numbers before the name will equal to
1=FVC
3=FVC+POST
5=FVC+MTC
6=FVC+MTC+POST
The way we have been doing this by hand was to have 5 columns, FVC, POST/BD,MTC,PLETH, and DLCO. An 'X' is made in each square per patient depending on what we did. Ideally that is what the stats office wants in terms of format. I don't quite know far the SQL query can be pushed as far as this kind of formatting.
Is there any way to have the actual results displayed with the text in place of the numbers? The same would apply to the numbers following the date. Preferably 1=a X and 0's would be blank.
I couldn't get the count feature to work. Don't know if my code modifications would change how the count feature is worded but that would still be a nice feature to have.
1 Ga*** 2008-10-03 09:40:04.000 1 0 0
5 Shamr*** 2008-10-03 09:17:38.000 1 0 0
5 PE*** 2008-10-03 08:27:49.000 1 0 0
1 Na*** 2008-10-02 08:55:23.000 1 1 1
6 TUR*** 2008-10-02 08:00:59.000 1 0 0
1 MEDGRAPHICS-QC 2008-10-02 07:31:38.000 0 1 0
5 GUE*** 2008-10-01 09:06:24.000 1 0 0
5 RIV*** 2008-10-01 08:45:29.000 1 0 0
5 CL*** 2008-10-01 08:11:20.000 1 0 0
1 MEDGRAPHICS-QC 2008-10-01 07:27:07.000 0 1 0
1 Jack*** 2008-09-30 09:13:25.000 1 0 1
Not sure if this is what you are after but with a bit of experimentation and/or more questions, we should be able to arrive at what you are after. -
SELECT a.LastPFProtocolStageIndex,
-
b.PatientLastName AS LastName,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel AS ProtocolState3,
-
count(a.LastPFProtocolStageIndex) as NumDups
-
FROM PatVisit a
-
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID
-
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID
-
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
-
GROUP BY a.LastPFProtocolStageIndex,
-
b.PatientLastName ,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel
-
ORDER BY a.VisitDateTime DESC
-
and if you only want a list of the duplicate records then something like this -
SELECT a.LastPFProtocolStageIndex,
-
b.PatientLastName AS LastName,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel AS ProtocolState3,
-
count(a.LastPFProtocolStageIndex) as NumDups
-
FROM PatVisit a
-
INNER JOIN Patient b ON a.PatientGUID = b.PatientGUID
-
INNER JOIN PFProtocol c ON a.PFProtocolGUID = c.PFProtocolGUID
-
INNER JOIN PFProtocolStage d ON c.PFProtocolGUID =d.PFProtocolGUID
-
GROUP BY a.LastPFProtocolStageIndex,
-
b.PatientLastName ,
-
a.VisitDateTime,
-
a.FVLTest AS FVL,
-
a.DLCOTest AS DLCO,
-
a.PlethTest AS PLETH,
-
d.PFProtocolStageLabel
-
HAVING count(a.LastPFProtocolStageIndex)>1
-
ORDER BY a.VisitDateTime DESC
-
The only difference between the two queries is the HAVING clause (2nd last line).
I have added table aliases (a,b,c,d) only to make the code a bit more readable
in the limited width code window. You can choose to keep them or not.
The code is almost identical to yours. I have added 1 colmn in the select clause to count the duplicate rows, and a GROUP BY clause.
Not having your tables and their data I cannot test it so I hope I didn't make any syntactical errors?
Delerna 1,134
Recognized Expert Top Contributor
Wasn't requesting access, just making a statement that I couldn't test for that reason. :)
To replace the numbers with the text
=============== ===========
all you need do is make this table
ID,Text
1,FVC
3,FVC+POST
5,FVC+MTC
6,FVC+MTC+POST
Now you can join PatVisit.LastPF ProtocolStageIn dex to the ID of the above and the select the Text field of above table instead of PatVisit.LastPF ProtocolStageIn dex.
With your HAVING clause
=============== ===
having is useful for filtering by aggregated fields
ie HAVING sum(field)>10 and count(OtherFiel d)>1
For the filter you are using (no agregated field) the WHERE clause is what you would use
WHERE PatVisit.VisitD ateTime > CONVERT(DATETIM E, '2008-09-01 00:00:00', 102)
For the count
==========
I don't understand why it didnt work
all you need is Count(field) in the select clause
and an appropriate GROUP BY clause.
Count(field) returns the number of rows
where all the fields in the GROUP BY clause
are exactly identical.
What was the problem?
Was there an error reported? what was it?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: GTi |
last post by:
I have a query like:
SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable"
"ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
("ProjectMembers"."ContactNDX" =...
|
by: Robert |
last post by:
I am trying to create a db for service providers by county. I'm relatively
new to db programming, but I have done quite a bit of programming ranging
from the old basic days up to doing some programming in the HotDocs
software. I've kind of accomplished my goal in access, but I'm not quite
there yet and figure I've really screwed something...
|
by: Bernard Lebel |
last post by:
Hello,
I'm stumbled at a serious problem, and quite frankly getting
desparate. This is a rather long-winded one so I'll try to get
straight to the point.
I have this Python program, that performs MySQL queries to a database.
These queries are performed at regular intervals. Basically it is
looking for fields that match certain criterias....
|
by: Dixie |
last post by:
I have asked this question before, but I could not get the suggested
solution work. So I will give more details this time.
I have an append query that adds several hundred records to a table into a
text field. Next to this text field is a separate Number (Single) field
with a unique ID number for the entry. I want each number to be 1 more...
|
by: Jimmy Stewart |
last post by:
Ok, I'm trying to write a query that is starting to wear me down.
What I'm trying to do is create a year end report that gets sent to all of
my customers who meet two criteria. One they are 'Residential' customer AND
they have had business with us during this past year.
My report is based on a query.
The query has two tables associated...
| |
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables...
|
by: jeff8676 |
last post by:
I'm very new at Access, so if this is a lame question I'm sorry.
Anyways, I'm trying to make a query that returns unique entries from a table, but when it finds two entries where everything matches except for in this case the feild "Ammount" it returns the entry unique but adds the differing ammount entries.
Bassically I'm trying to get the...
|
by: jameswilkinsonfjs |
last post by:
Hi All,
Ok I have a table - it lists items with a unique reference
code; lets say there are 4 items :
Item RefCode
1 ABC1
2 ABC2
3 ABC3
4 ABC4
|
by: escapersky |
last post by:
I have a database that i've been entering and I need to show how many unique entries I have entered prividing the date range.
The database includes date, productID, locationID, quantity etc.
What I'm trying to find out is the numbers of lines that is Unique date AND locationID. LocationID's are 4 digits with alphabets/number mixed.
I've...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |