Count subrecords from record

[edwin]

I'm creating a database (information has been entered already) where I
want to count some subrecords linked by a record. An example:

TABLE1:
ID | TITLE
1 | TEST
2 | TEST2

TABLE2:
ID | PID | TITLE | SUBTOTAL
1 | 1 | 1a | 50
2 | 1 | 1b | 65
3 | 1 | 1c | 15
4 | 1 | 2a | 54
5 | 1 | 2b | 13
6 | 2 | 1a | 24
7 | 2 | 5a | 35
8 | 2 | 5b | 25
9 | 2 | 7 | 15

Okay, TABLE2.PID is linked to TABLE1.ID.
Now, my problem is: I want to count TABLE2.SUBTOTAL together, WHERE
TABLE2.TITLE starts with the same number (TABLE2.TITLE is always starts
with a number) AND WHERE PID is the same (for example, only WHERE PID =
1).
So, when executed, PID = 1, TABEL2.TOTAL = 130 (1a + 1b + 1c WHERE PID
= 1).

And, while I'm busy, I also want to know how much items there are with
PID = 1 AND TABLE2.TITEL.items starting with the same number. So, when
executed, PID = 1, there a 3 items starting with the same number 1, 2
items starting with the same number 2, etc.

I hope this is clear.

Thanks in advance,

Edwin Siebel

[Johnny Meredith]

Try this. Let me know and I can email you the db I created testing
this:

SELECT T1.ID, Left([T2]![Title],1) AS TitleBeginningCharacter,
Count(T2.ID) AS CountOfID, Sum(T2.SUBTOTAL) AS SumOfSUBTOTAL FROM
Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.ID = T2.PID GROUP BY T1.ID,
Left([T2]![Title],1);

You can add a HAVING clause before the semi-colon to return just one
PID at a time:

....HAVING T1.ID = 1

Thanks,
Johnny

[heppie.nl]

Johnny Meredith wrote:

Try this. Let me know and I can email you the db I created testing
this:

SELECT T1.ID, Left([T2]![Title],1) AS TitleBeginningCharacter,
Count(T2.ID) AS CountOfID, Sum(T2.SUBTOTAL) AS SumOfSUBTOTAL FROM
Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.ID = T2.PID GROUP BY T1.ID,
Left([T2]![Title],1);

You can add a HAVING clause before the semi-colon to return just one
PID at a time:

...HAVING T1.ID = 1

Thanks,
Johnny

Excellent Johnny, although I haven't tested it yet, but thanks. I've
been struggling with this one for a while now. If possible, I'm
interessed in the db, you can just send it to
edwin@heppie*removethis*.nl (without the *removethis*).

Thanks!

Edwin

[heppie.nl]

Johnny Meredith wrote:

Try this. Let me know and I can email you the db I created testing
this:

SELECT T1.ID, Left([T2]![Title],1) AS TitleBeginningCharacter,
Count(T2.ID) AS CountOfID, Sum(T2.SUBTOTAL) AS SumOfSUBTOTAL FROM
Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.ID = T2.PID GROUP BY T1.ID,
Left([T2]![Title],1);

You can add a HAVING clause before the semi-colon to return just one
PID at a time:

...HAVING T1.ID = 1

Thanks,
Johnny

Excellent Johnny, although I haven't tested it yet, but thanks. I've
been struggling with this one for a while now. If possible, I'm
interessed in the db, you can just send it to
edwin@heppie*removethis*.nl (without the *removethis*).

Thanks!

Edwin

[heppie.nl]

Johnny, it works perfect!!! Absolutely wicked.

One more question: do you know how to get another column (for example
name "AVG") with SumOfSUBTOTAL / CountOfID? I tried to do it in an
EXPRESSION, but it didnt' work.

Thanks

[Johnny Meredith]

I'm out of the office until Tuesday. I'll send you the db and answer
your question at that time.

heppie.nl wrote:

Johnny, it works perfect!!! Absolutely wicked.

One more question: do you know how to get another column (for example
name "AVG") with SumOfSUBTOTAL / CountOfID? I tried to do it in an
EXPRESSION, but it didnt' work.

Thanks

[heppie.nl]

Johnny, it works, also the AVG, so no absolute need of sending the
database.

Thanks, I owe you a beer :)