Concatenated field displays Primary Key, need description

[MissaLissa]

Greetings Experts

I have a subform that contains a combo box for "Reason Category". The Reason Category table contains Category ID (Primary Key) and Reason Category.

Category ID	Reason Category
0	N/A
1	Underwriting
2	Marketing & Sales
3	Claims
4	Policyholder Services
5	Miscellaneous

<tbody>
</tbody>

I have another combo box for "Reason" which displays items based on what was chosen in the Reason Category field. The Reason table contains the Primary Key number from the Reason Category table. (small sample below)

Category Number	Reason
3	Unsatisfactory Settlements
0	U&C Reduction
2	Replacement
1	Refusal to Insured
1	Recession

<tbody>
</tbody>

These are set up so that categories chosen are passed through to a master table. The data passed through is the description not the primary key. However, when creating a select query which creates a new field with the Reason Category and the Reason fields concatenated, the results are as: 3 - Unsatisfactory Settlements. I need it to be displayed as: Claims - Unsatisfactory Settlements

Can someone please help me solve this issue?

 

[xenou]

In general it's a join on the tables -- IF the keys are properly set up. It looks like you are only using the Reason table by itself, but the category descriptions are in the Reason Category table.

SELECT [Reason Category], [Reason]
FROM [Reason]
INNER JOIN [Reason Category]
ON [Reason].[Category Number] = [Reason Category].[Category ID]

 

[MissaLissa]

Hi xenou

I don't think I was very clear as to what I'm trying to accomplish. I'm trying to create a select query that pulls all the data from the Master table. There are 2 fields in the Master Table that I need concatenate, but when I run the query I'm getting the primary key for the Reason Category.

NAIC Reason Code: [tbl_001Master].[Reason Category] & " - " & [tbl_001Master].[Reason] .... this statement's results in "3 - Unsatisfactory Settlements". I need it to be displayed as "Claims - Unsatisfactory Settlements".

Below is the SQL statement as it is now.

SELECT tbl_001Master.[State of Execution], tbl_001Master.[Source of Complaint], [tbl_001Master]![Applicant Last Name] & ", " & [tbl_001Master]![Applicant First Name] AS [Participants Name (Last, First)], tbl_001Master.[Coverage ID Number], tbl_001Master.[Coverage Type], tbl_001Master.Comments AS [Nature of Complaint], tbl_001Master.[Reason Category], [tbl_001Master].[Reason Category] & " - " & [tbl_001Master].[Reason] AS [NAIC Reason Code], tbl_001Master.[Company Disposition], tbl_001Master.[LTCG Received] AS [Date LTCG Received], tbl_001Master.[Compliance Received] AS [Date Compliance Received], tbl_001Master.[Acklgmnt Letter Sent] AS [Date Complaint Acknowledgement Letter Was Sent], tbl_001Master.[Referred to Acct Mgr] AS [Date Referred to Account Manager for Client Input or Approval], tbl_001Master.Closed AS [Date Closed], tbl_001Master.[Compliance Analyst] AS [Entered By], tbl_001Master.[Link to Complaint], tbl_001Master.[Link to Memo], tbl_001Master.[Link to Response], IIf([tbl_001Master]![BD to Acklgmnt Sent]=0,"N/A",IIf([tbl_001Master]![BD to Acklgmnt Sent]>5,"No","Yes")) AS [Acknowledgement Sent Within 5 Business Days   (Yes or No)], tbl_001Master.[BD to Close] AS [Number of Days from Date LTCG Received to Date Closed], tbl_001Master.[BD to Refer to Mgr] AS [Number of Days from LTCG Received to Referred to Acct Manager] INTO [tbl_CalPERS Register]
FROM tbl_001Master
WHERE (((tbl_001Master.[Client Number])="020"));

 

[xenou]

NAIC Reason Code: [tbl_001Master].[Reason Category] & " - " & [tbl_001Master].[Reason] .... this statement's results in "3 - Unsatisfactory Settlements". I need it to be displayed as "Claims - Unsatisfactory Settlements".

Is this a number or a description: [tbl_001Master].[Reason Category] ?

 

[MissaLissa]

Hi xenou.... thanks for your help. Reason Category is a description (text)... below is the layout of all the tables and queries.

tbl_Reason Category	Selected in form and passed through to the Master table. Reason Category
	Field Name	Data Type
pKEY	Category ID	Number
	Reason Category	Text
tbl_Reason	Selected in form based off selection made above and passed through to the Master table. Reason
	Field Name	Data Type
	Category Number	Number
	Reason	Text
tbl_001Master	(Not All Fields are Listed)
	Field Name	Data Type
pKEY	Record ID	AutoNumber
	Client Name	Text
	Reason Category	Text
	Reason	Text
qMkTbl_CalPERS Register	This is the Make Table query created to make tbl_CalPERS Register
	Field Name	Expression
	NAIC Reason Code	[tbl_001Master].[Reason Category] & " - " & [tbl_001Master].[Reason]
tbl_CalPERS Register	(Not All Fields are Listed)
	Field Name	Data Type
	NAIC Reason Code	Text

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[xenou]

Just to clarify, what are the values for Reason Category in the [tbl_001Master] table and what are the values for Reason Category in the [tbl_Reason Category] table? Are they the same and are they both descriptions?

 

[MissaLissa]

Yes, they are the same.

 

[MissaLissa]

Both descriptions (text)

 

[MissaLissa]

Record ID	Client Name	Client Number	Reason Category	Reason
3	United of Omaha	026		Delay
4	Mutual of Omaha	026		Customer Satisfaction
5	Mutual of Omaha	026		Customer Satisfaction
6	CalPERS	020	Claims	Denial of Claim
7	CalPERS	020	Claims	Unsatisfactory Settlements
8	CalPERS	020	Claims	Miscellaneous
22	CalPERS	020	Underwriting	Cancellation
23	United of Omaha	026		Non-covered charges
24	Genworth Individual - NCL	128	Policyholder Services	Miscellaneous
25	Genworth Group	054	Policyholder Services	Miscellaneous
26	CalPERS	020	Claims	Unsatisfactory Settlements
27	CalPERS	020	Policyholder Services	Delays

<caption>tbl_001Master</caption>

<thead>


</thead>
<tbody>


</tbody>
<tfoot></tfoot>

Category ID	Reason Category
0	N/A
1	Underwriting
2	Marketing & Sales
3	Claims
4	Policyholder Services
5	Miscellaneous

<caption>tbl_Reason Category</caption>

<thead>


</thead>
<tbody>


</tbody>
<tfoot></tfoot>

 

[xenou]

You are using some kind of combobox then, maybe?? Where exactly does this get used on what's the rest of it:

NAIC Reason Code: [tbl_001Master].[Reason Category] & " - " & [tbl_001Master].[Reason] .... this statement's results in "3 - Unsatisfactory Settlements". I need it to be displayed as "Claims - Unsatisfactory Settlements".

Note that one possible reason is that you are referring to a combo box that has a lookup on the Reason Category table and the combo box has a bound field which is the category number. You need to lookup the reason category based on that number.