Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2017, 02:03 PM   #1
AccessNewb01
Newly Registered User
 
Join Date: Jul 2017
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
AccessNewb01 is on a distinguished road
Select Into Query to show what is missing

All -

I am new to this forum and relatively new to more complicated queries in SQL. I have a table with four columns. The first column contains ID's where duplicates can often occur and the other three column tell me information about the first. The fourth column in my table has identify numbers that represent a specific set up and I need a query to return the values if a particular value does not appear for the ID.

Example Data set.

ID Field 2 Field 3 Field 4
41 59 12 1
41 59 12 2
41 59 12 71
42 60 12 1
42 60 12 2
42 60 12 741


What I need is for the query to look at the ID field and Field 4. Based on the ID, I need to know if field 4 is missing a particular number for just that group of ID's.. So for this example, look through ID field and tell me if any of the ID's "by group" are missing a 71 or a 2 in Field 4. So this particular example would return ID 42 is missing a 71. (I would want Fields 2 and 3) returned as well.

I would prefer it tell me what number in Field 4 it is missing, but understand if that is not possible.

AccessNewb01 is offline   Reply With Quote
Old 07-13-2017, 02:37 PM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,717
Thanks: 10
Thanked 2,083 Times in 2,038 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Select Into Query to show what is missing

How vague is your example? I mean is the criteria always 71 & 2? Or could it change? Also, could you have ID's that have duplicate values in Field4 records (e.g. 2 records with Field4=71)?

In general (since you haven't provided a table name nor specific fields), you would create a subquery to get all the 71 & 2 records for an ID:

Code:
SELECT ID, Field4
FROM YourTableNameHere
WHERE Field4=71 OR Field4=2  
GROUP BY ID, Field4

Then you would build another query using that one. You would GROUP BY ID and COUNT Field4 and put criteira under the Field4 of >1. That would give you the exact list that had both. To get the IDs with missing ones you wold need to take that second query and bump it against a list of unique IDs you want to test. All the ones without a match in that second query would be the ones missing records.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
AccessNewb01 (07-21-2017)
Old 07-13-2017, 03:02 PM   #3
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,674
Thanks: 10
Thanked 1,266 Times in 1,205 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
I think you could also do something like...
Code:
SELECT ID, -Sum(Field4 = 71 OR Field4 = 2) As CountState
FROM YourTable
GROUP BY ID
...in which you sum the result of a boolean test, and thereby effectively count the rows where the expression is true, by ID.

And it's easy enough to subquery to only find the IDs that are not complete...
Code:
SELECT ID, CountState
FROM 
   (
   SELECT ID, -Sum(Field4 = 71 OR Field4 = 2) As CountState
   FROM YourTable
   GROUP BY ID
   )
WHERE CountState < 2
And obviously from this, there is no need for a SELECT INTO query.
hth
Mark

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
AccessNewb01 (07-21-2017)
Old 07-13-2017, 03:33 PM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,469
Thanks: 50
Thanked 1,860 Times in 1,810 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Select Into Query to show what is missing

Markk,

Very nice.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 07-13-2017, 07:37 PM   #5
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,674
Thanks: 10
Thanked 1,266 Times in 1,205 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Thanks jdraw.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 07-21-2017, 12:47 PM   #6
AccessNewb01
Newly Registered User
 
Join Date: Jul 2017
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
AccessNewb01 is on a distinguished road
Re: Select Into Query to show what is missing

This worked great, thank you to everyone that responded. I'm all set now!

AccessNewb01 is offline   Reply With Quote
Reply

Tags
access 07 , query , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query to show missing entries preveo Queries 2 10-16-2013 01:13 AM
Form to select which fields show in Query patrickm89 Queries 5 10-09-2013 02:31 AM
Select fields to show in query with a form NWTools Queries 3 02-16-2013 03:26 AM
Query to show missing records ChristopherM Queries 8 01-25-2008 06:43 AM
Select Query: Missing results due to two tables JimmyG Queries 2 09-15-2004 06:01 AM




All times are GMT -8. The time now is 09:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World