How to have combination answers and seperate later 4

[EllieFant]

I am creating a database for a co-worker that has no administrative help to create it for him. Overall it should be a pretty simple database to create...I do have a delima on how to do something though.

Eric (the gentleman I am doing this for) wants an ExitCode field and be able to choose one of the following Exit Codes:
A, C, D, E, I, L, N, G, T or P. That was going to be easy (use a list box) until I read the fine print on the form they are now filling out by hand...it states: A combination of codes may be used.

My question is can I make one field store in combination of the Exit Codes and still be able to break them out for a report? For example, if Eric makes an entry and his Exit code is C and E...I will want to be albe to print a report that says how many entries Eric has had and how many of those were each type:

Eric - 10 Entries
A - 1
B - 1
E - 2
N - 3
T - 3

Does this make since to anyone? I know what I want to be able to do, just not sure how to go about asking you if I can do it.

Thanks in advance for your help.
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[Isadore]

Ell. Yes, use a multiselection list box, and then capture the data. Here's a tidbit that might help you.

To read the various values from a multiselection listbox, you can try:

i = 0
Set ctlSource = Forms![frmEditWebChem]![lstContacts]
For intCurrentRow = 0 To ctlSource.ListCount - 1
i = i + 1
If i = 1 Then
strItems = ctlSource.Column(0, intCurrentRow)
Else
strItems = strItems & ", " & ctlSource.Column(0, intCurrentRow)
End If
Next intCurrentRow
Me![CNames] = strItems

This routine takes the values A, B, C and D, say, in a list box, and creates in one textbox "A, B, C, D".

And you can truncate later. However, if you know that, say, 5 will be the maxium number you'll ever have to deal with you could create 5 hidden textboxes and use then in a way that you do not create "A, B, C, D" but read each one into its own textbox. E.g.,

Set ctlSource = Forms![frmEditWebChem]![lstCID]
For intCurrentRow = 0 To ctlSource.ListCount - 1
i = i + 1
If i = 1 Then
Me![ID1] = CLng(ctlSource.Column(0, intCurrentRow))
ElseIf i = 2 Then
Me![ID2] = CLng(ctlSource.Column(0, intCurrentRow))
ElseIf i = 3 Then
Me![ID3] = CLng(ctlSource.Column(0, intCurrentRow))
ElseIf i = 4 Then
Me![ID4] = CLng(ctlSource.Column(0, intCurrentRow))
ElseIf i = 5 Then
Me![ID5] = CLng(ctlSource.Column(0, intCurrentRow))
End If
Next intCurrentRow

Hope this helps a bit. Post back if you need further guidance. Someone might drop by and add their two cents on top of this.

 

[EllieFantHome]

Thank you for the help although I am just learning this VBA coding. I know that this is asking alot, but could you put an example database together and send it to me (lena.wood@starband.net) so I can see how it all works? I will understand if you don't have the time. I learn much better if I have a working example to follow - tends to make more sense.

Thanks for your time...I really do appreciate the kindness offered in this group.

 

[Isadore]

Ellie - sure, no problem. I'll put together a simple example for you on a form and forward to you. Let me know how it goes - you might post your final solution here for others to view.

 

[EllieFantHome]

OH THANK YOU! I assure you that I will post the final solution. Please accept a purple star - wish I could give you more of a thank you though

 

[Isadore]

Ellie: No problem. That's what academics is all about - helping others. Its a pleasure, I assure you.

 

[EllieFant]

Isadore,

Well, I am not sure I totally understand what I did, but I used parts of the database that you sent me and made it so that when you click on a name in the list box, it puts it in a field that is part of a table (so that I can keep track of the selections). I used the following code:

Private Sub listNames_AfterUpdate()
[Name1] = Null
[Name2] = Null
[Name3] = Null
[Name4] = Null
[Name5] = Null

i = 0
Set ctlSource = Forms![MyTry]![listNames]
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
i = i + 1
If i = 1 Then
[Name1] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 2 Then
[Name2] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 3 Then
[Name3] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 4 Then
[Name4] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 5 Then
[Name5] = ctlSource.Column(0, intCurrentRow)
End If
End If
Next intCurrentRow
End Sub

I am sure I did things wrong, but hey, it works

Now my next question is how do I join Name1, Name2, Name3, Name4, and Name5 into one field so that I can generate a report like I described above? I found an example of joining two fields if they are in different tables in a book I have (Access Cookbook), but am not sure if it will work when there are 5 fields and they are all in the same table.

I really appreciate your help and have learned how to use a list box...and that is a step in the right direction!
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[Lightning]

Lena

You shouldn't need to join the five fields together to generate your report. Base your report on a query.

Create a query based on your table. Set the criteria row for [NAME1] as
Forms![MyTry]![Name1]

For [Name2], set the SECOND criteria row as
Forms![MyTry]![Name2]
Set the third criteria row for [Name3], etc

This is creating an OR statement for you report.

Print rows where [Name1] = a OR [Name2] = B OR [Name3] = c OR...etc

Create your report as a Summary report if you only wish to show totals rather than details.

HTH
Lightning

 

[Isadore]

Lightening: Good point. Lena, whenever you can "regenerate" results, on a form or in a report as pointed out by Lightening, it is advisable to do so. Good luck!

 

[flyboytim]

I designed a medical database that had the data entry field and the report field for the Immunology of the patient in a form the Medics could understand. It would be a string in the form of "ANA+ ACA+ RF+ Ro+ La+" in a field called AutoAntibodies. It was easier for data entry, and report generation, to keep the string in one field, rather than splitting it up into the elements for each antibody in different fields. Negative values were ignored.
However, I might occasionally want to break down counts for single antibodies or for those that sometimes appeared together, and it would be nice to have these antibodies always in the right case, and in the same order in the string. The following Access SQL enables the strings to be broken down into their elements.

SELECT DISTINCTROW IMMUNOLOGY1.StudyNo AS StudyNumber, IMMUNOLOGY1.AutoAntibodies AS AABList, IIf([AutoAntibodies] Like "*ana*","ANA+","&quot AS ANA, IIf([AutoAntibodies] Like "*aca*","ACA+","&quot AS ACA, IIf([AutoAntibodies] Like "*Scl70*","Scl70+","&quot AS Scl70, IIf([AutoAntibodies] Like "*RF*","RF+","&quot AS RF, IIf([AutoAntibodies] Like "*ro*","Ro+","&quot AS RO, IIf([AutoAntibodies] Like "*la+*","La+","&quot AS La, IIf([AutoAntibodies] Like "*pmscl*","PmScl+","&quot AS pmscl, IIf([AutoAntibodies] Like "*nRNP*","nRNP+","&quot AS nRNP, IIf([AutoAntibodies] Like "*U3*","U3-RNP+","&quot AS [U3-RNP]
FROM IMMUNOLOGY1;

In order to recreate the string, but neatly ordered, a query was built to concatenate the values of these elements into the complete list again.
You may find a use of the Iif() function to break down your combination of exit codes so they can be counted.

 

[EllieFant]

I was in Spokane this weekend and away from the internet (having withdrawls bad haha).

I have been learning SO much on this list that it just makes me want to learn more and more so before we left for Spokane I purchased the Developers Handbook (Vol 1 and 2). Now mind you 99% of it is over my head, but at one time not so long ago 110% was over my head. ANYWAY...I was paging through this book on our drive up to Spokane and I seen something that caught my eye...a union query! Although much of it was going over my head, a few things stuck out and I tried it...IT WORKED!

I am so amazed at the power of Access and I am sure I am only just looking at the surface - heck I haven't even scratched the surface yet.

I guess my point is that with all your help and ideas, I actually found a different way to do things (probably not the easiest way) but it works..and that keeps me encouraged to keep learning.

I will try the suggestions posted above and let you know how things go.

Thanks again for all your help and advice!
Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net