Is anyone an SQL guru?

[IanNav]

hi,

I was wondering if anyone could tell me how select a load pf data with joins which are linked one-2-many???

i'll probably explain better through my example

Table 1
========
Date(key) Ords Value
=========================
01/08/2001 8 550
02/08/2001 4 1070
03/08/2001 3 1150
04/08/2001 7 60
05/08/2001 1 480
06/08/2001 6 500



Table 2
========
Date(Key) Code(Key) Ords Value
====================================
01/08/2001 01 3 500
01/08/2001 03 3 100
02/08/2001 01 4 330
02/08/2001 02 5 578
02/08/2001 03 6 1245
03/08/2001 01 5 1199
03/08/2001 03 8 1999
03/08/2001 04 9 200
04/08/2001 05 3 500
04/08/2001 06 4 349
05/08/2001 01 8 564
06/08/2001 01 2 2356
06/08/2001 04 2 4506

what select do i use to get a recordset (below)

basically it needs to add the 'ords' & 'value' up in table 2 by date, then link it to table 1

Date [tab1].ords [tab1].val [tab2].ords [tab2].value
=========================================================
01/08/2001 8 550 6 600
02/08/2001 4 1070 15 2153
03/08/2001 3 1150 22 3398
04/08/2001 7 60 7 849
05/08/2001 1 480 8 564
06/08/2001 6 500 4 6862

I'm sure this is easy to do, but i'm not an SQL expert.

Thanks

Ian

 

[downwitchyobadself]

Two steps:

First, make a group-by query on Table2.

For grouping (the Totals line of the QBE)

Date(Key): Group by
Code(Key): Group by
Ords: Sum
Value: Sum

("Date", "Code", and "Value" are really bad field names, by the way, because they are reserved word for programming, meaning they have special meanings in Access, and so shouldn't also be field names. You might want to rename them OrdDate, OrdCode, OrdValue or some such. But anyway.)

Save this query, let's say it's called qryGrpOrdValsByDte.

Step 2: Create a new query. Add Table1 and qryGrpOrdValsByDte.

Join the two date lines in the window (click, draw line from Date in Table1 field list to Date in qryGrpOrdValsByDte, let go).

Add the following fields:

Table1.Date
Table1.Ords
Table1.Value
qryGrpOrdValsByDte.SumOfOrds
qryGrpOrdValsByDte.SumOfValue

Save it. That's it.

(You'll notice that the group-by changes the field names slightly, to distinguish them from their unsummed forebears.)