New Quick Tutorial - Exclude Data using a table

[boblarson]

Don't know if anyone is interested but sometimes it is helpful if you have a table of data (for example, where I have a long list of investors who should not show up in my queries and I don't want to try typing a very long list in a query's IN statement).

So, I created a "Quick Tutorial" to show how easy it is to use a table to exclude data from a query.

See it here.

 

[PNGBill]

Thanks Bob,
On another forum (not access) we can click to add our name to a Thanks Post which says "thanks for the info, it has been helpful", without growing the thread.

 

[boblarson]

Thanks Bob,
On another forum (not access) we can click to add our name to a Thanks Post which says "thanks for the info, it has been helpful", without growing the thread.

Yeah, I've seen some like that. But it just isn't set up that way here. Oh well.

 

[Loranga]

Hi bob,

Right now this is a little bit to complicated for me but I just wanted to say I think it's great you used the northwind databse for this example. This make it alot easier to follow. I have bookmarked your site for further reading.
My regards from a fellow viking (Larsson being a common name here in Sweden )

 

[Petros]

Don't know if anyone is interested but sometimes it is helpful if you have a table of data (for example, where I have a long list of investors who should not show up in my queries and I don't want to try typing a very long list in a query's IN statement).

So, I created a "Quick Tutorial" to show how easy it is to use a table to exclude data from a query.

See it here.

would it be possible to use this method to also exclude dater-anges from a query?..lets say i dont want to inlcude records "older" than 1 year to be included in my query..this to return higher performance...

Thanks!

 

[boblarson]

That wouldn't be necessary as you can have your criteria just be

> DateAdd("y", -1, Date())

 

[Petros]

I understand, thanks!

 

[mebaria]

Thank you for the link and showing how to do a querry. It helped me to do some reports. I tried to filter before and it does not work, it says something like the data requested is to big to be filter. This way is a lot easier to get only the info I want in the report.

Thank you !!

 

[Megan]

Nice, clear tutorials you have there. I subscribed and I'm looking forward to learning more. Thank you!

 

[qurawe]

thanksssssss

 

[qurawe]

thanksssss

 

[liddlem]

Thanks Bob
In effect, by putting in the "IsNull", you are saying "Show me all the records that DONT exist in this table". Very interesting indeed.

I was recently asked to extract data based on what is NOT known and i simply couldn't get me head around it. I suspect that this is the solution.

 

[boblarson]

Thanks Bob
In effect, by putting in the "IsNull", you are saying "Show me all the records that DONT exist in this table". Very interesting indeed.

I was recently asked to extract data based on what is NOT known and i simply couldn't get me head around it. I suspect that this is the solution.

I'm glad that it was helpful.

 

[Rx_]

Great example, I have sent it to several people. Thank you.

After using your example to create a query, I often use Union Query.
In this example, a location status can be "Requested" through "Final".
One table keeps all transactions along each status change.
The first query "-Final" simply filters on all the Final status.
The second query "-Req" filters on the Requested status - then is joined to exclude the Requested sites that were upgraded to "Final".
The second Requested query uses the same method in your demonstration.

Now that there are two queries with the same field names / field types it is easy to Union them (see attachment).
The top "-Fin-Req" Union Query shows all Finals, and only the Requested that have not been elevated to Final status.

This might be useful to anyone attempting to create their first Union query.

 

[racolunga]

Thank you boblarson it's a very helpful tutorial.

 

[Kelly@AllenEnt]

This was great. Thank you for the useful information.

 

[MichalB]

That is brilliant, just what I need! Thanks!

 

[IAmNotABot]

Awesome tutorials! Site bookmarked

 

[trkl]

plz punish me bob.i posted wrong.just to increase my post count to 10.i wont tell you why did u punish me?

 

[Sarabjit]

Hello Boblarson,I am new to access. I am creating a student database. I have crated a single table for this database and a form. I need help with two combo boxes. I have created these two combo boxes named (Documents Submitted and Documents pending). Thease two boxes I created using "Lookup coloumn". I selected "I will type in the values that I want" for both the boxes. After I filled the values in both as (CV, Ielts, Passport Copy, Certificates, photo id). After this I gave the name and selected "Allow multiple values". Now in my form it shows both the combo boxes and in both the boxes it shows CV, Ielts etc. I can select multiple Items in both the boxes. I hope I am clear up to here.
Now at this point these two boxes (Document Submitted and Documents pending) are showing items (documents names) separately. What I want is to combine the values of both. I want to do is, if I select "CV, IELTS, passport copy" in document submitted box, rest of the pending names (Photo ID, Certificates) should automatically be filled in Document pending box. What ever I didnt select in Document submitted box should appear in Document pending box.
Can you help me in accomplish this. Please guide me through the process how I can do that. I shall be very thankful to you.

For better understanding i am attaching my file with it. Please check it and let me know.


cheers
Sarab