Query within a query

[Barra44]

I have 2 tables One to Many
WeekSummary to PayrateZone

I have created 2 simple select querys for each table.

I want to select the first record using query1 followed by query 2 showing the related records.

linked by WeekSummary.clkno and PayrateZone.clocknumber

EG Q1 first record 1.................
Q2 record..........
Q2 record........

Q1 next record....................
Q2 record...........
Q2 record...........
Q3 record..........

These are the 2 select queries I have

Can some one please put these together if possible to acheive the output as described above

Query 1

SELECT WeekSummary.clkno, WeekSummary.norm, WeekSummary.[1-5x], WeekSummary.[2x], WeekSummary.brk, WeekSummary.sl, WeekSummary.al, WeekSummary.wc, WeekSummary.ls, WeekSummary.pl, WeekSummary.PIA
FROM WeekSummary;

Query 2

SELECT PayrateZone.clocknumber, Sum(PayrateZone.singlet) AS SumOfsinglet, Sum(PayrateZone.timehalf) AS SumOftimehalf, Sum(PayrateZone.doublet) AS SumOfdoublet, PayrateZone.payrate
FROM PayrateZone
GROUP BY PayrateZone.clocknumber, PayrateZone.payrate;



many thanks in advance

 

[Clipper2000]

Barra44, this is very simple question, but here you go:

SELECT

WeekSummary.clkno, WeekSummary.norm, WeekSummary.[1-5x], WeekSummary.[2x], WeekSummary.brk, WeekSummary.sl, WeekSummary.al, WeekSummary.wc, WeekSummary.ls, WeekSummary.pl, WeekSummary.PIA, PayrateZone.clocknumber, Sum(PayrateZone.singlet) AS SumOfsinglet, Sum(PayrateZone.timehalf) AS SumOftimehalf, Sum(PayrateZone.doublet) AS SumOfdoublet, PayrateZone.payrate

FROM

WeekSummary INNER JOIN PayrateZone ON WeekSummary.clkno and PayrateZone.clocknumber

GROUP BY

WeekSummary.clkno, WeekSummary.norm, WeekSummary.[1-5x], WeekSummary.[2x], WeekSummary.brk, WeekSummary.sl, WeekSummary.al, WeekSummary.wc, WeekSummary.ls, WeekSummary.pl, WeekSummary.PIA, PayrateZone.clocknumber, PayrateZone.payrate

 

[Barra44]

I pasted your sript into a query, but when I run it- came up with a message - join expresion not supported.

and the WeekSummary.clkno in the from line was highlighted.

FROM

WeekSummary INNER JOIN PayrateZone ON WeekSummary.clkno and PayrateZone.clocknumber

 

[Clipper2000]

Try LEFT JOIN instead of INNER JOIN

 

[PHV]

ON WeekSummary.clkno [highlight]=[/highlight] PayrateZone.clocknumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Barra44]

That made the difference thank you very much