Why is text being stored as numeric?

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active, Inactive, and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID], [tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

you don't want the text to show in the *table*. in the table, you want the
stored data to show, and the stored data is the ID value taken from
tblStatusV1.
in the *form* (which is where you should do all data entry and data
display), you just need to adjust the ColumnWidth property of the Status
combo box, as

0"; 1"

so the first column (ID) is effectively hidden, and the second column
(Status) will show in the droplist, and in the combobox control after a
status is selected. note: the second column does not have to be 1 inch. set
it to whatever width you need to show the Status text values. read up on the
ColumnWidth property in Access Help to better understand it.

hth


"shep" wrote in message
...
I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active, Inactive,
and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID],
[tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

On Tue, 13 Sep 2005 09:55:06 -0700, "shep"
wrote:

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

That's because the ID number is what is actually stored in your table.
I'm guessing that you used Microsoft's misdesigned, misleading, and
all but useless Lookup Wizard. This will make your table LOOK like it
contains the text, by concealing the actual contents of the field
behind a combo box.

How can I get the text to show in the table?

Tables are for data storage. They should not be used for data entry,
display, or reporting.

To see the data with the status as text on a Form, use a Combo Box
based on the status table; use the ID as the bound column but the text
as the visible field. That way the computer sees the ID, you see the
text, and you're both happy.

In a Report, use a Query joining tblPatientV1 to the Status table;
pick up the status text from the status table, and the other
information from the patient table.

In a table datasheet, just accept the fact that a table datasheet
isn't designed for public view. You can drive your car without having
to look at the piston rings, after all!

John W. Vinson[MVP]

Thanks for responding
Then to run a query on the table to count number of Active patients, I have
to set the criterion to "1", 1 being the ID of Active. Is that correct?

I do have the column properties set so the drop list is text; i.e., Active
etc.

"tina" wrote:

you don't want the text to show in the *table*. in the table, you want the
stored data to show, and the stored data is the ID value taken from
tblStatusV1.
in the *form* (which is where you should do all data entry and data
display), you just need to adjust the ColumnWidth property of the Status
combo box, as

0"; 1"

so the first column (ID) is effectively hidden, and the second column
(Status) will show in the droplist, and in the combobox control after a
status is selected. note: the second column does not have to be 1 inch. set
it to whatever width you need to show the Status text values. read up on the
ColumnWidth property in Access Help to better understand it.

hth


"shep" wrote in message
...
I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active, Inactive,
and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID],
[tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

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.

Thanks


"John Vinson" wrote:

On Tue, 13 Sep 2005 09:55:06 -0700, "shep"
wrote:

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

That's because the ID number is what is actually stored in your table.
I'm guessing that you used Microsoft's misdesigned, misleading, and
all but useless Lookup Wizard. This will make your table LOOK like it
contains the text, by concealing the actual contents of the field
behind a combo box.

How can I get the text to show in the table?

Tables are for data storage. They should not be used for data entry,
display, or reporting.

To see the data with the status as text on a Form, use a Combo Box
based on the status table; use the ID as the bound column but the text
as the visible field. That way the computer sees the ID, you see the
text, and you're both happy.

In a Report, use a Query joining tblPatientV1 to the Status table;
pick up the status text from the status table, and the other
information from the patient table.

In a table datasheet, just accept the fact that a table datasheet
isn't designed for public view. You can drive your car without having
to look at the piston rings, after all!

John W. Vinson[MVP]

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

On Tue, 13 Sep 2005 17:15:04 -0700, "shep"
wrote:

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.

It sounds like you're joining the Patient's unique ID number to the
Status table's unique ID number.

That would appear to be the problem. Your tblPatientV1 table should
have a Status, or StatusID, or *some* foreign key linked to
tblStatusV1 - I don't know the name of that field, of course, but I'm
guessing that it is *not* [ID] and that [ID] is the Primary Key of the
patient table.

If I'm correct, then Patient 1 would be shown with the status
corresponding StatusID 1, Patient 3 with the status of StatusID 3 and
so on - and if there are patients whose ID's don't correspond to any
status table record, you would not see them.

Check that you're joining on the correct fields.

John W. Vinson[MVP]

to add on to John's reply:

your first post described tblStatusV1 as
**tblStatusV1 has two fields; ID and Status. Status has Active, Inactive,
and
Discharged entered as options.**

but the WHERE clause in your query is trying to match a NUMBER in the Status
field, as

**WHERE (((tblStatusV1.Status)="1" Or (tblStatusV1.Status)="2"))**

if your criteria will be on the Status field, then it has to be a valid
Status value: "Active", "Inactive", "Discharged". if your criteria will be
1 or 2, you need to set that criteria on the tblStatusV1.ID field.

hth


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

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]