Query Criteria using a table

[greenfalcon]

Im really stumped.. and I really need some help, Here is my problem,

I have a field with a whole bunch of regular peoples names in it (user_names). Home Developer names are also in this field, it has to be this way to show if the home is still owned by the developer or moved into. What im trying to do is build a query that takes out all of the developer names (by takes out i mean does not change the table at all, just temp so i can perform some calculations on the data).. Im having a problem with this. I have a developer_name table that looks like this

Autonumber Developer
1 Walts
2 Peets
3 Donalds

When i set up my query under field i put user_names and under criteria i have used all variations i can think of pertaining the following code

<>[DeveloperInfo]![DevName]

from what I understand it should return all values that are not developer homes, it doesnt work though, it leaves some in there. if i change the code from <> to = it takes out all the user names and leaves just the Developer names as it should, unfortunatly i need it to take out the developer names not leave them in.... Any ideas?

Thanks

Greeny

 

[KenHigg]

I would think you simply bring down the dev name and set the criteria to 'is null'.

???

 

[Michael J Ross]

I think you need an extra field in your table that holds user names to specify whether the users are owners or developers then you could query this field using <> "Developer"

 

[greenfalcon]

Kenhigg what do you mean by "bring down the dev name"? I need it to be dynamic.

I hope I don't need an extra field, I would have thought this possible when the code works when i run it one way and mostly the other way. Here is the funny thing, lets say there are 5000 users and 2000 developers if i run the code it leaves 5000 users and 1 developer left in the query... I cant have this but it is wierd behavior... any other ideas?

Thanks

 

[greenfalcon]

I hope someone is able to help me with this problem, mabye it would help if i attached a sample database with two queries to better explain my problem...

Green

 

[KenHigg]

Sure - Try to upload a sample db. I will not be able to look at until wed as I have dial up here at home

 

[greenfalcon]

I will make it really really tiny

 

[greenfalcon]

Ok, here is the small sample database, i made it real quick but it shows what im trying to do, you can look at the two tables im trying to filter out the developer names from the tblOccupants, I am trying to do this by having a query look up the values in tblDeveloperInfo and just not show these developers in the query. Mabye someone can fool around with it... it cant be that hard...

If you look at the query "Wierd_Behavior" I tried to write a small statement showing when it knows it has found a developer.. it works half way... it finds a developer but then creates a duplicate and does not catch that one....

Thanks!

Greeny

P.S. I made it small enough even your 56k will download it in under 2 seconds

 

[john471]

You need to define a join between the two tables, to tell Access how the information in one table relates to the information in the other table. Adding two tables to a query without a join results in a multiplying effect - aka a "cartesian product" or "cross product" (every combination of records between the two tables).

For what you want, you need an "Outer Join" between the two fileds containing the names. When Ken said "bring down the Developer names" I believe he meant add the developer table to the query (which you had already done - but you hadn't defined the join). Once you have defined the uter join you select the Occupant names and add the IsNull condition to the Developer Names. This will result in all occupant names that are not also present tin the Developer names.

Read Access on-lne help on Joins, particularly "Outer Join", for more information.

See pic.

HTH

Regards

John.

 

[greenfalcon]

Thank you John471 I will try this out I hope my small (demo) Db was at least fun to work on, i tried to add some unusual names, ill let you know if i get it working.

Much Appriciated

Jason