Compare and Append if No Data

[sxschech]

Hi, was trying to figure a way to append records to a table if there were none for the current week. I created two queries,
Curweek
[tt]SELECT tblApplicants.*
FROM tblApplicants
WHERE (((tblApplicants.Report_Date) In (select Max(Report_Date) from tblApplicants)) AND ((tblApplicants.SType)="G") AND ((tblApplicants.App_Status)="Applied"));
[/tt]
Prevweek
[tt]
SELECT tblApplicants.*
FROM tblApplicants
WHERE (((tblApplicants.Report_Date) In (select Max(Report_Date)-7 from tblApplicants)) AND ((tblApplicants.SType)="G") AND ((tblApplicants.App_Status)="Applied"));
[/tt]

I tried joining the two, but not sure how best to do it. What I'm looking for is all DIV and Major from Prevweek where the CurWeek is null, then I'd like to take those results and append to the table, in this way, the data will be consistent from week to week. Data are exported to excel via crosstab and by adding missing elements, will be able to easily copy paste the whole block rather than having to do so by piecemeal, or so the thought goes.

Abbreviated table data for example:
tblApplicants
[tt]
STYPE SEM Report_Date DIV Maj App_Status AppCount
G M 4/14/2006 BN MB Applied 10
G M 4/14/2006 BN MB Cancelled 1
..
G M 4/14/2006 NS NU Applied 50
G M 4/14/2006 NS NU Cancelled 3
..
G M 4/21/2006 NS NU Applied 54
G M 4/21/2006 NS NU Cancelled 5
..
[/tt]

Using this example, the query should identify that there were no BN MB data for the week of 21st and should add these records setting the counts to 0.

 

[PHV]

something like this ?
INSERT INTO tblApplicants (SType, SEM, Report_Date, DIV, Maj, App_Status, AppCount)
SELECT A.SType, A.SEM, A.Report_Date+7, A.DIV, A.Maj, App_Status, 0
FROM Prevweek A LEFT JOIN Curweek B ON A.DIV = B.DIV AND A.Maj = B.Maj
WHERE B.DIV Is Null

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

 

[sxschech]

PHV probably didn't give enough data...If there will be for example, DIV=BN Maj=XY in Curweek, then DIV won't be null, also need to match on SEM as well since we have 3 different SEM codes because there are may be a Maj in SEM=M but not in SEM=S, so wouldn't want to add the extra records in that case.

 

[PHV]

FROM Prevweek A LEFT JOIN Curweek B ON A.DIV = B.DIV AND A.Maj = B.Maj AND A.SEM = B.SEM

DIV=BN Maj=XY in Curweek, then DIV won't be null
Wrong assertion, due how OUTER joins work.

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

 

[MichaelRed]

look into using an UPDATE query. It will do an "Append" of "Missing" records, so you just need to join the new and old in a manner which identifies the new. Similar to joining the two .




MichaelRed