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!!!
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!!!