Counting the sum of related records for a primary record

[GLT]

Hi,

I am trying to work out how to calculate the number of records in a related
table for each record in the primary table, and record those results in a
third table.

Table A has a one-to-many relationship with Table-B.

What I am trying to do is have a third (Table C), and i would like Table C
to contain a one-to-one relationship with Table-A, and appart from the linked
keys, Table-C will only have one feild which is a number. This number will
be the SUM or COUNT of the related records in Table B.

Any assistance would be greatly appreciated.

Cheers,
GLT

 

[KARL DEWEY]

Try this --
SELECT Table1.HRID, Count(Table2.HRID) AS CountOfHRID INTO Table3
FROM Table1 INNER JOIN Table2 ON Table1.HRID = Table2.HRID
GROUP BY Table1.HRID;

 

[GLT]

Hey Karl,

You’re a champion - works perfectly...

I wanted to know if the following might be possible - if it’s too
complicated then please advise...

I was just looking at the data in Table B (table B has a list of errors
related to each record in table A). For example, record # 1 may have 5
errors associated with it in table B.

However, some of the errors in (Table B) we ignore, and some we don’t. So I
would like to flag certain related records in Table B by perhaps a yes/no
field, and if any fields are flagged as 'Yes' then they are excluded from the
count calculated above....

Is this type of calculation possible via SQL?

Cheers,
GLT.

 

[KARL DEWEY]

This should do it --
SELECT TableA.PrimaryKey, Abs(Sum(TableB.Flag)) AS ErrorCount
FROM TableA LEFT JOIN TableB ON TableA.PrimaryKey = TableB.ForeignKey
GROUP BY TableA.PrimaryKey;

 

[GLT]

Thanks Karl it works great!! Cheers

This should do it --
SELECT TableA.PrimaryKey, Abs(Sum(TableB.Flag)) AS ErrorCount
FROM TableA LEFT JOIN TableB ON TableA.PrimaryKey = TableB.ForeignKey
GROUP BY TableA.PrimaryKey;