New Quick Tutorial - Exclude Data using a table (1 Viewer)

boblarson

Smeghead
Local time
Today, 03:59
Joined
Jan 12, 2001
Messages
32,059
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

Win10 Office Pro 2016
Local time
Today, 23:59
Joined
Jul 15, 2008
Messages
2,271
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

Smeghead
Local time
Today, 03:59
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 03:59
Joined
Sep 14, 2010
Messages
13
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

Registered User.
Local time
Today, 11:59
Joined
Jun 30, 2010
Messages
145
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

Smeghead
Local time
Today, 03:59
Joined
Jan 12, 2001
Messages
32,059
That wouldn't be necessary as you can have your criteria just be

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

mebaria

New member
Local time
Today, 13:59
Joined
Oct 8, 2010
Messages
9
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

Registered User.
Local time
Today, 06:59
Joined
Jun 3, 2011
Messages
23
Nice, clear tutorials you have there. I subscribed and I'm looking forward to learning more. Thank you!
 

liddlem

Registered User.
Local time
Today, 10:59
Joined
May 16, 2003
Messages
339
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

Smeghead
Local time
Today, 03:59
Joined
Jan 12, 2001
Messages
32,059
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_

Nothing In Moderation
Local time
Today, 04:59
Joined
Oct 22, 2009
Messages
2,803
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.
 

Attachments

  • Union Query Final Requested.gif
    Union Query Final Requested.gif
    90.7 KB · Views: 1,127

trkl

Registered User.
Local time
Today, 14:59
Joined
Jun 30, 2013
Messages
31
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

New member
Local time
Today, 23:59
Joined
Aug 17, 2013
Messages
3
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
 

Attachments

  • Student Database.accdb
    1.7 MB · Views: 856

Users who are viewing this thread

Top Bottom