DCount

[hurst75]

Hi All,

I posted the other day, got two perfect answers from Anchoress and Scooterbug (thanks for that guys!) and then realised that my question didn't go far enough for what I want to do.

So to briefly outline,

I have 2 tables, Asbestos and AsbestosSample

I am a surveyor and visit sites, buildings etc. The site details are entered into the Asbestos table.

Whilst on site I identify potential asbestos containing items (ACM) and carry out a risk assessment on each. A record for each ACM is entered into the AsbestosSample table.

In some cases I take a sample of the ACM and have it analysed by a lab. If this is the case, the Ident field in the AsbestosSample table for that record is given the value "Sample".

No when it comes to invoicing, I charge a flat fee for the building survey, Y, and charge for each individual sample IF it is sent to the lab for analysis, Z.

So for example, I carry out a survey to Joe Bloggs Offices and find 20 ACMs but only take samples from 16 of those ACMs. The invoice amount would be;

Total = Y + (16 x Z)

Thats the background!

So I am trying to create a query to handle this for me. I have linked the two tables, Asbestos and AsbestosSample, in a query by their corresponding ID fields, Asbestos.ReportRef and AsbestosSample.ReportRef.

The query contains all the client details, name, address, date etc. Thats the easy part.
Now I need to use a function which will count the number of records in the AsbestosSample table for the corresponding ReportRef field, where Ident = "Sample".
I have this so far but its just returning the total number of records in the AsbestosSample table;

DCount("*","AsbestosSample","AsbestosSample.[IndentificationLevel] = 'Sample'" And "Asbestos.[ReportRef]= AsbestosSample.[ReportRef]")


Your assistance is greatly appreciated!!!

 

[namliam]

You dont use a dcount for that, you use a proper query... and JOIN the query (or left join/left outer join) the query to get your results...

Also creating an invoice usually would look like:
Building survey 100
16 ACM tested at 10 each 160

Invoice total: 260

The basic problem your looking at is a design one, not a query or dcount one...

 

[hurst75]

I'm afraid my access knowledge is a little rusty, having designed this database some 5-6 yrs ago.

Is the statement below true;

Left Join Properties = Include all the records from 'Asbestos' and only those records from 'AsbestosSample' where the joined fields are equal

If the above is true then what is the Left Outer Join?

Also what do you mean by a "proper query"?

Thnx

 

[namliam]

Left join == Left Outer join
Left outer join is the FULL 'proper' name

"Porper query"
DCount is a poor solution in many cases, like this one. What happens if there is another compound you need to have researched? Another Dcount, and another and another...

Instead you should have one select statement/query that counts all the research you did and list them in your invoice as a sub-query/sub-report

 

[hurst75]

Thanks Mailman,

I've done it and without DCount as recommended.

Cheers