compare two tables, create query from lowest to highest value found

[RECrerar]

Hi,

I'm trying to create a query that combines the predicted spend that is stored on one table with the actual spend that is stored on another for all weeks for which data is available in either table. The problem is that the duration for which data is available is often not the same on both tables so if I use the week column form one table to collect the data often some data from the other table will be missing.

The tables are:
tblApprovedPT tblRawTabs
ProjectYearID ProjectYearID
Week Period (This is the equivalent to week)
Cost Hours (the cost is converted to hours)

tblAnnualPT
Req_Total
Hours_Total This table is use to convert the cost to hours

The SQL I am using for my query at the moment is below, it is a make tabel query. The Having statement limits the query to the sepected ProjectYear ID on the form that calls it.

SELECT Sum(nz(([Cost]/tblAnnualPT!Req_Total)*[Hours_Total])) AS ProjectedHours, Sum(nz(DLookUp("[SumOfHours]","tblRawTabs","[Period]=" & [Week] & "AND tblRawTabs.[ProjectYearID] = '" & tblApproved_PT_by_week.ProjectYearID & "'"),0)) AS WeeklyTabs, tblApproved_PT_by_week.Week, tblApproved_PT_by_week.ProjectYearID INTO tblTempReportGen
FROM tblAnnualPT INNER JOIN tblApproved_PT_by_week ON tblAnnualPT.ProjectYearID = tblApproved_PT_by_week.ProjectYearID
GROUP BY tblApproved_PT_by_week.Week, tblApproved_PT_by_week.ProjectYearID
HAVING (((tblApproved_PT_by_week.ProjectYearID)=[Forms]![frmTempReportGenInfo]![IDList]));

This though limits my data to the range of weeks in tblApproved_PT_by_Week, how can I get it to look at both tables to find the lowest and highest week numbers and hence give me data for the whole range?

Hope this make sense.

Regards, Robyn

 

[RECrerar]

Just to let you know I have found a way to do this by creating another table continaing just fields for Week and ProjectYearID and then populating the weeks for this using VBA and using this week field in the query.

The codee I used is shown below. I would still be interested in knowing any alternative methods of doing this. Regards, Robyn

Dim FirstTabsWeek, FirstProWeek, FirstWeek, LastWeek
        FirstTabsWeek = (DMin("[Period]", "tblRawTabs", "[ProjectYearID]='" & Me.[IDList] & "'"))
        FirstProWeek = (DMin("[Week]", "tblApproved_PT_by_week", "[ProjectYearID]=" & Me.[IDList]))
        If FirstTabsWeek <= FirstProWeek Then
            FirstWeek = FirstTabsWeek
        Else
            FirstWeek = FirstProWeek
        End If
        LastWeek = CurrentWeek(Now())
        Dim rcdset As DAO.recordset
        Set rcdset = CurrentDb.OpenRecordset("tblTempWeek", dbOpenDynaset)
        For i = CInt(FirstWeek) To CInt(LastWeek)
 
            If Right(i, 2) = 52 Then 'Cos there are only 51 weeks in our work year
                i = i + 49
            End If
 
            rcdset.AddNew
            rcdset("Week").Value = i
            rcdset("PY_ID").Value = CInt(Me![IDList]) 'Contains the ProjectYearID of interest
            rcdset.Update
        Next i
        rcdset.Close
        Set rcdset = Nothing