Query Multiple Selections Field

[Grasor]

Good afternoon all,

I'm a novice Access user. I know some of the terminology and have taken a few certification prep courses but I don't regularly use the program so I still get stuck on what are probably trivial matters.

I'm building a database to track insurance claims.

For each claim I'm recording the claim's denial codes using a lookup to another table "tblDenialCodes" which just has fields for the [DenialCode] and [Code_Desc]. When I'm entering the claim to "tblInsuranceClaims" to the [Denial_Codes] field, I've allowed Multiple Values so I can just click the check boxes.

However, I don't have all the codes memorized. So on the Form "frmInsurance" I've put a textbox in the footer which is meant to display the tblDenialCodes.Code_Desc for each denial code selected in tblInsuranceClaims.Denial_Codes.

I can't seem to figure out how to make this work. Even the query I created 'qryInsuranceDenialCodes' which attempts to do this using SQL:

SELECT tblInsurance.Denial_Codes, tblNationwideDenialCodes.Code_Desc
FROM tblNationwideDenialCodes INNER JOIN tblInsurance ON tblNationwideDenialCodes.[DenialCode] = tblInsurance.[Denial_Codes].[Value];

The result only displays one of the descriptions. e.g If codes ZA11 and ZR11 are selected the query only retrieves the description for ZA11.

So I'm just not understanding how to properly create this functionality.

Can anyone guide me towards the end result? Keep in mind my experience with Access and SQL is limited.

Thanks
G

[June7]

Review https://support.microsoft.com/en-us/...624e1e323a#bm3

[Grasor]

June7,

Using the link you provided I was able to get the query to work. I recall now that you can't just reference the query as the Control Source in a textbox on a form though. So how do I get the textbox on the form to display the code descriptions for all selected codes?

Thank you.

[June7]

I have never used multi-value fields because they are a pain. Most experienced developers avoid them.

I think options are:

1. VBA function to build string of the multiple descriptive values http://allenbrowne.com/func-concat.html, call function from textbox

2. cascading listbox with its RowSource set to the expanded query, VBA code to syncronize with form record

3. subform with RecordSource set to expanded query, Master/Child links properties synchronize with main form record

Lock listbox and subform so edits are not allowed.

Understand that a multi-value field just holds links to records in a hidden table. A table you could build and manage as a normal related table instead.