Formatting issue

[Guest]

I have a field in my query that has a combination of letters and numbers. In
the table from which the query is derived, this field is formatted to have
all letters capitalized (>) and I have the following input mask: LA0000. In
my query, the letters aren't in caps. I'm concerned that this issue will
cascade to reports.

Please help?

 

[Carl Rapson]

I have a field in my query that has a combination of letters and numbers.
In
the table from which the query is derived, this field is formatted to have
all letters capitalized (>) and I have the following input mask: LA0000.
In
my query, the letters aren't in caps. I'm concerned that this issue will
cascade to reports.

Please help?

Can you use the UCase function in your query to force upper case?

SELECT UCase(field) ...

Then you won't have to rely on formatting within the table.

Carl Rapson

 

[Guest]

Uh oh.

Once again, my ignorance is showing.

How does one use the UCase function?

 

[Carl Rapson]

Just like I showed:

SELECT UCase([field]) AS [new name] FROM

The returned value will always be in upper case, regardless of how it's
stored in the table.

Carl Rapson

 

[John Spencer]

If you are only able to use the Design View (query grid) then

Field: NameofField: Ucase([Tablename].[FieldName])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Just like I showed:

SELECT UCase([field]) AS [new name] FROM

The returned value will always be in upper case, regardless of how it's
stored in the table.

Carl Rapson

 

[Guest]

John & Carl -
First off, thanks to both of you for your help on this issue. The field I'm
having trouble with is "MRN". The SQL is so long-winded that I'm bumfuzzled
as to where to put what to "force" into uppercase.
Any help would be greatly appreciated.
Here's my SQL for the query:

SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName, tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments, tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS [Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC , tblPatientProtocolActivity.D1Tx
DESC;


--
Thanks for your time!

If you are only able to use the Design View (query grid) then

Field: NameofField: Ucase([Tablename].[FieldName])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Just like I showed:

SELECT UCase([field]) AS [new name] FROM

The returned value will always be in upper case, regardless of how it's
stored in the table.

Carl Rapson

Uh oh.

Once again, my ignorance is showing.

How does one use the UCase function?

--
Thanks for your time!


:

I have a field in my query that has a combination of letters and
numbers.
In
the table from which the query is derived, this field is formatted to
have
all letters capitalized (>) and I have the following input mask:
LA0000.
In
my query, the letters aren't in caps. I'm concerned that this issue
will
cascade to reports.

Please help?
--
Thanks for your time!

Can you use the UCase function in your query to force upper case?

SELECT UCase(field) ...

Then you won't have to rely on formatting within the table.

Carl Rapson

 

[John Spencer]

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName

, UCase(tblPatientProtocolActivity.PtID) AS MRN

, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate
, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC , tblPatientProtocolActivity.D1Tx
DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I'm afraid that didn't work.
The table that MRN comes from has the format for that field as ">" & an
input mask of LA0000. Could the problem lie there?

Again, thanks so much.
--
Thanks for your time!

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName

, UCase(tblPatientProtocolActivity.PtID) AS MRN

, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate
, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC , tblPatientProtocolActivity.D1Tx
DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John & Carl -
First off, thanks to both of you for your help on this issue. The field
I'm
having trouble with is "MRN". The SQL is so long-winded that I'm
bumfuzzled
as to where to put what to "force" into uppercase.
Any help would be greatly appreciated.
Here's my SQL for the query:

SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName,
tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments,
tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS
[Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

 

[John Spencer]

What do you mean by that didn't work? Did you get an error? Did the data
not show up in uppercase? Did you get no data returned?

Also, I don't understand why you have called the field
tblPatientProtocolActivityPtId three times in the query and named it as
LastName, FirstName, and MRN.

The format mask in the table should not have any affect on the data
displayed by the query. And an input mask only controls input - it has
nothing to do with output of the data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm afraid that didn't work.
The table that MRN comes from has the format for that field as ">" & an
input mask of LA0000. Could the problem lie there?

Again, thanks so much.
--
Thanks for your time!

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName

, UCase(tblPatientProtocolActivity.PtID) AS MRN

, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate
, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John & Carl -
First off, thanks to both of you for your help on this issue. The field
I'm
having trouble with is "MRN". The SQL is so long-winded that I'm
bumfuzzled
as to where to put what to "force" into uppercase.
Any help would be greatly appreciated.
Here's my SQL for the query:

SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName,
tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments,
tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS
[Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

 

[Guest]

Data returned & there was no error message - the characters just didn't go to
uppercase.

I don't work in SQL, so I don't know how "tblPatientProtocolActivityPtId"
wound up showing up multiple times.

The patient info data comes from tblPatients and consists of 3 fields -
LastName, FirstName, MRN. Each patient may be enrolled on multiple protocols
(and vice versa), a many-to-many relationship. Their status for each protocol
will be different. So there's a second table - tblPatientProtocolActivity -
that uses the primary key from tblPatients and tblProtocols to keep this info
separate.

I'm sure there are MANY more elegant ways to achieve what I'm doing, but the
database is immensely useful to our workgroup and, except for this
uppercase/lowercase issue, it does everything I need except cook breakfast.

Thank you!!

--
Thanks for your time!

What do you mean by that didn't work? Did you get an error? Did the data
not show up in uppercase? Did you get no data returned?

Also, I don't understand why you have called the field
tblPatientProtocolActivityPtId three times in the query and named it as
LastName, FirstName, and MRN.

The format mask in the table should not have any affect on the data
displayed by the query. And an input mask only controls input - it has
nothing to do with output of the data.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I'm afraid that didn't work.
The table that MRN comes from has the format for that field as ">" & an
input mask of LA0000. Could the problem lie there?

Again, thanks so much.
--
Thanks for your time!

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName

, UCase(tblPatientProtocolActivity.PtID) AS MRN

, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate
, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John & Carl -
First off, thanks to both of you for your help on this issue. The field
I'm
having trouble with is "MRN". The SQL is so long-winded that I'm
bumfuzzled
as to where to put what to "force" into uppercase.
Any help would be greatly appreciated.
Here's my SQL for the query:

SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName,
tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments,
tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS
[Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

 

[John Spencer]

I am stuck since the SQl you posted would never return a the LastName or
the firstName from tblPatients.

It would return PtID three times. Two times with whatever case the
value was stored in and once in upper case.

Something is askew, but I don't know what.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Just in case it was mis-pasted, here's the SQL again. As I think about it, I
can probably account for one of the duplicates - I'm asking for LastName,
FirstName, & MRN for patients for 2 separate protocols. Beyond that, Lord
knows if you're stuck, John, I'm certainly unlikely to solve it - you've been
a huge help to me (and all of us on the board) many times.

Again, thanks for your thoughts & time.



SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName, tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments, tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS [Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC , tblPatientProtocolActivity.D1Tx
DESC;

 

[John Spencer]

Sue,
As I said earlier the SQL statement you posted will never return a last name
or a first name. It will return a column with that name, however the data
in the column will be the data that is in the PtID field. Are you sure you
are posting the correct query?

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, tblPatientProtocolActivity.PtID AS MRN
/// The above three lines will all return the same data - Pt ID///

If you are looking at the query grid (Design view) you would see the
following for the first three columns

Field: LastName: PtID
Table: tblPatientProtocolActivity

Field: FirstName: PtID
Table: tblPatientProtocolActivity

Field: MRN: PtID
Table: tblPatientProtocolActivity


SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, UCase(tblPatientProtocolActivity.PtID) AS MRN
, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity
ON tblPatients.PtID = tblPatientProtocolActivity.PtID
WHERE tblPatientProtocolActivity.PtStatusID in (3,15,16)
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC
, tblPatientProtocolActivity.[ICF date] DESC
, tblPatientProtocolActivity.D1Tx DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Just in case it was mis-pasted, here's the SQL again. As I think about it,
I
can probably account for one of the duplicates - I'm asking for LastName,
FirstName, & MRN for patients for 2 separate protocols. Beyond that, Lord
knows if you're stuck, John, I'm certainly unlikely to solve it - you've
been
a huge help to me (and all of us on the board) many times.

Again, thanks for your thoughts & time.



SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName,
tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments,
tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS
[Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

--
Thanks for your time!

I am stuck since the SQl you posted would never return a the LastName or
the firstName from tblPatients.

It would return PtID three times. Two times with whatever case the
value was stored in and once in upper case.

Something is askew, but I don't know what.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Maybe this will clear up the muddle.
In tblPatients, I have an autonumber as my primary key. Additional fields
include first name, last name, MRN, etc.
In tblProtocols, I have an autonumber as my primary key, then the protocol
name, etc.
In tblPatientProtocolActivity, I use a foreign key from tblPatients and a
foreign key from tblProtocols to "unite" a given patient with each protocol
he's screened for, enrolled in, etc. Since patients may be enrolled in
several protocols over time, and since each protocol can have many patients,
I needed to establish a way to track each patient's involvement with a
protocol separately.

In tblPatientProtocolActivity, then, I pull the patient's LastName,
FirstName, & MRN from tblPatients using the foreign key.

I don't think I'm especially coherent at the moment, but I believe that's
why this crazy query really DOES deliver the LastName, FirstName, & MRN.

In my defense, I was very new to Access when I started designing this
database, and like Topsy, it just grows and grows.

--
Thanks for your time!

Sue,
As I said earlier the SQL statement you posted will never return a last name
or a first name. It will return a column with that name, however the data
in the column will be the data that is in the PtID field. Are you sure you
are posting the correct query?

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, tblPatientProtocolActivity.PtID AS MRN
/// The above three lines will all return the same data - Pt ID///

If you are looking at the query grid (Design view) you would see the
following for the first three columns

Field: LastName: PtID
Table: tblPatientProtocolActivity

Field: FirstName: PtID
Table: tblPatientProtocolActivity

Field: MRN: PtID
Table: tblPatientProtocolActivity


SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, UCase(tblPatientProtocolActivity.PtID) AS MRN
, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity
ON tblPatients.PtID = tblPatientProtocolActivity.PtID
WHERE tblPatientProtocolActivity.PtStatusID in (3,15,16)
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC
, tblPatientProtocolActivity.[ICF date] DESC
, tblPatientProtocolActivity.D1Tx DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Just in case it was mis-pasted, here's the SQL again. As I think about it,
I
can probably account for one of the duplicates - I'm asking for LastName,
FirstName, & MRN for patients for 2 separate protocols. Beyond that, Lord
knows if you're stuck, John, I'm certainly unlikely to solve it - you've
been
a huge help to me (and all of us on the board) many times.

Again, thanks for your thoughts & time.



SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName,
tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments,
tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS
[Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

--
Thanks for your time!

I am stuck since the SQl you posted would never return a the LastName or
the firstName from tblPatients.

It would return PtID three times. Two times with whatever case the
value was stored in and once in upper case.

Something is askew, but I don't know what.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Sue wrote:
Data returned & there was no error message - the characters just didn't
go to
uppercase.

I don't work in SQL, so I don't know how
"tblPatientProtocolActivityPtId"
wound up showing up multiple times.

The patient info data comes from tblPatients and consists of 3 fields -
LastName, FirstName, MRN. Each patient may be enrolled on multiple
protocols
(and vice versa), a many-to-many relationship. Their status for each
protocol
will be different. So there's a second table -
tblPatientProtocolActivity -
that uses the primary key from tblPatients and tblProtocols to keep
this info
separate.

I'm sure there are MANY more elegant ways to achieve what I'm doing,
but the
database is immensely useful to our workgroup and, except for this
uppercase/lowercase issue, it does everything I need except cook
breakfast.

Thank you!!

 

[John Spencer]

Sue,

The structure you describe looks good. It is not the structure of your
tables that is confusing me. It is that the SQL string you posted would
never return the Last Name and First Name of the patients.

Have you attempted to run the exact SQL you have posted as a query - not as
the source for a report or for a form? If you have and it returns the first
and last name of the patient I would be very surprised and totally confused.
If you are opening a form or printing a report that shows this as the record
source, then I would make a GUESS that somewhere there is some VBA code that
is changing the record source prior to the form opening or the report
printing.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Maybe this will clear up the muddle.
In tblPatients, I have an autonumber as my primary key. Additional fields
include first name, last name, MRN, etc.
In tblProtocols, I have an autonumber as my primary key, then the protocol
name, etc.
In tblPatientProtocolActivity, I use a foreign key from tblPatients and a
foreign key from tblProtocols to "unite" a given patient with each
protocol
he's screened for, enrolled in, etc. Since patients may be enrolled in
several protocols over time, and since each protocol can have many
patients,
I needed to establish a way to track each patient's involvement with a
protocol separately.

In tblPatientProtocolActivity, then, I pull the patient's LastName,
FirstName, & MRN from tblPatients using the foreign key.

I don't think I'm especially coherent at the moment, but I believe that's
why this crazy query really DOES deliver the LastName, FirstName, & MRN.

In my defense, I was very new to Access when I started designing this
database, and like Topsy, it just grows and grows.

--
Thanks for your time!

Sue,
As I said earlier the SQL statement you posted will never return a last
name
or a first name. It will return a column with that name, however the
data
in the column will be the data that is in the PtID field. Are you sure
you
are posting the correct query?

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, tblPatientProtocolActivity.PtID AS MRN
/// The above three lines will all return the same data - Pt ID///

If you are looking at the query grid (Design view) you would see the
following for the first three columns

Field: LastName: PtID
Table: tblPatientProtocolActivity

Field: FirstName: PtID
Table: tblPatientProtocolActivity

Field: MRN: PtID
Table: tblPatientProtocolActivity


SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, UCase(tblPatientProtocolActivity.PtID) AS MRN
, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity
ON tblPatients.PtID = tblPatientProtocolActivity.PtID
WHERE tblPatientProtocolActivity.PtStatusID in (3,15,16)
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC
, tblPatientProtocolActivity.[ICF date] DESC
, tblPatientProtocolActivity.D1Tx DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Just in case it was mis-pasted, here's the SQL again. As I think about
it,
I
can probably account for one of the duplicates - I'm asking for
LastName,
FirstName, & MRN for patients for 2 separate protocols. Beyond that,
Lord
knows if you're stuck, John, I'm certainly unlikely to solve it -
you've
been
a huge help to me (and all of us on the board) many times.

Again, thanks for your thoughts & time.



SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName,
tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments,
tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS
[Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

--
Thanks for your time!


:

I am stuck since the SQl you posted would never return a the LastName
or
the firstName from tblPatients.

It would return PtID three times. Two times with whatever case the
value was stored in and once in upper case.

Something is askew, but I don't know what.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Sue wrote:
Data returned & there was no error message - the characters just
didn't
go to
uppercase.

I don't work in SQL, so I don't know how
"tblPatientProtocolActivityPtId"
wound up showing up multiple times.

The patient info data comes from tblPatients and consists of 3
fields -
LastName, FirstName, MRN. Each patient may be enrolled on multiple
protocols
(and vice versa), a many-to-many relationship. Their status for each
protocol
will be different. So there's a second table -
tblPatientProtocolActivity -
that uses the primary key from tblPatients and tblProtocols to keep
this info
separate.

I'm sure there are MANY more elegant ways to achieve what I'm doing,
but the
database is immensely useful to our workgroup and, except for this
uppercase/lowercase issue, it does everything I need except cook
breakfast.

Thank you!!

 

[Guest]

I hate to confuse you - truly I do, because you and the other MVPs have
UNconfused me countless times. But in my initial post to you, I mentioned
that this is the source for the query on which the report is based, and
indeed it does return last name, first name, MRN, etc. I think we've chatted
on this issue long enough that this message was lost. Sorry if I've
contributed to the confusion.

So here is the SQL for the query

SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName, tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments, tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS [Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC , tblPatientProtocolActivity.D1Tx
DESC;


In the query, the MRN displays all caps. In the report BASED on that query,
not so.
--
Thanks for your time!

Sue,

The structure you describe looks good. It is not the structure of your
tables that is confusing me. It is that the SQL string you posted would
never return the Last Name and First Name of the patients.

Have you attempted to run the exact SQL you have posted as a query - not as
the source for a report or for a form? If you have and it returns the first
and last name of the patient I would be very surprised and totally confused.
If you are opening a form or printing a report that shows this as the record
source, then I would make a GUESS that somewhere there is some VBA code that
is changing the record source prior to the form opening or the report
printing.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Maybe this will clear up the muddle.
In tblPatients, I have an autonumber as my primary key. Additional fields
include first name, last name, MRN, etc.
In tblProtocols, I have an autonumber as my primary key, then the protocol
name, etc.
In tblPatientProtocolActivity, I use a foreign key from tblPatients and a
foreign key from tblProtocols to "unite" a given patient with each
protocol
he's screened for, enrolled in, etc. Since patients may be enrolled in
several protocols over time, and since each protocol can have many
patients,
I needed to establish a way to track each patient's involvement with a
protocol separately.

In tblPatientProtocolActivity, then, I pull the patient's LastName,
FirstName, & MRN from tblPatients using the foreign key.

I don't think I'm especially coherent at the moment, but I believe that's
why this crazy query really DOES deliver the LastName, FirstName, & MRN.

In my defense, I was very new to Access when I started designing this
database, and like Topsy, it just grows and grows.

--
Thanks for your time!

Sue,
As I said earlier the SQL statement you posted will never return a last
name
or a first name. It will return a column with that name, however the
data
in the column will be the data that is in the PtID field. Are you sure
you
are posting the correct query?

SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, tblPatientProtocolActivity.PtID AS MRN
/// The above three lines will all return the same data - Pt ID///

If you are looking at the query grid (Design view) you would see the
following for the first three columns

Field: LastName: PtID
Table: tblPatientProtocolActivity

Field: FirstName: PtID
Table: tblPatientProtocolActivity

Field: MRN: PtID
Table: tblPatientProtocolActivity


SELECT tblPatientProtocolActivity.PtID AS LastName
, tblPatientProtocolActivity.PtID AS FirstName
, UCase(tblPatientProtocolActivity.PtID) AS MRN
, tblPatientProtocolActivity.ScreeningDate
, tblPatientProtocolActivity.[ICF date]
, tblPatientProtocolActivity.D1Tx
, tblPatientProtocolActivity.Comments
, tblPatientProtocolActivity.PtStatusID
, tblPatients.ApptDate, tblPatients.Time
, tblPatients.[Room#OfInpts] AS [Rm#]
, tblPatients.[ApptNeeded?]
, tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity
ON tblPatients.PtID = tblPatientProtocolActivity.PtID
WHERE tblPatientProtocolActivity.PtStatusID in (3,15,16)
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC
, tblPatientProtocolActivity.[ICF date] DESC
, tblPatientProtocolActivity.D1Tx DESC;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Just in case it was mis-pasted, here's the SQL again. As I think about
it,
I
can probably account for one of the duplicates - I'm asking for
LastName,
FirstName, & MRN for patients for 2 separate protocols. Beyond that,
Lord
knows if you're stuck, John, I'm certainly unlikely to solve it -
you've
been
a huge help to me (and all of us on the board) many times.

Again, thanks for your thoughts & time.



SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName,
tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments,
tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS
[Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON
tblPatients.PtID
=
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC ,
tblPatientProtocolActivity.D1Tx
DESC;

--
Thanks for your time!


:

I am stuck since the SQl you posted would never return a the LastName
or
the firstName from tblPatients.

It would return PtID three times. Two times with whatever case the
value was stored in and once in upper case.

Something is askew, but I don't know what.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Sue wrote:
Data returned & there was no error message - the characters just
didn't
go to
uppercase.

I don't work in SQL, so I don't know how
"tblPatientProtocolActivityPtId"
wound up showing up multiple times.

The patient info data comes from tblPatients and consists of 3
fields -
LastName, FirstName, MRN. Each patient may be enrolled on multiple
protocols
(and vice versa), a many-to-many relationship. Their status for each
protocol
will be different. So there's a second table -
tblPatientProtocolActivity -
that uses the primary key from tblPatients and tblProtocols to keep
this info
separate.

I'm sure there are MANY more elegant ways to achieve what I'm doing,
but the
database is immensely useful to our workgroup and, except for this
uppercase/lowercase issue, it does everything I need except cook
breakfast.

Thank you!!

 

[John Spencer]

Sue,
What I am trying to say is that the report is probably NOT based on this
query. Somehow the report is using a different query. Either there is some
VBA code changing the record source for the report or the record source is
different than this query.

Open the report in design view and click on the three dots for the record
source. That should open up a query window, switch to the SQL view and see
if it is the same SQL as you have posted. If not, then you will need to
change this SQL to force upper case. If so, then close the Query window and
look at the events tab of the report and see if there is any code in the on
open event that could be modifying the record source.

Of course, we could just modify the format of the control that is displaying
the MRN and not worry about the query. IF you want to go that route. in
design view
-- Click on the control that is bound to the MRN field
-- If properties are not showing, Right-Click on the control and select
Properties
-- Select the Format Tab
-- Enter < into the Format property (that will force all upper case)

Save the report and run it.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

First off, your last suggestion - modifying the control - worked. For some
dumb reason, I had the "<" entered as ">". NOT helpful in solving my problem.
As to looking for code, there's nothing in the events tab of the report
itself.
Finally, here's the SQL for the query using your 3-dot-in-the-report
instructions - I believe it's the same?

SELECT tblPatientProtocolActivity.PtID AS LastName,
tblPatientProtocolActivity.PtID AS FirstName, tblPatientProtocolActivity.PtID
AS MRN, tblPatientProtocolActivity.ScreeningDate,
tblPatientProtocolActivity.[ICF date], tblPatientProtocolActivity.D1Tx,
tblPatientProtocolActivity.Comments, tblPatientProtocolActivity.PtStatusID,
tblPatients.ApptDate, tblPatients.Time, tblPatients.[Room#OfInpts] AS [Rm#],
tblPatients.[ApptNeeded?], tblPatients.MDID
FROM tblPatients INNER JOIN tblPatientProtocolActivity ON tblPatients.PtID =
tblPatientProtocolActivity.PtID
WHERE (((tblPatientProtocolActivity.PtStatusID)=16 Or
(tblPatientProtocolActivity.PtStatusID)=15 Or
(tblPatientProtocolActivity.PtStatusID)=3))
ORDER BY tblPatientProtocolActivity.ScreeningDate DESC ,
tblPatientProtocolActivity.[ICF date] DESC , tblPatientProtocolActivity.D1Tx
DESC;


Please don't worry yourself further - unless I've created an itch you simply
MUST scratch for your own peace of mind. You've solved my immediate problem,
and I'm immensely grateful.

If you find you won't be able to sleep nights without figuring out why the
query I have works, I'll be glad to keep trying to chat about it.

Again, thank you SO much.