Survey Results 1

[schase]

Access 2000

I have multiple pages set up - for a user to enter new salespeople, tv stations, cable Networks, etc.

These populate separate tables.

There is a main form that has a series of drop down box's. (populated by the tables mentioned above) This form enters into tblEntries.

On the results page, I would like it to display say the salespeople, John Doe - then count how many records = John Doe.

Any idea how? I would think to specify in the recordset where rsSalesperson=*****

Thank you in advance.

"Insert witty remark here"

Stuart

 

[link9]

you can accomplish this through pure sql, and get a recordset with that information. GROUP BY is the clause you will need.

Say you had a field in tvStations called 'salespeople', and you wanted to count how many had 'John Doe' in the salesperson column. Then:

SELECT COUNT(*) AS numOfRecords, salespeople
FROM tvStations
WHERE salespeople = 'John Doe'
GROUP BY salespeople

Or, to get a full list with all salespeople and their count:
SELECT COUNT(*) AS numOfRecords, salespeople
FROM tvStations
GROUP BY salespeople

You can also use a JOIN:

SELECT COUNT(*) as numOfRecords, s.salespeople
FROM salespeople s INNER JOIN tvStations t
ON s.salespeople = t.salespeople
GROUP BY s.salespeople

and so on.

 

[schase]

thanks Link, brain hiccup I think. "Insert witty remark here"

Stuart