Query Question

[sturner333]

This seems like a simple thing but I can not figure it out. I want to combine 2 tables based on a field. The field is week of the year.The first table is just a static table that has a list of week numbers, 0 to 52, and the start and end dates for the week number.The second table has the week numbers field, 0 to 52, and other fields. I filter table 2 based on a user field. I would like the end result to be all of table 1 with the filtered table 2 information inserted in the matching weeks. The problem is the weeks filtered out from table 2 because it is not the correct user, make the final query skip those weeks altogether. The purpose of the static table is place holders for the final table so there is an entry for each week. My join properties is all table 1 plus matching table 2 data.

Here is the SQL:
SELECT Weeks.Week, Weeks.StartDate, Weeks.EndDate, TimeLogEntered.Engr, TimeLogEntered.DateEntered, TimeLogEntered.Entered

FROM Weeks LEFT JOIN TimeLogEntered ON Weeks.Week = TimeLogEntered.Week

WHERE (((Weeks.EndDate)<Now()) AND ((TimeLogEntered.Engr)=[Forms]![TimeLog]![Engineer] Or (TimeLogEntered.Engr) Is Null))


Table 1 Table 2
week week user
1 1 Bob
2 2 Bob
3 1 John
4 2 John
5 3 John

So the result should be when filtering table 2 for Bob:
1 Bob
2 Bob
3
4
5

 

[sdk]

hi

If you want only the full list of weeks, and only those entries that match weeks for your crtiteria, use an outer join.

Dbl-click the link between the two tables in the qry design, then select the option that returns all values in Weeks and matching values in TimeLoggedOn. Also remove the Null constraint from TimeLogEntered.Engr.

fyi: DatePart(interval, date[,firstdayofweek[, firstweekofyear]])
is a function that might be of interest to you.

Cheers

S

 

[PHV]

SELECT W.Week, W.StartDate, W.EndDate, T.Engr, T.DateEntered, T.Entered
FROM Weeks AS W LEFT JOIN (
SELECT * FROM TimeLogEntered WHERE Engr=[Forms]![TimeLog]![Engineer] Or Engr Is Null
) AS T ON W.Week = T.Week
WHERE W.EndDate<Now()

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