Count multiple entries in Access

[Guest]

Hi guys,
I'm using Access 2000 and I want to perform a counting. I got a table
[Table1] with one field which has multiple entries per record. For example a
record could be "AAAA" or "BBBB" or "AAAA~BBBB~CCCC" or "DDDD~AAAA" or "AAAA
DDDD", and so on. It is a very poor set up for Access but that's what I have
to work with. I have created another table [Table2] with the proper set up,
each record with one field representing the possible individual values. In
this case, the table contains the records, AAAA, BBBB, CCCC and DDDD.
What I'm trying to do is counting the number of times each value appears in
the original table. For example, in the four entries I presented above, I
know that value AAAA appeared 4 times, value BBBB 2 times, CCCC 1 time and
DDDD 4 times. Is there any way that I could set up this query? Please note
that I have no knowledge of coding. I'd happy if I could also use a form to
help me select the values that I want counted at a time. For example, I could
choose in the form to count value "AAAA" only. Actually I tried this with no
success using the following function in a query:
IIf("*" & [Forms]![frm02-Counts]![cmbAuth1] & "*" In ([LI_Auths01]),1,0),
where cmbAuth1 is the combo box where I pick a value from and [LI_Auths01] is
the field which has multiple entries per record.
Thanks in advance

 

[Guest]

Try this:

SELECT Count([LI_Auths01]) AS CountOfLIAuths
FROM MyTable
WHERE ((([LI_Auths01) Like "*" & [Forms]![frm02-Counts]![cmbAuth1] & "*"));

Barry

 

[Guest]

Thanks for that Barry. It worked perfectly.

If I needed to go one step further and prepare a report that would bring the
count for each item (I mean, how many times AAA, BBBB, CCCC and DDDD appear
in the original table), would it be possible of achieving that with my lack
of coding knowledge?

Try this:

SELECT Count([LI_Auths01]) AS CountOfLIAuths
FROM MyTable
WHERE ((([LI_Auths01) Like "*" & [Forms]![frm02-Counts]![cmbAuth1] & "*"));

Barry

Hi guys,
I'm using Access 2000 and I want to perform a counting. I got a table
[Table1] with one field which has multiple entries per record. For example a
record could be "AAAA" or "BBBB" or "AAAA~BBBB~CCCC" or "DDDD~AAAA" or "AAAA
DDDD", and so on. It is a very poor set up for Access but that's what I have
to work with. I have created another table [Table2] with the proper set up,
each record with one field representing the possible individual values. In
this case, the table contains the records, AAAA, BBBB, CCCC and DDDD.
What I'm trying to do is counting the number of times each value appears in
the original table. For example, in the four entries I presented above, I
know that value AAAA appeared 4 times, value BBBB 2 times, CCCC 1 time and
DDDD 4 times. Is there any way that I could set up this query? Please note
that I have no knowledge of coding. I'd happy if I could also use a form to
help me select the values that I want counted at a time. For example, I could
choose in the form to count value "AAAA" only. Actually I tried this with no
success using the following function in a query:
IIf("*" & [Forms]![frm02-Counts]![cmbAuth1] & "*" In ([LI_Auths01]),1,0),
where cmbAuth1 is the combo box where I pick a value from and [LI_Auths01] is
the field which has multiple entries per record.
Thanks in advance