Problems with Count(*)

[JamesStevens]

Hi there

I have a form which is based on a query that links several tables.

The form lists all the records of learners on courses - for example:-

Adams Richard / Health And Safety
Allen Steve / Introduction to Spreadsheets
Allen Neville / Web Publishing
Allen Neville / Introduction to Spreadsheets

In the form header I have a calculated text box to display the number of courses: =Count(*) e.g. 4 in the above example.

I want to display the number of learners in another calculated text box in the form header but am unsure how to do this. Can you use =Count(*) with distinct? The above example should return 3 but I don't know how to do it.

The Learners all have unique LearnerID so I guess I need to count this but exclude the duplicates (of Allen Neville being in twice).

Help!

 

[Poteen]

I recommend you use Distinct in a listbox row source SQL and use Listcount to count the contents. Otherwise you'll have to write a VBA routine.
regards,
John

 

[Terpsfan]

I created a workaround by creating a query and then a DCount function that you could put in a text box or label. You can replace the tablename with a queryname obviously.

SELECT DISTINCT Learner
FROM Tablename

Gives you the distinct values for learners.

=DCount("*","tablename&quot

Gives you the count of distinct learners(3).

 

[JamesStevens]

Thanks Guys - that's great!

Quick too - jeez, not a bad site this ;0)

James

 

[JamesStevens]

Actually, I still have a problem :0(

I should have explained, the form is filtered when it opens depending on the users' input into a dialogue form "SelectLearnersDialogue". In this dialogue form they can choose from three combo boxes/option groups: the month, learner status and company. When they click on a button, the form "ViewLearnersSelect" opens and displays records depending on the value of the combo boxes.

I've done this using VB and setting the recordsource of the form to a SELECT/WHERE string which has multiple IF statements and so changes depending on the value of the combo boxes.

The text box in the ViewLearnersSelect form header with Count(*) correctly displays the number of records that this filtered form shows. I want to be able to count the number of learners in this filtered list excluding the duplicates where one learner is on 2 or more courses.

The query that the form is based on doesn't change so I think the 'create a query' solution returns the total number of individual learners and not the new filtered number.

I've been trying to incorporate a listbox into the form header but can't get my head round this either.

??!!!