Count Votes in a query

[robingSA]

Hi,

I have a table (Businesses) with a number of different types of
shopping outlets (eg, supermarket, pet shop, Florist etc) as the
fields. People will submit a voting form on which they have named their
favourite store in each category. Each individual form will be captured
as a separate row.

I then need top run a query at the end of it all to see what the top 5
outlets in each category are and this is where I need the help please -
how should the query look?

Many thanks in advance!

Rob

 

[Nick Coe \(UK\)]

It would work a bit better if you had one column (field)
called BusType into which you put the different types of
outlet - probably from another table containing a list of
types used as the source of a combo box on your entry form.
Then you could filter on that column (using criteria in the
query design grid) to get only one type of outlet and sort
on whichever column contains your marks to get the top
scoring ones. IIRC there is a property in queries (Top
Values I think) which will let you get only a certain number
of results. You could make the query a parameter query
where you are prompted for the BusType for the filter
criteria manually each time the query is run.

Since you have the Type in different columns I think you
will need to have a different query for each column, with
criteria set accordingly though the sort and Top Values will
remain similar. Once you have the seperate queries working
you could then create a new query with each of those
individual queries as the sources to gather your results
together into one. Or use the SQL view to write a UNION
query to gather them all together.

Personally I'd go for the first option and alter my table
design and content. The second method could be a right PITA
to get right.

Good luck.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp is now Free
http://www.mrcomputersltd.com/ Repairs Upgrades

In (e-mail address removed) typed:

 

[MNewsGroup]

dfsdfsds

 

[MNewsGroup]

fff

dfsdfsds