Assistance with selecting first visit record of patients

Hi all

I saw a similar query on another thread but was unable to use the answers to resolve my problem.

I have an access database with two tables - the first contains demographic data for patients (Initial visit table) and the second (followup visit) contains all visit records for these patients linked by a PID. Some of the data was entered retrospectively so the record number is not a reflection of date of visit. Have 25000 visits captured.

I need to devise a query to extract the first CD4 count (a blood result) available for each patient - cd4 not done on every visit. Have tried using:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit]b

where a.InitialVisitID = b.InitialVisitID

order by VisitDatetime desc)));

This initially generates a table with duplicates followed shortly thereafter by a warning that only one record can be returned by the subquery and then blanking out of all records in the query output.

Please help!

I believe you want something like this:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM FollowUpVisit a

inner join

(select Min(InitialVisitID) as InitialVisitID, VisitDatetime, CD4CountPercentage

from FollowUpVisit

group by InitialVisitID

) as b

on a.InitialVisitID = b.InitialVisitID

order by a.VisitDatetime desc

|||

Try:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit] b

where b.PID = a.PID

order by VisitDatetime desc)));

AMB

|||

Thanks for the quick response AMB

Your suggestion has the same result - except asks for the PID paramter to be entered.

Just for clarification InitialVisitID in this database is in fact the PID. So i tried it with changing PID to InitialVisitID but same outcome.

h

|||

Thanks for the response

For clarification the "InitialVisitId" is the PID - so selecting for min InitialVisitID does not work as every patient only has one InitialVisitID (is unique identifier and primary key). Your solution also gives a circular reference problem within the Selection list. Tried making it:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage
FROM FollowUpVisit a
inner join
(select Min(VisitDateTime) as VisitDateTime, InitialVisitID, CD4CountPercentage
from FollowUpVisit
group by InitialVisitID
) as b
on a.InitialVisitID = b.InitialVisitID
order by a.VisitDatetime desc

but got same problem

h

|||

Please, do not make us to guess your enviroment. Post some DDL, including constraints and indexes, sample data and expected result.

Can you post the error msg you are getting?. I do not think that (select top 1 c1, ..., cn from ... order by) can bring more than one row, without using keywords WITH TIES.

AMB

|||

I am confused by this table FollowUpVisit. If this was a Visit table, I might understand, but is the InitialVisit stored in the FollowUpVisit table?

And this seems to imply that you can have >1 InitialVisit?

In your query, be sure and use aliases for every column, just to be careful with the output:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 b.InitialVisitID from [FollowUpVisit] b

where a.InitialVisitID = b.InitialVisitID

order by b.VisitDatetime desc)));

I agree with Hunchback, in that I don't know how that top 1 query can return > 1 row.

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
Anith
|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
--
Anith|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
--
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

ASPNETDB - Unable to connect to SQL Server database

Hi Eevryone:

I seem to have corrupted my ASP.netWeb Site Administration Tool some how. It worked fine a few days ago, but reloaded SQL Server 2005 Express Edition W Adv Services SP1 yesterday I can not get past the "Unable to connect to SQL Server database" error. I have run the aspnetdb.exe and created both a aspnetdb.mdf and loaded the tables to into a existing mdf. I have checked all the permissions.

So any ideas on what I am doing wrong, or can you point me to some documentation on the 'AspNetSqlProvider'

Thanks in advance, Gene

You could erase the file in the appdata directory and it should recreate one fromscratch for you. actually, all you need is

<roleManager enabled="true" />

to get it to make the appdata express file

|||Exactly were do I am this entry?|||

Here is a very simple web.config with rolemanager enabled.

<?xml version="1.0"?>
<!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
\Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="FetchEmailConnectionString" connectionString="Data Source=TM8200;Initial Catalog=FetchEmail;Integrated Security=True"
providerName="System.Data.SqlClient" />
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<roleManager enabled="true"></roleManager>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
-->
<compilation debug="true"/>
<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
<authentication mode="Windows"/>
<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>

|||

Thanks again for the help.

Your sample web.config helped prove what I was beginning to suspect yesterday. It is the creation of user instances under .SQLExpress that is causing my problems. This makes sense considering that it was after I switched to .SQLExpress from SQL 2000 that my problems started. Any suggestions on where to find good user instance documenation? So far I have not found any good trouble shooting articles.

Cheers, Gene

ASP.net version

I have been running SSRS in VB 2003 but recently I installed VS 2005 beta1
and now I am unable to access the report server. I noticed that I now have
asp.net version 2 instead of version 1. I think this may be the problem.
In VS 2005 I do not use the web part of the program at all so I think I may
be able to delete version 2 from IIS and install version 1 again and that
this may solve my problem. But before I do I just thought I should ask here
if someone else sees a problem. I guess the worst that could happen is that
it would continue not to work.
Anyone?Both the ReportServer and Reports virtuals roots need to be mapped to
ASP.Net 1.1.4322. You can verify this in the properties for each virtual
root on the ASP.Net tab.
What error message do you get when you access report server? Installing a
new version of the framework will reset the password on the ASPNet account,
removing all encryption keys from the account. You may simply need to run
rsactivate -r -c <config file>
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Woody Splawn" <nospam@.splawns.com> wrote in message
news:Os60WfWgEHA.632@.TK2MSFTNGP12.phx.gbl...
> I have been running SSRS in VB 2003 but recently I installed VS 2005 beta1
> and now I am unable to access the report server. I noticed that I now
have
> asp.net version 2 instead of version 1. I think this may be the problem.
> In VS 2005 I do not use the web part of the program at all so I think I
may
> be able to delete version 2 from IIS and install version 1 again and that
> this may solve my problem. But before I do I just thought I should ask
here
> if someone else sees a problem. I guess the worst that could happen is
that
> it would continue not to work.
> Anyone?
>|||To remap the vroots back to 1.1.4322 use the aspnet_regiis.exe utility found
in C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message
news:OmNAryWgEHA.3728@.TK2MSFTNGP10.phx.gbl...
> Both the ReportServer and Reports virtuals roots need to be mapped to
> ASP.Net 1.1.4322. You can verify this in the properties for each virtual
> root on the ASP.Net tab.
>
> What error message do you get when you access report server? Installing a
> new version of the framework will reset the password on the ASPNet
account,
> removing all encryption keys from the account. You may simply need to run
> rsactivate -r -c <config file>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Woody Splawn" <nospam@.splawns.com> wrote in message
> news:Os60WfWgEHA.632@.TK2MSFTNGP12.phx.gbl...
> > I have been running SSRS in VB 2003 but recently I installed VS 2005
beta1
> > and now I am unable to access the report server. I noticed that I now
> have
> > asp.net version 2 instead of version 1. I think this may be the
problem.
> >
> > In VS 2005 I do not use the web part of the program at all so I think I
> may
> > be able to delete version 2 from IIS and install version 1 again and
that
> > this may solve my problem. But before I do I just thought I should ask
> here
> > if someone else sees a problem. I guess the worst that could happen is
> that
> > it would continue not to work.
> >
> > Anyone?
> >
> >
>|||Using the aspnet_regiis utility will definitely cause you to lose the
encryption keys because it resets the ASPNet account password. You are
better off using the IIS virtual root properties page.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:utbIkAXgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> To remap the vroots back to 1.1.4322 use the aspnet_regiis.exe utility
found
> in C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message
> news:OmNAryWgEHA.3728@.TK2MSFTNGP10.phx.gbl...
> > Both the ReportServer and Reports virtuals roots need to be mapped to
> > ASP.Net 1.1.4322. You can verify this in the properties for each
virtual
> > root on the ASP.Net tab.
> >
> >
> > What error message do you get when you access report server? Installing
a
> > new version of the framework will reset the password on the ASPNet
> account,
> > removing all encryption keys from the account. You may simply need to
run
> > rsactivate -r -c <config file>
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights
> >
> > "Woody Splawn" <nospam@.splawns.com> wrote in message
> > news:Os60WfWgEHA.632@.TK2MSFTNGP12.phx.gbl...
> > > I have been running SSRS in VB 2003 but recently I installed VS 2005
> beta1
> > > and now I am unable to access the report server. I noticed that I now
> > have
> > > asp.net version 2 instead of version 1. I think this may be the
> problem.
> > >
> > > In VS 2005 I do not use the web part of the program at all so I think
I
> > may
> > > be able to delete version 2 from IIS and install version 1 again and
> that
> > > this may solve my problem. But before I do I just thought I should
ask
> > here
> > > if someone else sees a problem. I guess the worst that could happen
is
> > that
> > > it would continue not to work.
> > >
> > > Anyone?
> > >
> > >
> >
> >
>|||> What error message do you get when you access report server?
I am getting the following message:
The report server cannot decrypt the symmetric key used to access sensitive
or encrypted data in a report server database. You must either restore a
backup key or delete all encrypted content and then restart the service.
Check the documentation for more information. (rsReportServerDisabled)
>You may simply need to run rsactivate -r -c <config file>
If I do, what is the exact syntax?
If I understand correctly I move to my MS SQL Server\80\tools\binn directory
to run rsactivate. What is the name of the config file? I have a file
called cnfgsvr.ini, if that helps. What exactly do I do?
In the help on line I got the following:
"A system administrator on the computer running the Report server service
can use a backup copy of the public key to get the report server running
again. Use the rskeymgmt utility to apply the backup key set. If you do not
have backup of the key, you must run rskeymgmt utility to delete all of the
encrypted content, run rsconfig utility to respecify the encrypted
connection values used to connect to the report server database, restart the
service, and retype stored credentials for all reports that use stored
credentials. For more information this utility, search Books Online."
However, I do not understand exactly what they're saying.
"Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message
news:OmNAryWgEHA.3728@.TK2MSFTNGP10.phx.gbl...
> Both the ReportServer and Reports virtuals roots need to be mapped to
> ASP.Net 1.1.4322. You can verify this in the properties for each virtual
> root on the ASP.Net tab.
>
> What error message do you get when you access report server? Installing a
> new version of the framework will reset the password on the ASPNet
account,
> removing all encryption keys from the account. You may simply need to run
> rsactivate -r -c <config file>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Woody Splawn" <nospam@.splawns.com> wrote in message
> news:Os60WfWgEHA.632@.TK2MSFTNGP12.phx.gbl...
> > I have been running SSRS in VB 2003 but recently I installed VS 2005
beta1
> > and now I am unable to access the report server. I noticed that I now
> have
> > asp.net version 2 instead of version 1. I think this may be the
problem.
> >
> > In VS 2005 I do not use the web part of the program at all so I think I
> may
> > be able to delete version 2 from IIS and install version 1 again and
that
> > this may solve my problem. But before I do I just thought I should ask
> here
> > if someone else sees a problem. I guess the worst that could happen is
> that
> > it would continue not to work.
> >
> > Anyone?
> >
> >
>|||P.S.
I do keep a backup of my entire system. I'm sure I have a backup that
preceeds the install of VS 2005 beta1. If it is just a matter of copying a
certain file back into a certain directory I can easily do that, but what
file and what directory?|||Woody,
Check this thread and see if it will help.
http://tinyurl.com/6fyb7
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Woody Splawn" <nospam@.splawns.com> wrote in message
news:OSyDmjXgEHA.3348@.TK2MSFTNGP12.phx.gbl...
> P.S.
> I do keep a backup of my entire system. I'm sure I have a backup that
> preceeds the install of VS 2005 beta1. If it is just a matter of copying
a
> certain file back into a certain directory I can easily do that, but what
> file and what directory?
>
>|||Your ASPNet account password was reset. The database is intact, and that
backup won't help you. As a side note, you may want to check out the topics
relating to backing up your report server in books online. You certainly
want to back up the encryption key.
The data that is missing was in the ASPNet user profile. Running
"rsactivate -r -c <config file>" will fix the problem. The config file you
are looking for is called rsreportserver.config. You should specify the
full path to the file.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Woody Splawn" <nospam@.splawns.com> wrote in message
news:OSyDmjXgEHA.3348@.TK2MSFTNGP12.phx.gbl...
> P.S.
> I do keep a backup of my entire system. I'm sure I have a backup that
> preceeds the install of VS 2005 beta1. If it is just a matter of copying
a
> certain file back into a certain directory I can easily do that, but what
> file and what directory?
>
>