Query Showing Non-Assigned

[Flash76]

I am not very good at queries and I need help. I need a query showing all techs not assigned on a particular date. So for example I would run the query from a form saying show all techs available on 2/1/2011. Below is my table structure with the assignments being done in the first table. Thanks

tbl_tech_assign_TL
Assignment_id
Tech_Name
Team_Leader
Assign_Date
Assignment_details

tbl_team_leader
TLID
Team_Leader_Name
Team_Leader_Email
Team_Leader_Phone
Team_Leader_Cell

tbltech
TechID
Tech_Name
Tech_Email
Tech_Fax
Assigned

[TheShabz]

First off, in your table "tbl_tech_assign_T, replace Tech_Name with TechID as it defeats the purpose of having a Unique Identifier if you dont use it.

your query would then be

SELECT tbltech.Tech_Name,
FROM tbltech LEFT JOIN tbl_tech_assign_TL ON tbltech.TechID = tbl_tech_assign_TL.TechID
WHERE tbl_tech_assign_TL.techID is null AND tbl_tech_assign_TL= [Enter Date to check]

Note, the [Enter Date to check] will create a dialog box where you enter a date in mm/dd/yyyy format

[Flash76]

Thanks for the reply below is what I have I had to add "assign_date" for entering a date but I am not receiving any results for dates techs are not assigned?

SELECT tbltech.Tech_Name
FROM tbltech LEFT JOIN tbl_tech_assign_TL ON tbltech.TechID = tbl_tech_assign_TL.TechID
WHERE tbl_tech_assign_TL.techID Is Null AND tbl_tech_assign_TL.Assign_Date = [Enter Date to check];

[TheShabz]

Did you replace the Tech_Name with TechID? If not your join won't work. You would have to join on the Tech_Names instead of the TechIDs in that case.

[Flash76]

Yes changed it to techID in tbl_tech_assign_TL.

[TheShabz]

And by change, I was referring to the data, Not just the name of the field. In any case. Try joining with the names and see if you get results.

[Flash76]

I made the date in the tbl_tech_assign_TL for TechID the TechID number. I've tried both ways now and when I run the query and enter todays date I should have one tech come up but I receive zero records. Attached is the relationship report.

[TheShabz]

The only other reason i can come up with is that the date isnt being entered in properly. Dry getting rid of the [Enter date] and just put in a date that you know should return some results. The date should have # around them, like #2/2/2011#

[Flash76]

Tried Below and still no results. I think I am missing something below what I have in there now and I 3 techs in the tbltech list not assigned on 2/2/2011.

SELECT tbltech.Tech_Name
FROM tbltech LEFT JOIN tbl_tech_assign_TL ON tbltech.TechID=tbl_tech_assign_TL.TechID
WHERE tbl_tech_assign_TL.techID Is Null And tbl_tech_assign_TL.Assign_Date = #2/2/2011#;

[TheShabz]

it should work. I have no clue why its not. =/

[Flash76]

It must have something to do with the Is Null command in the last line if I change that to Is Not Null I get all techs assigned on that day so Is Null is not working. Just food for thought while I mess around with it.

[TheShabz]

The dataset without the Null reference would provide something to the effect of

TechID | TechIDassignedToTask
1 1
2
3 3
4 4
5

So to find the ones not assigned, we would want to find the ones that dont have any values, the Nulls.

[Flash76]

If I take our the "And tbl_tech_assign_TL.Assign_Date = [Enter Date]" at the end I get all techs that are not assigned as soon as it looks for the date I don't get any records even though there aren't any techs assign on the date.

[TheShabz]

Does the date exist in that table?