Query help!!

[spudracer]

I've got two fields in a table that I need in a query.

I've got one set to show who all is in a certain position, but I've also got another field that I want to show people who will be filling that same position, but haven't arrived yet.

So, in the end, I want the query to pull the names of all people in X position, with it also including personnel, who will fill that position, who have not arrived yet, without it returning EVERYONE who hasn't arrived yet.

I've tried all the possible ways I can think of to do it, but I've ran out of options. Could anyone shine a light for me?

 

[pbaldy]

I suspect we're going to need more info about the structure of the table(s) and perhaps what the data looks like.

 

[spudracer]

As much information that I can give on this (which isn't much) is the following.

Table structure is pretty basic, Name (First and Last), Position Title, Arrival Date

I'm using all three for the query, which isn't too hard to build a query looking for the specific info, it's when I get to the part where I need the people that aren't here yet to show up along with those that ARE here.

So, would I use an Is Null in the Or criteria?

 

[pbaldy]

Does this not work?

SELECT *
FROM TableName
WHERE Position = "X"

 

[spudracer]

See, now you're confusing me. Queries have never been my strong suit.

Here's what I have for criteria:

Position = "Human Resources" Arrival Date = ??? <--Where I'm stuck.

Again, I don't want to return just those who are already there, but I also don't want to return just those who aren't yet.

 

[pbaldy]

It sounds like you don't want any criteria at all on the date field.

 

[spudracer]

Yes, but for those that are already there, it will return their information, but not those who have not arrived yet. Since I did not design this database, I cannot add a simple Here? Yes/No box in the table to simplify my life.

 

[pbaldy]

I'm assuming that those that haven't arrived will be in the table with a date in the future. Without a criteria on date, those with past or future dates will all be returned as long as the position field matches. I'm back to asking what the data looks like and what you expect out of it. It doesn't have to be real data.

 

[boblarson]

Yes, but for those that are already there, it will return their information, but not those who have not arrived yet.

Huh????? <Big questioning look on face>

 

[spudracer]

Name Position Arrival Date
Joe Schmo Human Resources 7/1/08
John Doe Human Resources

The name and arrival date are pulled from one table, but the position is in a separate table. Both tables are linked, so that's not a problem.

 

[pbaldy]

If I run a query on your example data without a criteria on arrival date, I'll get both records. Isn't that what you want?

 

[spudracer]

I just remembered the most important part. Since the people that have not arrived yet are not in the table where the position is, they, of course, don't show up.

The table with the name and arrival date contains the information pulled off a much larger database (so information cannot be modified as far as adding columns to the table).

So John Doe's name shows up in the master table, along with everyone else's information, but when it goes for the position, he has not technically arrived yet so he's not labeled as being in that position.

I can return 15 people when not trying to include those not here yet. When I go to include those who haven't arrived, I get 4, but all are already here.

Will I simply need to enter that person's information into "table 2" where the position title is to make this easy on myself?