Why is text being stored as numeric?

Thanks for the advice on cross-posting. I saw that after I posted in this
group. I'll be sure to do that if I have the occassion again.

I'm confident that the problem is in the join relationship of the tables as
both you and Tina have addressed.

I can create the queries I need as long as I do not have tblStatusV1 in the
query. The problem I have though, is that reports show ID numbers rather
than text; e.g., "1" vice "Active" and users could not relate to that very
well.

The ID fields in both tables were created by ACCESS as the Primary Key and
are autonumbers. When I add PatientID to both tables, which data type should
they be and should they be the Primary Key in those tables?

Thanks

"BruceM" wrote:

If you feel the need to post in more than one group you should add the
additional groups to the To field of your message (I think that is called
cross-posting) rather than posting a duplicate question in another group.
It is in your interest to do so, as the whole discussion will appear in one
thread rather than scattered across several groups.
To expand upon something that has already been mentioned about which fields
you are linking together, I would suggest that you give your ID numbers
unique names, such as PatientID and StatusID. If you did so, PatientID
would be a field in addition to StatusID in tblStatus. Your relationship
would be between the two PatientID fields.

"shep" wrote in message
...
I understand and only use forms to enter and edit data.
Here is the query SQL:
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.ID =
tblStatusV1.ID
WHERE (((tblStatusV1.Status)="1" Or (tblStatusV1.Status)="2"));

This pulls does not pull any records.
The join is ID to ID
There are 4 records (for testing) and all have a status.

"John Vinson" wrote:

On Tue, 13 Sep 2005 14:49:45 -0700, "shep"
wrote:

Thanks. That makes sence to me.
I changed all the fields in tblPatientV1 that were lookups to text. Now
the
data entered on the form through combo boxes show in the table as the ID
number.

I joined the two tables in a query ID to ID. When I pull patient name
from
tblPatientV1 and Status from tblStatusV1, I get only 3 records, 1 each
for
the 3 types of status.

If I Link Status To Status, I don't get any records.

When I delete tblStatusV1 from the query, all records show, but of
course I
get IDs for Status and other info such as Gender and Primary Insurance.

What am I likely doing wrong in joining the tables.

I'm not sure, because I don't know what you're doing exactly!

You should be joining on ID to ID. If every record in tblPatientV1 has
a non-NULL Status ID field (numeric, not blank), this should work. If
it doesn't please open the query in SQL view and post it here.

Clearly you can't link Status to Status, since tblPatientV1 does not
have any records containing "Active" or any othyer text value in its
status field... so you won't get any other records.

Again...

DON'T use table datasheets, or for that matter, query datasheets for
routine viewing or editing of data. *That is not their purpose*.
Datasheet view is for development and debugging; once you are
confident that you have the correct information, create a Form.

John W. Vinson[MVP]

Tina nailed it: you are trying to link two autonumber fields. This cannot
be done.
I am not suggesting that you add PatientID to both tables. Rather, I am
suggesting that you name rename ID in tblPatient to PatientID, and ID in
tblStatus to StatusID (or something like that).
It is difficult for me to answer your questions because I would have
proceeded differently. As I understand, a patient's current status is all
that concerns you. A patient will be either Active OR Inactive OR
Discharged. In that case I would store Status as a text field in
tblPatient, and forget about linking. However, if there is some reason why
you would prefer to link, I believe the procedure (after renaming the fields
as I have suggested) would be to add a StatusID field to tblPatient. If
StatusID in tblStatus is Autonumber (as defined in table design view) then
StatusID in tblPatient would be Number. If StatusID in tblStatus is
something other than Autonumber, StatusID in tblPatient would be the same
type of field. This will let you create a relationship between the StatusID
fields. When you do so, click Enforce Referential Integrity.
This is the opposite of what I previously suggested, and I apologize for
steering you in the wrong direction. I did not fully understand the
situation at the time. By the way, there is no requirement that linked
fields have the same name, but IMHO it certainly makes things simpler than
if you try to sort out identically-named fields in multiple tables.
I will repeat that since you are storing a single field (Status) that is
unlikely to change, you could just store the text value (Active, Inactive,
Discharged) in tblPatient. You could use a query to specify which Status
category you want to use. That is to say, you could sort by status, or set
up a parameter in the Status field. See Help for more information about
parameter queries. You could also group by Status in Reports.
If you are using linked fields, just put both tables into your query. Base
the form on the query, and place on the form a control (e.g. text box) bound
to the text field in tblStatus.

"shep" wrote in message
...
Thanks for the advice on cross-posting. I saw that after I posted in this
group. I'll be sure to do that if I have the occassion again.

I'm confident that the problem is in the join relationship of the tables
as
both you and Tina have addressed.

I can create the queries I need as long as I do not have tblStatusV1 in
the
query. The problem I have though, is that reports show ID numbers rather
than text; e.g., "1" vice "Active" and users could not relate to that very
well.

The ID fields in both tables were created by ACCESS as the Primary Key and
are autonumbers. When I add PatientID to both tables, which data type
should
they be and should they be the Primary Key in those tables?

Thanks

"BruceM" wrote:

If you feel the need to post in more than one group you should add the
additional groups to the To field of your message (I think that is called
cross-posting) rather than posting a duplicate question in another group.
It is in your interest to do so, as the whole discussion will appear in
one
thread rather than scattered across several groups.
To expand upon something that has already been mentioned about which
fields
you are linking together, I would suggest that you give your ID numbers
unique names, such as PatientID and StatusID. If you did so, PatientID
would be a field in addition to StatusID in tblStatus. Your relationship
would be between the two PatientID fields.

"shep" wrote in message
...
I understand and only use forms to enter and edit data.
Here is the query SQL:
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.ID =
tblStatusV1.ID
WHERE (((tblStatusV1.Status)="1" Or (tblStatusV1.Status)="2"));

This pulls does not pull any records.
The join is ID to ID
There are 4 records (for testing) and all have a status.

"John Vinson" wrote:

On Tue, 13 Sep 2005 14:49:45 -0700, "shep"
wrote:

Thanks. That makes sence to me.
I changed all the fields in tblPatientV1 that were lookups to text.
Now
the
data entered on the form through combo boxes show in the table as the
ID
number.

I joined the two tables in a query ID to ID. When I pull patient
name
from
tblPatientV1 and Status from tblStatusV1, I get only 3 records, 1
each
for
the 3 types of status.

If I Link Status To Status, I don't get any records.

When I delete tblStatusV1 from the query, all records show, but of
course I
get IDs for Status and other info such as Gender and Primary
Insurance.

What am I likely doing wrong in joining the tables.

I'm not sure, because I don't know what you're doing exactly!

You should be joining on ID to ID. If every record in tblPatientV1 has
a non-NULL Status ID field (numeric, not blank), this should work. If
it doesn't please open the query in SQL view and post it here.

Clearly you can't link Status to Status, since tblPatientV1 does not
have any records containing "Active" or any othyer text value in its
status field... so you won't get any other records.

Again...

DON'T use table datasheets, or for that matter, query datasheets for
routine viewing or editing of data. *That is not their purpose*.
Datasheet view is for development and debugging; once you are
confident that you have the correct information, create a Form.

John W. Vinson[MVP]

you should NOT have a PatientID field in tblStatusV1. as you noted, both
your tables already have a primary key field, and you don't need to change
them, or add other fields. the confusion we're all having in communicating,
is caused by the fact that Access named the primary keys in both tables as
ID.

you already have a Status field in tblPatientV1. that field is the foreign
key from tblStatusV1, and it holds the ID value from tblStatusV1, that you
choose from the combo box on the form. you don't need to change any of that.
so the Status field in tblPatientV1 is equivalent to the ID field in
tblStatusV1. change your query to

SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID
WHERE tblStatusV1.ID="1" Or tblStatusV1.ID="2";

hth


"shep" wrote in message
...
Thanks for the advice on cross-posting. I saw that after I posted in this
group. I'll be sure to do that if I have the occassion again.

I'm confident that the problem is in the join relationship of the tables
as
both you and Tina have addressed.

I can create the queries I need as long as I do not have tblStatusV1 in
the
query. The problem I have though, is that reports show ID numbers rather
than text; e.g., "1" vice "Active" and users could not relate to that very
well.

The ID fields in both tables were created by ACCESS as the Primary Key and
are autonumbers. When I add PatientID to both tables, which data type
should
they be and should they be the Primary Key in those tables?

Thanks

"BruceM" wrote:

If you feel the need to post in more than one group you should add the
additional groups to the To field of your message (I think that is
called
cross-posting) rather than posting a duplicate question in another
group.
It is in your interest to do so, as the whole discussion will appear in
one
thread rather than scattered across several groups.
To expand upon something that has already been mentioned about which
fields
you are linking together, I would suggest that you give your ID numbers
unique names, such as PatientID and StatusID. If you did so, PatientID
would be a field in addition to StatusID in tblStatus. Your
relationship
would be between the two PatientID fields.

"shep" wrote in message
...
I understand and only use forms to enter and edit data.
Here is the query SQL:
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.ID =
tblStatusV1.ID
WHERE (((tblStatusV1.Status)="1" Or (tblStatusV1.Status)="2"));

This pulls does not pull any records.
The join is ID to ID
There are 4 records (for testing) and all have a status.

"John Vinson" wrote:

On Tue, 13 Sep 2005 14:49:45 -0700, "shep"
wrote:

Thanks. That makes sence to me.
I changed all the fields in tblPatientV1 that were lookups to text.
Now
the
data entered on the form through combo boxes show in the table as
the ID
number.

I joined the two tables in a query ID to ID. When I pull patient
name
from
tblPatientV1 and Status from tblStatusV1, I get only 3 records, 1
each
for
the 3 types of status.

If I Link Status To Status, I don't get any records.

When I delete tblStatusV1 from the query, all records show, but of
course I
get IDs for Status and other info such as Gender and Primary
Insurance.

What am I likely doing wrong in joining the tables.

I'm not sure, because I don't know what you're doing exactly!

You should be joining on ID to ID. If every record in tblPatientV1
has
a non-NULL Status ID field (numeric, not blank), this should work. If
it doesn't please open the query in SQL view and post it here.

Clearly you can't link Status to Status, since tblPatientV1 does not
have any records containing "Active" or any othyer text value in its
status field... so you won't get any other records.

Again...

DON'T use table datasheets, or for that matter, query datasheets for
routine viewing or editing of data. *That is not their purpose*.
Datasheet view is for development and debugging; once you are
confident that you have the correct information, create a Form.

John W. Vinson[MVP]

That did it Tina!!

I have test driven it extensively and it works in every query I've tried.
It appears the key was your setting the Status in tblPatientV1 to = ID in
tblStatusV1. Now I can create queries using a Staus.e.g. Active, from
tblStatusV1.Status and get results including the text of status.

Thanks very much to all three of you for your great patience and your highly
professional help!!

"tina" wrote:

you should NOT have a PatientID field in tblStatusV1. as you noted, both
your tables already have a primary key field, and you don't need to change
them, or add other fields. the confusion we're all having in communicating,
is caused by the fact that Access named the primary keys in both tables as
ID.

you already have a Status field in tblPatientV1. that field is the foreign
key from tblStatusV1, and it holds the ID value from tblStatusV1, that you
choose from the combo box on the form. you don't need to change any of that.
so the Status field in tblPatientV1 is equivalent to the ID field in
tblStatusV1. change your query to

SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID
WHERE tblStatusV1.ID="1" Or tblStatusV1.ID="2";

hth


"shep" wrote in message
...
Thanks for the advice on cross-posting. I saw that after I posted in this
group. I'll be sure to do that if I have the occassion again.

I'm confident that the problem is in the join relationship of the tables
as
both you and Tina have addressed.

I can create the queries I need as long as I do not have tblStatusV1 in
the
query. The problem I have though, is that reports show ID numbers rather
than text; e.g., "1" vice "Active" and users could not relate to that very
well.

The ID fields in both tables were created by ACCESS as the Primary Key and
are autonumbers. When I add PatientID to both tables, which data type
should
they be and should they be the Primary Key in those tables?

Thanks

"BruceM" wrote:

If you feel the need to post in more than one group you should add the
additional groups to the To field of your message (I think that is
called
cross-posting) rather than posting a duplicate question in another
group.
It is in your interest to do so, as the whole discussion will appear in
one
thread rather than scattered across several groups.
To expand upon something that has already been mentioned about which
fields
you are linking together, I would suggest that you give your ID numbers
unique names, such as PatientID and StatusID. If you did so, PatientID
would be a field in addition to StatusID in tblStatus. Your
relationship
would be between the two PatientID fields.

"shep" wrote in message
...
I understand and only use forms to enter and edit data.
Here is the query SQL:
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.ID =
tblStatusV1.ID
WHERE (((tblStatusV1.Status)="1" Or (tblStatusV1.Status)="2"));

This pulls does not pull any records.
The join is ID to ID
There are 4 records (for testing) and all have a status.

"John Vinson" wrote:

On Tue, 13 Sep 2005 14:49:45 -0700, "shep"
wrote:

Thanks. That makes sence to me.
I changed all the fields in tblPatientV1 that were lookups to text.
Now
the
data entered on the form through combo boxes show in the table as
the ID
number.

I joined the two tables in a query ID to ID. When I pull patient
name
from
tblPatientV1 and Status from tblStatusV1, I get only 3 records, 1
each
for
the 3 types of status.

If I Link Status To Status, I don't get any records.

When I delete tblStatusV1 from the query, all records show, but of
course I
get IDs for Status and other info such as Gender and Primary
Insurance.

What am I likely doing wrong in joining the tables.

I'm not sure, because I don't know what you're doing exactly!

You should be joining on ID to ID. If every record in tblPatientV1
has
a non-NULL Status ID field (numeric, not blank), this should work. If
it doesn't please open the query in SQL view and post it here.

Clearly you can't link Status to Status, since tblPatientV1 does not
have any records containing "Active" or any othyer text value in its
status field... so you won't get any other records.

Again...

DON'T use table datasheets, or for that matter, query datasheets for
routine viewing or editing of data. *That is not their purpose*.
Datasheet view is for development and debugging; once you are
confident that you have the correct information, create a Form.

John W. Vinson[MVP]

we operate on the "paddle-wheel principle" around here - somebody's paddle
is sure to be in the water at any given time, and next time it will be
somebody else's! g


"shep" wrote in message
...
That did it Tina!!

I have test driven it extensively and it works in every query I've tried.
It appears the key was your setting the Status in tblPatientV1 to = ID in
tblStatusV1. Now I can create queries using a Staus.e.g. Active, from
tblStatusV1.Status and get results including the text of status.

Thanks very much to all three of you for your great patience and your
highly
professional help!!

"tina" wrote:

you should NOT have a PatientID field in tblStatusV1. as you noted, both
your tables already have a primary key field, and you don't need to
change
them, or add other fields. the confusion we're all having in
communicating,
is caused by the fact that Access named the primary keys in both tables
as
ID.

you already have a Status field in tblPatientV1. that field is the
foreign
key from tblStatusV1, and it holds the ID value from tblStatusV1, that
you
choose from the combo box on the form. you don't need to change any of
that.
so the Status field in tblPatientV1 is equivalent to the ID field in
tblStatusV1. change your query to

SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID
WHERE tblStatusV1.ID="1" Or tblStatusV1.ID="2";

hth


"shep" wrote in message
...
Thanks for the advice on cross-posting. I saw that after I posted in
this
group. I'll be sure to do that if I have the occassion again.

I'm confident that the problem is in the join relationship of the
tables
as
both you and Tina have addressed.

I can create the queries I need as long as I do not have tblStatusV1
in
the
query. The problem I have though, is that reports show ID numbers
rather
than text; e.g., "1" vice "Active" and users could not relate to that
very
well.

The ID fields in both tables were created by ACCESS as the Primary Key
and
are autonumbers. When I add PatientID to both tables, which data type
should
they be and should they be the Primary Key in those tables?

Thanks

"BruceM" wrote:

If you feel the need to post in more than one group you should add
the
additional groups to the To field of your message (I think that is
called
cross-posting) rather than posting a duplicate question in another
group.
It is in your interest to do so, as the whole discussion will appear
in
one
thread rather than scattered across several groups.
To expand upon something that has already been mentioned about which
fields
you are linking together, I would suggest that you give your ID
numbers
unique names, such as PatientID and StatusID. If you did so,
PatientID
would be a field in addition to StatusID in tblStatus. Your
relationship
would be between the two PatientID fields.

"shep" wrote in message
...
I understand and only use forms to enter and edit data.
Here is the query SQL:
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.ID =
tblStatusV1.ID
WHERE (((tblStatusV1.Status)="1" Or (tblStatusV1.Status)="2"));

This pulls does not pull any records.
The join is ID to ID
There are 4 records (for testing) and all have a status.

"John Vinson" wrote:

On Tue, 13 Sep 2005 14:49:45 -0700, "shep"
wrote:

Thanks. That makes sence to me.
I changed all the fields in tblPatientV1 that were lookups to
text.
Now
the
data entered on the form through combo boxes show in the table
as
the ID
number.

I joined the two tables in a query ID to ID. When I pull
patient
name
from
tblPatientV1 and Status from tblStatusV1, I get only 3 records,
1
each
for
the 3 types of status.

If I Link Status To Status, I don't get any records.

When I delete tblStatusV1 from the query, all records show, but
of
course I
get IDs for Status and other info such as Gender and Primary
Insurance.

What am I likely doing wrong in joining the tables.

I'm not sure, because I don't know what you're doing exactly!

You should be joining on ID to ID. If every record in
tblPatientV1
has
a non-NULL Status ID field (numeric, not blank), this should
work. If
it doesn't please open the query in SQL view and post it here.

Clearly you can't link Status to Status, since tblPatientV1 does
not
have any records containing "Active" or any othyer text value in
its
status field... so you won't get any other records.

Again...

DON'T use table datasheets, or for that matter, query datasheets
for
routine viewing or editing of data. *That is not their purpose*.
Datasheet view is for development and debugging; once you are
confident that you have the correct information, create a Form.

John W. Vinson[MVP]

I haven't read all of the posts in this thread but my eye was caught by this
statement: I have test driven it extensively and it works in every query
I've tried.

Make sure you try it out with situations where you expect it to fail. Test
with no/null statuses, for example, or invalid statuses. Just to make sure.

Good Luck!
--

Chaim


"shep" wrote in message
...
That did it Tina!!

I have test driven it extensively and it works in every query I've tried.
It appears the key was your setting the Status in tblPatientV1 to = ID in
tblStatusV1. Now I can create queries using a Staus.e.g. Active, from
tblStatusV1.Status and get results including the text of status.

Thanks very much to all three of you for your great patience and your
highly
professional help!!

"tina" wrote:

you should NOT have a PatientID field in tblStatusV1. as you noted, both
your tables already have a primary key field, and you don't need to
change
them, or add other fields. the confusion we're all having in
communicating,
is caused by the fact that Access named the primary keys in both tables
as
ID.

you already have a Status field in tblPatientV1. that field is the
foreign
key from tblStatusV1, and it holds the ID value from tblStatusV1, that
you
choose from the combo box on the form. you don't need to change any of
that.
so the Status field in tblPatientV1 is equivalent to the ID field in
tblStatusV1. change your query to

SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.Status =
tblStatusV1.ID
WHERE tblStatusV1.ID="1" Or tblStatusV1.ID="2";

hth


"shep" wrote in message
...
Thanks for the advice on cross-posting. I saw that after I posted in
this
group. I'll be sure to do that if I have the occassion again.

I'm confident that the problem is in the join relationship of the
tables
as
both you and Tina have addressed.

I can create the queries I need as long as I do not have tblStatusV1
in
the
query. The problem I have though, is that reports show ID numbers
rather
than text; e.g., "1" vice "Active" and users could not relate to that
very
well.

The ID fields in both tables were created by ACCESS as the Primary Key
and
are autonumbers. When I add PatientID to both tables, which data type
should
they be and should they be the Primary Key in those tables?

Thanks

"BruceM" wrote:

If you feel the need to post in more than one group you should add
the
additional groups to the To field of your message (I think that is
called
cross-posting) rather than posting a duplicate question in another
group.
It is in your interest to do so, as the whole discussion will appear
in
one
thread rather than scattered across several groups.
To expand upon something that has already been mentioned about which
fields
you are linking together, I would suggest that you give your ID
numbers
unique names, such as PatientID and StatusID. If you did so,
PatientID
would be a field in addition to StatusID in tblStatus. Your
relationship
would be between the two PatientID fields.

"shep" wrote in message
...
I understand and only use forms to enter and edit data.
Here is the query SQL:
SELECT Count(tblPatientV1.ChartNumber) AS CountOfChartNumber
FROM tblPatientV1 INNER JOIN tblStatusV1 ON tblPatientV1.ID =
tblStatusV1.ID
WHERE (((tblStatusV1.Status)="1" Or (tblStatusV1.Status)="2"));

This pulls does not pull any records.
The join is ID to ID
There are 4 records (for testing) and all have a status.

"John Vinson" wrote:

On Tue, 13 Sep 2005 14:49:45 -0700, "shep"
wrote:

Thanks. That makes sence to me.
I changed all the fields in tblPatientV1 that were lookups to
text.
Now
the
data entered on the form through combo boxes show in the table
as
the ID
number.

I joined the two tables in a query ID to ID. When I pull
patient
name
from
tblPatientV1 and Status from tblStatusV1, I get only 3 records,
1
each
for
the 3 types of status.

If I Link Status To Status, I don't get any records.

When I delete tblStatusV1 from the query, all records show, but
of
course I
get IDs for Status and other info such as Gender and Primary
Insurance.

What am I likely doing wrong in joining the tables.

I'm not sure, because I don't know what you're doing exactly!

You should be joining on ID to ID. If every record in
tblPatientV1
has
a non-NULL Status ID field (numeric, not blank), this should
work. If
it doesn't please open the query in SQL view and post it here.

Clearly you can't link Status to Status, since tblPatientV1 does
not
have any records containing "Active" or any othyer text value in
its
status field... so you won't get any other records.

Again...

DON'T use table datasheets, or for that matter, query datasheets
for
routine viewing or editing of data. *That is not their purpose*.

Datasheet view is for development and debugging; once you are
confident that you have the correct information, create a Form.

John W. Vinson[MVP]