Counting the sum of related records for a primary record

  • Thread starter Thread starter GLT
  • Start date Start date
G

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
 
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;
 
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.
 
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;
 
Thanks Karl it works great!! Cheers

KARL DEWEY said:
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;
Click to expand...
 
Back
Top