Concat unbound text boxes for true check boxes

[EmAlbritton via AccessMonster.com]

Morning all.

On my form, I have multiple check boxes for test types. More than one check
box can be selected for each record. On my report, I need to show a text
description for the checkboxes that are true. I have managed to create
unbound text boxes on the report to convert the true check boxes to text. My
problem is, I would like to concat the true instances into one text box with
the descriptions separated by commas, and not leaving blank spaces for the
false occurences.

I have tried various examples of concat formulas, and haven't had any success.


Any help or advice is appreciated.

Thanks,
Emily

 

[Duane Hookom]

I expect your issue is that your table is not normalized. Each test type
should create a new record in a related table.

I would first normalize your table with a union query and then use multiple
column subreport or the generic concatenate function from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane to
display the multiple records.

 

[EmAlbritton via AccessMonster.com]

I don' t want new records for each test type.

Here is a bit of background. The database is tracking drugtests that are
performed. Some donors are given more than one test. They could provide a
"liquid" sample as well as a blood sample. I need to maintain one record for
testing occurence which may or may not include more than one test being
performed.

ea

 

[Duane Hookom]

My reply is exactly the same. You should normalize but if you choose not to,
you can use a union query as noted. If you don't understand how to implement
this, come back with your relevant table and field names.

 

[EmAlbritton via AccessMonster.com]

Duane-

Will post structure...and be open to suggestions. My first response was a
bit short I think......just didn't think that it wasn't set properly.

Anyway...

MainTable

PrimaryKey(Auto#)
Client(Text)
TestLocation(Text)
And others...don't really need to list here


DonorTable

PrimaryKey (Auto#)
ForeignKey(#...1toM rel. with above table)
DonorName(Text)
BAT(Y/N)
BATConf(Y/N)
Blood(Y/N)
DIPOnly(Y/N)
DNASaliva(Y/N)
ETOHSaliva(Y/N)
Hair(Y/N)
LabUDS(Y/N)
RapidUDS(Y/N)
Unknown(Y/N)

My current entry form is using a main form that completes information in main
table, with subform to complete information required for Donor Table. The
subform includes the checkboxes for the different test types listed as Y/N
above. If I did put this into another table...I guess I would create another
subform and place it on the current subform to handle the checkboxes?

I am a newbie to some degree....getting better everyday with the help of
people like you here on the forums.

Thanks for your time looking over this.

ea

 

[Duane Hookom]

I would create a union query
SELECT PrimaryKey as DonorKey, "BAT" as Test
FROM DonorTable
WHERE [Bat] = True
UNION ALL
SELECT PrimaryKey, "BATConf"
FROM DonorTable
WHERE [BATConf] = True
UNION ALL
SELECT PrimaryKey, "Blood"
FROM DonorTable
WHERE [Blood] = True
UNION ALL
SELECT PrimaryKey, "DIPOnly"
FROM DonorTable
WHERE [DIPOnly] = True
UNION ALL
SELECT PrimaryKey, "DNASaliva"
FROM DonorTable
WHERE [DNASaliva] = True
UNION ALL
SELECT PrimaryKey, "ETOHSaliva"
FROM DonorTable
WHERE [ETOHSaliva] = True
UNION ALL
SELECT PrimaryKey, "Hair"
FROM DonorTable
WHERE [Hair] = True
UNION ALL
SELECT PrimaryKey, "LabUDS"
FROM DonorTable
WHERE [LabUDS] = True
UNION ALL
SELECT PrimaryKey, "RapidUDS"
FROM DonorTable
WHERE [RapidUDS] = True
UNION ALL
SELECT PrimaryKey, "Unknown"
FROM DonorTable
WHERE [Unknown] = True;

You can then use a suggested method for displaying the results from this
union query.

If this were my application to maintain, I would work toward changing the
table structure removing the test fields and add a table similar to the
results of the union query above.

 

[EmAlbritton via AccessMonster.com]

Okay...I saved a test version of my db....and moved the test types to a
separate table. I have gotten the concat to work somewhat as the control
source for an unbound text box on my report.

My only problem now is removing the unncessary commas for nulls.

code is here:

Control source for TextBAT(this is not visible)...there are similar for the
other 9 types
=IIf([BAT]=-1,"BAT","")

Control source for TxtTestType (this one is visible on report)
=+[TextBAT]+(", "+[TextBATConf])+(", "+[TextBlood])+(", "+[TextDIPOnly])+(",
"+[TextDNASaliva])+(", "+[TextETOHSaliva])+(", "+[TextHair])+(", "+
[TextLabUDS])+(", "+[TextRapidUDS])+(", "+[TextUnknown])

I have these text boxes populated when the check boxes are true, and "" when
false.

This works perfectly......gives me exactly what I want to see....except all
the excess commas. I have swapped things around a few times, and can't
manage to get the commas to go away.

any thoughts?

ea

 

[Duane Hookom]

Apparently you don't understand my suggestion to normalize your tables. Did
you create the union query? Did you use the generic concatenate function to
display the test names?

 

[EmAlbritton via AccessMonster.com]

Duane-

I have hashed over your suggestion from yesterday. I can't see anywhere that
the table structure needs norm. I have all pertinent data split into
multiple tables.

I have created the Union Query as suggested. It gives me the information
that I am looking for...I think. You asked about the concat function. I am
not sure exactly where I would place this function. I tried putting it in
the unbound textbox on my report...all that did was showed BAT twice per
donor ID.

You also said that if it were your application you would change the table
structure to be more along the lines of the union query. So that I
understand this...you would have one table for just the testypes with the
DonorID as foreign key? The Union query is creating one row for each test
type...regardless of donorID. How would I accomplish that if I were creating
a separate table to track the test types as a table on it's own related to
the donor table. My thought process is making me think that there would be
one TestTypeTable record for each DonorID that has test types selected.

I have confused myself considerably now I think.

Any insight you can offer that could possible clarify things in my mind would
be appreciated.

Thanks for your time.

EA

 

[Duane Hookom]

If you have separate fields for test types then your table is not
normalized.
To get a comma delimited list of tests in one expression you would need to
have add the generic concatenate function into a standard module in your
mdb. You could then set a control source in your report to something like:

=Concatenate("SELECT Test FROM quniYourUnion WHERE DonorKey =" &
[PrimaryKey])

If you can't figure this out, come back with the name of your union query as
well as its SQL property.

 

[EmAlbritton via AccessMonster.com]

Duane-

Thanks so much for your help. I finally got it all to work. Once you
mentioned the module...I went back to your concat example and realized that
there was something I was missing. Once I copied the concat module into my
file...it works like a charm.

Thanks again. Now I can move on and quit spinning in circles.

EA