Why is text being stored as numeric?

[shep]

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?

 

[tina]

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

 

[John Vinson]

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]

 

[shep]

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.

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

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?

 

[shep]

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]

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]

 

[shep]

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]

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]

 

[tina]

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

 

[BruceM]

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]

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]

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.

 

[tina]

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]

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]

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>

 

[Chaim]

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!