Hi Guys,

New to Access so please be gentle. I started a dummy project to teach myself (using Dummies Guide) but threw myself in at the deepend so I think what I'm trying to do is beyond basic!

Right, I have several tables but the main one of interests is called "TblListEmployees". This has a lot of info in it but the main field of interest is "Source_PNel_#" which is the employee unique ID.

I have several other tables in which people are assigned work, on leave etc.

I have two queries which calculate from some of the other tables:

1) "QryAbsent" - this looks at each entry in a table which records employees leave start and end dates. Where the planned absence is current (i.e. today's date falls in the range of leave) it puts the employees unique ID (PNel_#) into a field called "Absent" - this all works fine.

2) "QryOn Assign" - this looks at each entry in a table which records assigned work. Where the assigned work is current (i.e. today's date falls in the range of assignment) it puts the employees unique ID (PNel_#) into a field called "Assigned" - this also works fine.

Now the challenge.

I want to create another query, called, lets say "AvailForWork". This should list all employees in the "TblListEmployees" table, except where their personnel numbers appear in [QryOnAssign]![Assigned] or [QryAbsent]![Absent]

I've spent most of today fiddling, fruitlessly scouring the web and I'm loosing the will to live! Any help or pointer would really be appreciated.

Thanks,

JW191.

Welcome to the site. Play with the Unmatched query wizard. That will let you do one of them, and you should be able to manually add the second once you see how the first works.

Welcome to the site. Play with the Unmatched query wizard. That will let you do one of them, and you should be able to manually add the second once you see how the first works.

I've spent some time playing around with unmatched queries but have not been successful - I just get an empty table.

Heres my tables and queries:

TblListEmployees is source data:

TblListEmployees
Source_PNel_# ................First_Name
Emp1 ..............................Engineer
Emp2 ..............................Engineer
Emp3 ..............................Engineer
Emp4 ..............................Bob

Heres my OnAssign query. The Assigned column shows the employees ID if the assigment is live.
QryOnAssign
Assign_ID .....Assign_PNel_# .........Next_Available ...........................Assigned
5 ..................Emp1 .......................20/05/2008 17:00:00
6 ..................Emp2 .......................22/05/2008 17:00:00 ...................Emp2
7 ..................Emp3 .......................22/05/2008 17:00:00 ...................Emp3

And the query generated by unmatched wizard:

Field ...........Source_PNel_# ..................Assigned
Table ..........TblListEmployees ............QryOnAssign
Sort
Show .................Tick ..............................Tick
Criteria .................................................. .Is Null
Or

The resulting output is empty.

Any ideas?

As you are new to the game, and new members are always welcome, here is a couple of pointers that you need to condiser at the onset, which will make life better in the future.

First do not use # as part of a field name
Avoid using spaces in field names
prefix field names with 'fld'
Do not used reserved words for field names
Captitalise fldFieldNamesWithoutSpaces for easier reading

I could go on and on but to much at first is daunting.

CodeMaster

As you are new to the game, and new members are always welcome, here is a couple of pointers that you need to condiser at the onset, which will make life better in the future.

First do not use # as part of a field name
Avoid using spaces in field names
prefix field names with 'fld'
Do not used reserved words for field names
Captitalise fldFieldNamesWithoutSpaces for easier reading

I could go on and on but to much at first is daunting.

CodeMaster

Is it possible that my naming conventions are causing my current issues, or are you offering helpful advice? I was aware of the best practice to avoid spaces so I used underscore, and also to avoid reserved words. Fair point on the #, perhaps not the best thing to include in a field name.

Hi

What I did notice in your same data is that you record date and time. Does your query also refer to date and time?

Also design one of your working queries and save under a different name. Then change the condition from equals to not equals ("=" to "<>") and add a further condition relating to the other type. such as

<> "Absent" or <> "Assigned"

So if they are not absent and not assigned then they must me available.

David

OK Guys, a colleague helped me get this working. I coded the query in SQL:

SELECT *
FROM TblListEmployees
WHERE [Source_PNel_#] not in (SELECT Assigned FROM [QryOnAssign]);

Hi JW,

Welcome to the forum.

That technique is interesting. I think I'll be pinching that one :)

If you can post a sample db I'll try to figure out the SQL from the unmatched wizard. Your SQL will work for one but still has to be modified to exclude the other, and the subquery method will be noticeably less efficient on larger datasets than the join method would be.