Group By

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have two tables.
table1
--------
spindex(autonumber PK) job_code layno yd wd

table2
--------
subindex(autonumber pk) spindex(fk) rollno batchno color_code

I want a query to retrieve layno and norecords related to that layno in
second table.

I input job_code.

Means, table1 may contain
spindex job layno yd wd
1 04A001 1 20 30
2 04A001 2 19 28
3 04A001 3 25 32
4 04B001 1 10 20
5 04B001 2 15 25

table2 may contain
subindex spindex rollno batchno colorcode
1 1 560 ABCD 10
2 1 230 JHH 11
3 1 234 KJHK 10
4 1 2354 JHG 12
5 2 654 ETLK 10
6 2 987 DKI 13
7 3 34 LKJ 10


The output could be...(input job_code)
layno totrec
--------------
1 4
2 3


Can you give me the query?
 
Dear Rajani:

For layno = 1 in table 1 I see spindex values 1 and 4. Joining to
these in table2 I see 4 rows where spindex = 1 and no rows where it is
4, so the total count of 4 makes sense.

For layno = 2 in table1 I see spindex values 2 and 5. There are 2
rows in table2 for spindex = 2 but no rows for spindex = 5. So I
would think the result is 2, but your results say 3. This leads me to
wonder if I understand what you want. But here's the query for my
possibly wrong results:

SELECT T1.layno, COUNT(*) AS totrec
FROM table1 T1
INNER JOIN table2 T2 ON T2.spindex = T1.spindex
GROUP BY T1.layno

I'm perfectly willing to admit this may have nothing to do with what
you want, but I'm not getting any more than this from what I can read.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top