Returning Funky Values

[SaraMegan]

Hi, all. I just created a query out of three of my tables. When I run the query, I get all of the results I need. However, when I run the report I get these weird inconsistent results.

The primary tabe (tblEmployees) uses a lookup to tblClassCode to retrieve job titles. The result shows the position title, hiding the primary key field. The query returns the Position title, but the report sometimes shows the ClassCode. I can't figure it out!

Has anyone else encountered something like this? Any insights?

Thanks in advance.

--Sara

 

[Hayley Baxter]

Hi Sara

Do you mean that the field you are looking up Classcode is returning the id column and not the 2nd column which is related to the id no?

Hay

 

[SaraMegan]

Yes, that's it exactly, but only in some places. There are 41 results and five show the ClassCode (column1) instead of PositionTitle (column2).

Thanks, Hay!

--Sara

 

[Hayley Baxter]

Hmm strange why some would be ok and not others. I can't say I have experienced this Sara but you might want to double check in the lookup properties that the bound coulumn = 1 column count = 2 and column width = 0 then try compacting the database. If that still fails one last attempt might be to import into a new database but failing that I really can't be of any further help.

Hay

 

[SaraMegan]

Thanks, Hay. I checked that stuff, and it was all correct. And then I compacted the db, and it still happened. They are all the same results, too, so it's at least a consistent error. I also double-checked the table where the information is stored and it's all correct there. This is really strange. Thanks for your help.

--Sara

 

[The_Doc_Man]

For the cases where you get a code when you wanted text...

Did you verify that the code was valid? Because it might be possible to have run into one of those funky "NULL" issues that sometimes crop up with Access. When you drop an expression into something but that something involves a NULL, sometimes you get bizarre results such as failing to perform a particular substitution you expected to see.

 

[SaraMegan]

In case anyone was curious...

I'm still not sure why it was giving me strange results - there were none of those weird NULL things gonig on, but I think that when I was doing the initial data entry, I goofed.

I created this db from one that had been made by someone less experienced with Access than I. (Yes, it is possible!) Their system was not at all normalized and it was really quite messy. So I created an appropriate lookup table, and when I went to re-enter the results I must have left the results that looked correct withouth selecting the Limit to List property. I'm still not sure if that's what it was or what, but I just ended up deleting the relationship, deleting the field, re-creating the field, and re-entering all the data. (luckily there were only nine pages of data to re-enter...) Now it works like a charm.

Thanks, Hayley and Doc Man for your help.

--Sara

 

[Cosmos75]

Is this close to what you are trying to accomplish?

 

[SaraMegan]

I'm not sure if it is, Cosmos. But I've fixed it and it's all good now. I just had to re-do the relationship and data entry. I'll download it tonight, though, and peek at it tomorrow and see if it's helpful.

Thanks

--Sara

 

[Cosmos75]

I wouldn't bother, it looks like you've got it working now.

Mine example was just a simple two table database (tblPlayers & tblPositions).
One Position to Many Players and tblPlayers has a lookup field to tblPositions.
Then a query pulls together the players Name, Team (from tblPlayers) and the Position (from tblPositions).
And I added a report based on the query.

 

[SaraMegan]

That's what I did to begin with, and then re-did, basically. I think it was just a problem with the initial data-entry.

Thanks, anyway.

 

[Hayley Baxter]

Hi Sara

Glad you got to the bottom of it, good stuff.

Hope your well!!
Hay