Crosstab query not producing expected results

Hi

I have created a unnion query that joins 4 tables. The output of this query is three fields, ie id, low and high. The low and high fields can have the following values, 1 or 0

I then joined this union query to a select query, adding a few more fields.  The ouput of this new select query looks correct. for example (not all fields shown)

id 1418 has 0 in low and 1 in high
id 1435 has 0 in low and 1 in high
id 1435 has 1 in low and 0 in high

So, id 1435 has two records. This is correct; the number one represents both high and low in the union query, and the number 0 represents any value other than one. Here is the union query, to explain.

Select AuditID, iif(RxAuditID= 2,1,0) as Low ,   iif(RxAuditID= 3,1,0) as High from Requirements

UNION ALL

Select AuditID, iif(DecisionAuditID= 2,1,0) as Low ,   iif(DecisionAuditID= 7,1,0) as High from Decision

UNION ALL

Select AuditID, iif(CommunicationAuditID= 2,1,0) as Low ,   iif(CommunicationAuditID= 3,1,0) as High from Communications

UNION ALL

Select AuditID, iif(OtherAuditID= 2,1,0) as Low ,   iif(OtherAuditID= 3,1,0) as High from Other;


What I am trying to do is count the number of 1's that are recorded against each id. So in the above example id 1435 has two 1's (one in the low field and one in the high field) and I am struggling to produce a crosstab query that will return a count of two for id 1435.

The crosstab query that produces the incorrect result is

PARAMETERS [Forms]![NullsTestReports]![AuditNumber] Short;
TRANSFORM Count(QryMoreThanOneScoredFinding_Select.High) AS CountOfhigh
SELECT QryMoreThanOneScoredFinding_Select.AuditNumber, QryMoreThanOneScoredFinding_Select.AuditID, QryMoreThanOneScoredFinding_Select.PolicyNumber, QryMoreThanOneScoredFinding_Select.LastName, QryMoreThanOneScoredFinding_Select.FirstNames, Count(QryMoreThanOneScoredFinding_Select.High) AS Total
FROM QryMoreThanOneScoredFinding_Select
GROUP BY QryMoreThanOneScoredFinding_Select.AuditNumber, QryMoreThanOneScoredFinding_Select.AuditID, QryMoreThanOneScoredFinding_Select.PolicyNumber, QryMoreThanOneScoredFinding_Select.LastName, QryMoreThanOneScoredFinding_Select.FirstNames
PIVOT QryMoreThanOneScoredFinding_Select.Low;

Please also see the gif that shows the output of the select and crosstab query

Thank you.