Query counting records

[hurst75]

Hi All,

I'm trying to get my database to produce invoices. I am a surveyor and carry out asbestos surveys which involves inspecting sites and identifying asbestos containing materials.

Below are the database parameters;

I have two tables - Asbestos, AsbestosSample

Asbestos contains the client name and address and site details
AsbestosSample contains details regarding each item found

My costs are based on a charge for the survey and then a fixed charge per identified item. For example,

Invoice Total = survey cost Y + (number of samples x Z)

I plan to create a query, which contains the client details (fields in Asbestos table) and a field which counts the number of samples (=related records in the AsbestosSample table)

My expression so far has been;

DCount("*","AsbestosSample", {EXPR})

The {EXPR} is the part I can't work out.

Please help!! Many thanks in advance for your time.

 

[Anchoress]

I will tell you how I would do it:

Make a new query, and add both tables.

Add to the query all the fields from the parent table you want in the result.

Add to the query the field you want to count.

Turn on the "group" button (the Sigma symbol), from the toolbar.

In the field that you want to count, select "Count" from the operators that show up when you turn on grouping.

That should do it. I am using Access2000. This is a very simple query, doesn't need coding.

 

[Scooterbug]

DCount("*","AsbestosSample", {EXPR})

The {EXPR} is the part I can't work out.

You are on the right track. the [expr] should be equal to the ID number of the Client that you are billing. (You are storing their unique ID number with each record, right?)

So it would be:

DCount("*","AsbestosSample", "[ClientID]=" & Me.IdNumber)

Make sure you substitute ClientID and IdNumber with whatever your naming convention is.

 

[hurst75]

@Anchoress - Thanks so much thats great.

I forgot to mention one thing though. I only charge for the item identification where a sample is taken from said item and sent to a lab.

Therefore, there is criteria for the count :

where, the field named identificationlevel = "sample"

How do I incorporate that?

Many thanks!!