Question

Locked

How to derive per-row counts based on a combobox?

By joebattig ·
I am using Microsoft Access 2007. I have a series of comboboxes and would like to run a report on the content of those comboboxes. I would like to have two columns, the left column being the field entry, the right column being the number of times that entry was chosen in the database. It seems to me that this shouldn't be that difficult, but I am completely stuck. Any ideas?

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Not really an access type but

by Tony Hopkinson In reply to How to derive per-row cou ...

But you need to a query that

does this

Select MyCoumn1,count() From MyTable MyColumn1 = ? Group By MyColumn1

? would be the value in the combo.

You'll need one of these for each combo (fired by OnChange probably)
In Fact drop the left column and show number of entries next to the combo.

If you want one one report for all the combos
you could use a query like this

Select MyCoumn1 as Value,count() as NumberOfEntries From MyTable MyColumn1 = ? Group By MyColumn1
UNION
Select MyCoumn2,count() From MyTable MyColumn1 = ? Group By MyColumn2

If say it was forname and surname and town, I'd consider inserting a third column, because in theory the same value could be in all three columns but mean diffent things


Select cast('Forename',VarChar(16) as Type,MyCoumn1 as Value,count() as NumberOfEntries From MyTable MyColumn1 = ? Group By MyColumn1
UNION
Select 'Surname',MyCoumn2,count() From MyTable MyColumn1 = ? Group By MyColumn2

Hopefully there's some helpful ideas somewhere in there.

Back to Software Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums