Report not Matching Query Results

[JohnC63]

I think I've seen similar posts like this but they always seem a bit different and don't quite answer the mail for me, so here goes.

I built a database that is using forms as the primary user interface. The form is collecting user data and storing it in a table (Recruitment).

I built several queries that use the table (Recruitment).

I put together reports that are based on the queries and they all seem to work fine except for one field (Status). The Status field is getting results from the query. The query runs fine and display the results as I anticipated including the Status field. Going back to the Form, the Status field is a Combo Box that uses a lookup Table called Status.

I'm new to this so when I built the lookup table to be used for the Combo Box it included a Primary Key which was auto numbered. That primary key is what is showing up in my Report's Status field (the query runs fine and does not display the Primary Key since I bounded it and set the col width as 0";2").

Do I need to delete the Primary Key from the Lookup Table (Status) to ensure the Report displays the text instead of the key??

Sorry for being long winded and thanks.

 

[spikepl]

You are just another victim of the tool of Beelzebub: lookup fields in tables.

Don't use them in tables. A lookup (combobox) in a form gives the same visual result, but does not confuse the bejesus out of everybody, because what you see in the table or query columns is what you get. Not so with lookup fields, which ought to be banished.

 

[arnelgp]

link your Recruitment Status.Value against Status.ID or whatever is the primary key of table status. you may now include the Status field from status table.

 

[JohnC63]

Thanks for the replies, but I'm still somewhat confused. I am using a combo box lookup in my form. It gets values from a table that has all the different types of 'status' my users can select from.

The table that my form feeds, stores the primary key associated with the text field my user selected in the combo box in the form. I have no problem with that since my users don't mess around with the tables.

The problem is what my report is doing. My report is running off of a query. The query displays the text fields for the 'status' (which is what I want) but the report displays the primary key instead of the text. What am I doing wrong?

 

[arnelgp]

like what i have said you have to link status.value field of your recruitment table to status.id of your status id, include in your query the description field of status on your status table. on the report this description is the one you will want to show.

 

[JohnC63]

like what i have said you have to link status.value field of your recruitment table to status.id of your status id, include in your query the description field of status on your status table. on the report this description is the one you will want to show.

But how do I link status.value field of my recruitment table to status.id of my status id??? I think I'm getting lost with the verbiage here. Are you saying that in my Recruitment Table I have to link the Status field to my Query's Status ID field? In the Query, am I adding a new field called Description which my Report will then reference??

Thank you for your patience.

I wanted to attach some screen shots for the Recruitment Table, the Query, and the Report's Property but I don't have a url to use, only the jpegs.

 

[plog]

Report not Matching Query Results

1. Is the datasource of the Report your query?

2. What is the SQL of that query?

 

[JohnC63]

The data source is a query. The SQL is pasted below:

SELECT [Recruitment Table].[HR Specialist (Name)], [Recruitment Table].RMC, [Recruitment Table].Announcement, [Recruitment Table].[SF 52 Request], [Recruitment Table].Service, [Recruitment Table].[Position Title], [Recruitment Table].Selectee, [Recruitment Table].Status, [Recruitment Table].[Firm EOD Date]
FROM [Recruitment Table]
WHERE ((([Recruitment Table].[HR Specialist (Name)])=[Which Specialist?]))
ORDER BY [Recruitment Table].RMC, [Recruitment Table].Announcement, [Recruitment Table].[SF 52 Request], [Recruitment Table].[Position Title];

For the Status field in this query, the Lookup in the property sheet reads:
Display Control = Combo Box
Row Source Type = Table/Query
Row Source = Lookup Table Status
Bound Colum = 1
Column Count = 2
Column Widths = 0";2"
Show Only Row Source Values = No

When I run the query, the Status field displays my text which is what I want. But the Report displays the Primary Key.

 

[arnelgp]

SELECT [Recruitment Table].[HR Specialist (Name)], [Recruitment Table].RMC, [Recruitment Table].Announcement, [Recruitment Table].[SF 52 Request], [Recruitment Table].Service, [Recruitment Table].[Position Title], [Recruitment Table].Selectee, [Status].Status, [Recruitment Table].[Firm EOD Date]
FROM [Recruitment Table] Left Join [Status] On [Recruitment Table].Status = [Status].[StatusID]
WHERE ((([Recruitment Table].[HR Specialist (Name)])=[Which Specialist?]))
ORDER BY [Recruitment Table].RMC, [Recruitment Table].Announcement, [Recruitment Table].[SF 52 Request], [Recruitment Table].[Position Title];

replace the StatusID with the right field name of the pk of [status] table.

 

[JohnC63]

Update...I think I figured it out. I believe what arnelgp was trying to tell me was to add the Lookup Table Status to the query and link it to my Recruitment table. I did that and then played with the three join type properties and #2 worked like a charm!!!!! The report now displays the text and not the primary key value.

Thanks everyone!!!!

 

[arnelgp]

glad you did it, anyway i was just guessing you status table name, so that maybe confused you too.