I am using Access 2007.
I have 2 tables - abatements and materialsused. They are related by abateID.
I need to count the number of paint and powerwash abatements by month.
You can identify paint and power wash abatements by the materials used (materialcodeID in the materialsused table).
I wanted to attack the solution is steps. Step 1 is to build a query to pull the abatements and identify which used power wash materials and which abatements used paint materials. Step 2 is to figure out how to count the abatements.
So, I built a query to pull the abtements that used paint and powerwash materials as follows:
SELECT abatement_tbl.abateID, materialsused_tbl.materialusedCodeID, abatement_tbl.abateDate, IIf([materialusedCodeID]=5 Or [materialusedCodeID]=4 Or [materialusedCodeID]=8,"PW","PT") AS thetype
FROM abatement_tbl INNER JOIN materialsused_tbl ON abatement_tbl.abateID = materialsused_tbl.materialsusedAbateID
WHERE (((materialsused_tbl.materialusedCodeID)=1 Or (materialsused_tbl.materialusedCodeID)=2 Or (materialsused_tbl.materialusedCodeID)=6 Or (materialsused_tbl.materialusedCodeID)=5 Or (materialsused_tbl.materialusedCodeID)=4 Or (materialsused_tbl.materialusedCodeID)=8));
The field "thetype" will = "PW" if the abatement had materials used in a power wash and "PT" if paint materials were used in the abatement. This query works fine.
My test records contain an abatement that has 2 materialsused table records with materialusedCodeID=6. This happened because the materialsused table lists the quanitiy used of each material by unit of measurement. This abatement used 3 gallons of paint as well as 1 tube of paint.
When I take the next step to count the number of powerwash abatements - I do not want to count this abatement twice because both of the materialused records were paint. I do want to count the abatement twice if it was a paint and a power wash.
What can I do to determine if I have mulitple materialused records of the same type for 1 abatement?
I have 2 tables - abatements and materialsused. They are related by abateID.
I need to count the number of paint and powerwash abatements by month.
You can identify paint and power wash abatements by the materials used (materialcodeID in the materialsused table).
I wanted to attack the solution is steps. Step 1 is to build a query to pull the abatements and identify which used power wash materials and which abatements used paint materials. Step 2 is to figure out how to count the abatements.
So, I built a query to pull the abtements that used paint and powerwash materials as follows:
SELECT abatement_tbl.abateID, materialsused_tbl.materialusedCodeID, abatement_tbl.abateDate, IIf([materialusedCodeID]=5 Or [materialusedCodeID]=4 Or [materialusedCodeID]=8,"PW","PT") AS thetype
FROM abatement_tbl INNER JOIN materialsused_tbl ON abatement_tbl.abateID = materialsused_tbl.materialsusedAbateID
WHERE (((materialsused_tbl.materialusedCodeID)=1 Or (materialsused_tbl.materialusedCodeID)=2 Or (materialsused_tbl.materialusedCodeID)=6 Or (materialsused_tbl.materialusedCodeID)=5 Or (materialsused_tbl.materialusedCodeID)=4 Or (materialsused_tbl.materialusedCodeID)=8));
The field "thetype" will = "PW" if the abatement had materials used in a power wash and "PT" if paint materials were used in the abatement. This query works fine.
My test records contain an abatement that has 2 materialsused table records with materialusedCodeID=6. This happened because the materialsused table lists the quanitiy used of each material by unit of measurement. This abatement used 3 gallons of paint as well as 1 tube of paint.
When I take the next step to count the number of powerwash abatements - I do not want to count this abatement twice because both of the materialused records were paint. I do want to count the abatement twice if it was a paint and a power wash.
What can I do to determine if I have mulitple materialused records of the same type for 1 abatement?
