Well, I have a *partial* solution here, didn't figure out how to put
this onto a Form, but did get an updateable Query for populating the
table, and one for displaying the counts you wanted.
Maryann said:
I have reviewed posts related to what I am about to ask, so I think I know
the general direction to go, but I'm so new to all this, I am not able to get
things to work just yet. Here is what I have:
I have a database with many combo boxes (about 75). The combo boxes are
bound to fields of an underlying table that correspond to the fields being
evaluated. The row sourse for the combo boxes (they are all the same row
source) is a table (tblDetermination). tblDetermination has 2 fields
(DeterID, & DeterDesc). The choices from tblDetermination are "correct",
"incorrect", "FYI", & "N/A". What I need to do is count the number of
occurences per record that Incorrect is chosen in the combo boxes. In the
combo boxes, it stores the DeterID, which is 1,2,3, or 4, so incorrect would
store as a value of 2.
I am assuming that because I have so many fields, that I am not going to be
able to just put a calculation in the control source of my text box.
Hopefully I was not too confusing in my explanation of the situation or my
needs. Any suggestions?
OK, I set up two Tables that look like these:
[tblDeter]
tblDeterID DeterDesc
---------- ---------
1 correct
2 incorrect
3 FYI
4 N/A
This is similar to your [tblDetermination], but with a shorter name.
[tblCombo]
RecordNum BoxNum tblDeterID
--------- ------- ----------
1 1 1
1 2 3
1 3 1
1 4 4
1 5 2
2 1 2
2 2 1
2 3 4
2 4 2
2 5 1
[tblCombo] has another field, [tblComboID], but I didn't show it here;
it's not used in these Queries. The record number for each of your
records is in [RecordNum], though as you see it occupies several records
in [tblCombo]. In each record, [BoxNum] identifies one of the 50 combo
boxes in your Form. For the sake of brevity, I included only 5
[BoxNum]s in each record, but I think you can see how to add the other
45. (You may want to set your database up to display these in smaller
sets, like maybe 15 or 20 at a time, to avoid cluttering the screen.)
Although I displayed raw numbers in the version of [tblCombo] shown
above, to make it clear what was stored there, I actually displayed the
values using a lookup field for [tblDeterID], giving the following
easier-to-read results:
[tblCombo] with lookup field:
RecordNum BoxNum tblDeterID
--------- -------- ----------
1 1 correct
1 2 FYI
1 3 correct
1 4 N/A
1 5 incorrect
2 1 incorrect
2 2 correct
2 3 N/A
2 4 incorrect
2 5 correct
If you wish to do that, use Table Design View, select the [tblDeterID]
field, select the Lookup tab, and set the properties as follows:
Display Control = List Box
Row Source Type = Table/Query
Row Source = tblDeter
Bound Column = 1
Column Count = 2
Column Widths = 0;1
To display and update the records, I used the following Query, though of
course you would want to include fields up to Combo50. For testing, I
suggest you start with five fields, as I did:
SELECT tblCombo_1.RecordNum,
tblCombo_1.tblDeterID AS Combo1,
tblCombo_2.tblDeterID AS Combo2,
tblCombo_3.tblDeterID AS Combo3,
tblCombo_4.tblDeterID AS Combo4,
tblCombo_5.tblDeterID AS Combo5
FROM tblCombo AS tblCombo_1,
tblCombo AS tblCombo_2,
tblCombo AS tblCombo_3,
tblCombo AS tblCombo_4,
tblCombo AS tblCombo_5
WHERE (((tblCombo_1.BoxNum)=1)
And ((tblCombo_2.BoxNum)=2)
And ((tblCombo_3.BoxNum)=3)
And ((tblCombo_4.BoxNum)=4)
And ((tblCombo_5.BoxNum)=5)
And ((tblCombo_2.RecordNum)=tblCombo_1.RecordNum)
And ((tblCombo_3.RecordNum)=tblCombo_1.RecordNum)
And ((tblCombo_4.RecordNum)=tblCombo_1.RecordNum)
And ((tblCombo_5.RecordNum)=tblCombo_1.RecordNum))
ORDER BY tblCombo_1.RecordNum;
As with the [tblCombo].[tblDeterID] field, whose values were the
somewhat meaningless ID numbers, I set the lookup properties of
[Combo1], [Combo2], etc., as I had with [tblCombo].[tblDeterID]. (Use
the same property values that I listed above, for each of these fields.)
I also set the properties of the entire Query to include
Recordset Type = Dynaset (Inconsistent Updates)
to allow the field contents to be changed, as if this were a Table with
lots of fields in each record. The results, based on the values in
[tblCombo], looked like this:
RecordNum Combo1 Combo2 Combo3 Combo4 Combo5
--------- --------- ------- ------- --------- ---------
1 correct FYI correct N/A incorrect
2 incorrect correct N/A incorrect correct
The fields may be updated either by clicking and choosing, or on the
keyboard via Tab and the first letter (c, i, f, n) of the value to be
stored.
Having done all this stuff, getting the totals is easy. The following
Query displays the number of "incorrect" values in each record:
[Q_ComboCountIncorrect]
SELECT tblCombo.RecordNum, Count(tblCombo.tblDeterID)
AS CountOftblDeterID
FROM tblCombo INNER JOIN tblDeter
ON tblCombo.tblDeterID = tblDeter.tblDeterID
WHERE (((tblDeter.DeterDesc)="incorrect"))
GROUP BY tblCombo.RecordNum;
The results of running this on my sample data are shown here:
RecordNum CountOftblDeterID
--------- -----------------
1 1
2 2
As I said, I wasn't able to produce an updatable Form with lookup values
based on these Queries, but I hope you can get some value out of them.
Good luck.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
