Report Displaying Number Instead of Value

[andysgirl8800]

Alright,
I know this must be one of the stupidest and most simple question of all time, but I just can't get my brain to work this out and have been searching through the forums for more than 2 hours with no luck.
I built a report from a query based on a table. It looks great, all but for one field in the report. The related field in the table is a combo box with 4 options. It displays the text on the form and in the query, but as soon as I open the report, it's displaying the numberical value of the field instead of the text value. How can I fix it to display the text?

 

[Rich]

Add the text column to the query and then display that field on the Report instead

 

[andysgirl8800]

The column is already in the query and displays correctly if I open the query seperately. But after making the query the data source for the report, it doesn't display the text, only it's numerical designation.

 

[Rich]

You've added the wrong field to the report, delete it and add the correct one

 

[andysgirl8800]

I'm very sorry, I don't quite understand. How do I join the necessary tables for the correct column? I've completely rebuilt my query and my report, and it's still not displaying correctly. The primary underlying table is called "GPBS Members", the table holding the combo box options is called "Status". The query built on the table is "qryGPBSmembers" and the report built on the query is called "rptGPBSmembers". The current SQL for "qryGPBSmembers" looks like this:
SELECT [GPBS Members].[LAST NAME], [GPBS Members].[FIRST NAME], [GPBS Members].HRN, [GPBS Members].[DATE DENIAL SENT], [GPBS Members].[DATE APPROVAL SENT], [GPBS Members].[CLASS ENROLLMENT DATE], [GPBS Members].[SURGERY DATE], [GPBS Members].[REQUESTING PHYSICIAN], [GPBS Members].STATUS
FROM [GPBS Members]
ORDER BY [GPBS Members].[LAST NAME] DESC;
I'm sure there is something more I need to do, but I can't seem to work it out. I would appreciate your help, thank you.

 

[Kelemit]

IF you go to the table where the data is at.. does the status come out as a number as well?

In that case, what you have stored in the table is the number, and the status table probably has two columns, one the number you're getting, and the other the "description" you WANT to get.

The best way to fix this, I think, would be to make the box on you report a combo box, then in the recordsource of the combo box link it to the Status table, and have the combo box report the "description" of the status number.

IE: Display the Description not the number. You can do this by making a query in the combo boxes RECORD SOURCE (NOT CONTROL SOURCE) and makeing sure both fields are returned. Then, have the data BOUND to the column that displays the field, and on format page, show 2 columns, and on column width of the format for this combo box: do a width of 0 for the number, and a width of whatever you want (its in inches in US access) thats not 0 and it will display the discreption

 

[Rich]

IF you go to the table where the data is at.. does the status come out as a number as well?

In that case, what you have stored in the table is the number, and the status table probably has two columns, one the number you're getting, and the other the "description" you WANT to get.

The best way to fix this, I think, would be to make the box on you report a combo box, then in the recordsource of the combo box link it to the Status table, and have the combo box report the "description" of the status number.

IE: Display the Description not the number. You can do this by making a query in the combo boxes RECORD SOURCE (NOT CONTROL SOURCE) and makeing sure both fields are returned. Then, have the data BOUND to the column that displays the field, and on format page, show 2 columns, and on column width of the format for this combo box: do a width of 0 for the number, and a width of whatever you want (its in inches in US access) thats not 0 and it will display the discreption

Neither of your suggestions are the correct way to deal with look up tables

 

[Rich]

I'm very sorry, I don't quite understand. How do I join the necessary tables for the correct column? I've completely rebuilt my query and my report, and it's still not displaying correctly. The primary underlying table is called "GPBS Members", the table holding the combo box options is called "Status". The query built on the table is "qryGPBSmembers" and the report built on the query is called "rptGPBSmembers". The current SQL for "qryGPBSmembers" looks like this:
SELECT [GPBS Members].[LAST NAME], [GPBS Members].[FIRST NAME], [GPBS Members].HRN, [GPBS Members].[DATE DENIAL SENT], [GPBS Members].[DATE APPROVAL SENT], [GPBS Members].[CLASS ENROLLMENT DATE], [GPBS Members].[SURGERY DATE], [GPBS Members].[REQUESTING PHYSICIAN], [GPBS Members].STATUS
FROM [GPBS Members]
ORDER BY [GPBS Members].[LAST NAME] DESC;
I'm sure there is something more I need to do, but I can't seem to work it out. I would appreciate your help, thank you.

If the correct field is displaying in your query then it'll be in the field list at the top of the report, drag it off the list and drag it to the detail section of the report

 

[Kelemit]

Neither of your suggestions are the correct way to deal with look up tables

You may be right, then again...

Her point is that the table is recording the number associated with the field lookup.

On the form, the lookup table is returning both fields, but hides one and is bound to the field recording the number. This is what I'm assuming because she states its showing a number.

To return the correct description, you must link the lookup table back to the field that the form uses to fill out the field for that table.

Currently, as i understand from her description, the data stored in the table from the Look up table is NOT the description, but in all likelihood is teh primary key.

Regardless of whether this is the "right" way or "wrong" way of using lookup tables, you have to deal with it.

If you want to use a query to fix this problem, you need to put bring up both tables in the query, link the two tables. Main table.status to lookup table (primary key?) I'm assuming. Then display the lookup table, and only return all records from main table, and return only records from 2nd table where the "joined fields" =.

 

[Rich]

Currently, as i understand from her description, the data stored in the table from the Look up table is NOT the description, but in all likelihood is teh primary key.

Regardless of whether this is the "right" way or "wrong" way of using lookup tables, you have to deal with it.

Storing the Primary Key is of course the correct way

 

[Kelemit]

Possibly, unless you have a lookup table that you wish to edit over time / change. in which case, your numbers will now be pointless because the data in your table is a number and the data in you lookup table is gone...

Or you don't delete it, but can't keep other people from still picking it, unless now u do even more work and now add a "current / in use" check box and edit all drop boxes to only show if current = true.

Shrug. i would rather my data not be dependent on a look up table possibly being deleted / corrupted / bad / lost / screwed around with by a user.

Sides, after that, the data is correct for the main table, and the look up table remains independent and completely unnecessary.

Kelemit

 

[Rich]

Possibly, unless you have a lookup table that you wish to edit over time / change. in which case, your numbers will now be pointless because the data in your table is a number and the data in you lookup table is gone...



Kelemit

Yes but then surely one could just add another record ?

 

[Kelemit]

to which? The look up table? and thus have another Drop down menu list?

And what if you one of the options from you lookup table is now defunct / old / no longer used. You cannot remove it from the look up table, to do so will make your data on the main table useless. You must now add a way to "remove" the option from drop down boxes without removing the record from the actual look up table.

Admittedly not hard to do, but not necessary if you just store the data in your main table in the first place. You remove dependancy of your main data on another table which isn't necessary in the end.

 

[Rich]

Yes but then you could have 50,000 records all with the same text instead of just one

 

[Kelemit]

If the text was 10s to 20s of entries long, then yes. But when a text is only 3 to 5 letters long..

and 50,000 wouldn't have all the same text.. it would have one of 4 possible... and 4 possible is a smallish look up table.

Kelemit

And you are right, space-wise. Largish options would probably be wisest to store just numbers. The only downside to using the actual data is only storing redundant data. If one does not like that *shrug*

 

[andysgirl8800]

Oh dear, I'm afraid I've gotten a little lost. I took your suggestion, and added the "status" query to the original query and joined them to each other via the primary key. I dragged the field from the "status" query into the main query, and now the report IS displaying the name of the field, but not the correct data. My report should be about 7 pages long, when correct, but seems to only be showing one entry for each status category. What have I done wrong this time?

 

[Rich]

The joins in your query are the wrong way around

 

[andysgirl8800]

How do I fix it? I want to use the status as a header in the report, sorted by member last name below the heading. So, all members with a denied status will be listed ABC'd under the heading DENIED....does that make sense? It's like that now, only, instead of displaying denied, it says "10" then lists the members ABC'd. How do I modify the join to make this happen the right way?

 

[andysgirl8800]

I've been struggling with this for several days now and still can't seem to make this work. I only have 2 days left until I go on medical leave, and I have to have this straightened out before I leave. I can't seem to make these selected names appear instead of its number. I've messed around with the tables and the queries and the report, but haven't progressed at all. Please let me know how to do this or where to look to fix it. Thanks for any suggestions you may have.

 

[Rich]

Post a 97ver of the db and I'll take a look