Query to get everyone not in table

Here's what I want to do.

I have table 1 with staff member's firstname, lastname, and id#.
I have table 2 with staff member's entry#, entrydate, and id#.
Relationship between table 1 and table 2 through id# w/ referrential 
integrity.

I want to put a query together that I would input a date and retrieve a list 
of staff member's who do not have an entry on that date.  Basically, I want 
to find out who didn't show up to the meeting on that date.

Does anyone have a solution for this?

Robin
0
Utf
1/26/2008 10:01:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
683 Views

Similar Articles

[PageSpeed] 15

rbuick wrote:
>I have table 1 with staff member's firstname, lastname, and id#.
>I have table 2 with staff member's entry#, entrydate, and id#.
>Relationship between table 1 and table 2 through id# w/ referrential 
>integrity.
>
>I want to put a query together that I would input a date and retrieve a list 
>of staff member's who do not have an entry on that date.  Basically, I want 
>to find out who didn't show up to the meeting on that date.


That's just a find unmatched records query.  The query
wizard will create one for youand you can tweak it for your
date criteria.

-- 
Marsh
MVP [MS Access]
0
Marshall
1/26/2008 10:50:05 PM
Two query solution.

Query A: WhoWasThere
SELECT Table2.[Id#]
FROM Table2
WHERE EntryDate = #1/1/2007#

QueryB is an unmatched query using queryA and Table1
SELECT Table1.[ID#], FirstName, LastName
FROM Table1 LEFT JOIN WhoWasThere
ON Table1.[ID#] = WhoWasThere.[ID#]
WHERE WhoWasThere.[ID#] is Null

You can do that all in one query
SELECT Table1.[ID#], FirstName, LastName
FROM Table1
WHERE [ID#] NOT IN
    (SELECT Table2.[Id#]
     FROM Table2
     WHERE EntryDate = #1/1/2007#)

The first option may be faster - especially with large data sets.



'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


rbuick wrote:
> Here's what I want to do.
> 
> I have table 1 with staff member's firstname, lastname, and id#.
> I have table 2 with staff member's entry#, entrydate, and id#.
> Relationship between table 1 and table 2 through id# w/ referrential 
> integrity.
> 
> I want to put a query together that I would input a date and retrieve a list 
> of staff member's who do not have an entry on that date.  Basically, I want 
> to find out who didn't show up to the meeting on that date.
> 
> Does anyone have a solution for this?
> 
> Robin
0
John
1/27/2008 8:27:37 PM
Reply:

Similar Artilces:

Can I get menus in 2007
Is there any way to get a menu bar w/ word 2007? I like 2007 but the choices in the tabs are not sufficient and I have to fight to get the function I need. thanks john Add what you want to the Quick Access Toolbar. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "John Coltrane" <tendengarci@yahoo.com> wrote in message news:ejbigfJ5KHA.5808@TK2MSFTNGP02.phx.gbl... > Is there any way to get a menu bar w/...

How do I increase query field length (>20 characters) ?
I want to exclude 3 or 4 variables from a particular query. Using Not "xx"or"yy" is fine, but when excluding more than this, query fails. I think the problem is that the total text characters is quite large (approx 60 characters in total, from four exclusions). Ideas, please ? 60 characters is not the limit. 1024 characters is the limit. POST the SQL of the query that is not working (View: SQL on the menu) or at a minimum post what you are attempting as the criteria. Also "query fails" does not tell us if you get an error (what is it) or the wrong resul...

#Deleted being displayed in a linked table
I have linked to a mainframe DB2 table and when I try to open the table or run a query against it, the only thing that display is "#Deleted" in every cell. I know there is data in the table as I can query using Excel and get the correct results. Using the same simple query in Access, I retrieve the correct number of rows but they all contain the same "#Deleted" message in each cell. I have links to other DB2 tables on this mainframe and the results are fine. Anybody have any ideas? Thanks. Have you tried relinking the table? I had the same thing happen to me o...

Get $500 to your PAYPAL Account by just one CLICK)
i have hidden the form of getting $500 to your PAYPAL ACCOUNT....CLICK on the IMAGE which is on the RIGHT SIDE of the website http://tricks-4u.4-all.org/ ...

why do i get #### in my formula cell?
I don't really know much about Excel, and my formula (it's just simple arithmetic formulas) returns with ##### displayed on the cell, with a tooltip showing the cell's correct value when I hover over it. airn, make the column wider and see if that helps -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "airn" <airn@discussions.microsoft.com> wrote in message news:C6F4DD7C-3D7D-475C-894D-0F7745735666@microso...

How do I get focus back on an Excel file?
When automating Excel, if there is a picture (graphic) on the worksheet, when the file is opened, the picture has the focus and mouse-clicks anywhere on the Excel window are lost. Is there a way to get the focus back so that other cells can be editted? Insert this line of code into the macro: ActiveSheet.Range("A1").Select You could also rework the code to avoid selecting the shape in the first place. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Stumpiana" <Stumpiana...

Installed Exchange 2003 Server on new Hardwar... How to get rid of old Exchange 2000 Server?
Hi, Our single domain company (Win 2000 DC's) was running a single Exchange 2000 server. I bought a new server, installed Windows 2003 Server on it, joined it to the domian (non-DC) and then installed Exchange 2003 Server on that and joined it to the site. I then pointed our MX records towards the new box. As soon as I verified connectivity, I began moving user's mailboxes over 1 by 1. Everyone is now running smoothly on the new server. I have not as of yet removed the old server (still on, running, no mialboxes). The problem is, that as soon as I turn off the old Exchange 2000 s...

Query wildcard symbols
Just a heads-up in case this bites you, too. I migrated an application from Access 2002 to Access 2007. I have a search form for the users to enter some criteria ... the form dynamically builds a SQL WHERE clause before opening a "results" form based on an underlying query that includes all the fields available to search on. Once in Access 2007 format, the search form opens empty every time?! When I add selection criteria to the underlying query directly, the result set is empty every time?! Oh wait, I'm using the "*" (anything/everything) wildcard character. W...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed working fine until when I try to create the Pivot Table Report (Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the pre-defined 'Definition ID' and click on the Excel icon, a message popped up saying " The WHTemplate.XLT file was not found." Anybody have any idea what that is, and how to resolve it?? Thanks. 1.) There are two pieces of software. Did you install both the server piece, and the client piece? 2.) Is Excel installed on the machine, on which you'r...

How do I get the data/xml/export option
My export option is disabled under data/xml tab. What is the exact feature I need to install to get this export xml option. I didn't install all the option during excel installation. Which specific install tab are required during excel installation? thanks Nick One way would be to do a web query, first preformat the cells as text, then do the query (data>import external data>new web query), put in the web site address, select the part you want to import, under properties select preserve cell formatting and do the import -- Regards, Peo Sjoblom (No private emails pleas...

Getting to grips with MOOL
Hi, I've bought, downloaded and installed MOOL, only to find it's not quite what I thought (I know some others have been disappointed by the reality vs representation), but more importantly that there's really no guidance on using it. I'm quite surprised how difficult I'm finding it as I've never struggled with any non-specialist applications before, but perhaps I'm just ageing badly! Anyway, can anyone give me a simple guide to getting going? What I would like: Basically I'd like something rather like Exchange. I (now) understand that MOOL won't do ...

Can't get newsgroup messages posted by self
I have a weird issue with Windows Live Mail concerting synchronizing messages from newsgroups. If I post a message to the Microsoft private newsgroups (privatenews.microsoft.com), I don't see the post but I can see new posts by other users. If I use Outlook Express on Windows XP, I see my posts. I don't have problems with WLM synchronizing my hotmail e-mail. Any idea how I can troubleshoot this issue? "Teo Lachev" <TeoLachev@discussions.microsoft.com> wrote in message news:65458221-D220-47D9-95E3-A9B593424714@microsoft.com... > I have a weird issu...

Importing a table from Access query
What is the quickest and easiest way to do the above? Thanks in advance. Hi The way I usually do this is run the select query, click on the top-right box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste into my workbook. An alternative is to right-click on the query in the Database Window and left-click on Export. In the Save As dialog box, select ..xls type. -- Andy. "Trish" <Trish@discussions.microsoft.com> wrote in message news:6BF3DE22-6590-4CAD-9EE1-FC978A3BB63B@microsoft.com... > What is the quickest and easiest way to do the above? >...

Help, how do I get an exe file with outlook 2002.
is there a way to turn off the GD stupid F#@$ing level one interecept "feature" in outlook? I am so angry with this stupid program@! I need to download an exe file and cannot with this software! Maybe if you washed your mouth out with soap, someone would be willing to help you. This is a technical forum -- not a place for ranting fools. "Sean" <sean@pointblankinc.com> wrote in message news:5a8001c42d8f$52c08b20$a401280a@phx.gbl... > is there a way to turn off the GD stupid F#@$ing level > one interecept "feature" in outlook? > > I am so angr...

attachment getting stripped off
I have Outlook 2000 and every time I origionate an email from my home computer with an attachment it gets stripped off before it gets to the addressee. I can foward emails with attachments and they stick. Any tips M are you using RTF format? if so, switch to plain text and see if it helps. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, Visual Quick Start Guide - OneNote 2003 Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answe...

get network login name
I have the following code to get the network login name: Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX <> 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = vbNullString End If E...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...

I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in these lists in another document/spread sheet. Is there an easy way to copy the data in these lists to another location? The long way would be to re-type all of it. ...

Reading empty query returns
When I open a form that has a bound query as the record source, no data appears (including the control objects of the form) if the query's select statement does not finds records that match the criteria I specify. This causes a problem in my VBA code as the variable I use to check the number of records returned by the query does not seem to be able to read empty query returns, even if I use isnull. So I guess I am asking how can I get Access to read an empty result set in VBA from a SQL select statement that is run??? Here is part of my code for this: DoCmd.OpenForm stDocName2, , ...

Query Flaw???
I need only one value from an aggregate Query - TheStores, which means the Number of Inspections in a "From - To" Timeframe. I use that as part of a calculation. The "Inspections" table contains only a Store and an InspectionDate. It gives me the result I need...but no Join...the Query has several other Fields from "Randy_F_FindFreq", but has been trimmed as an example here. SELECT Randy_F_FindFreq.AAll, Randy_Insp.TheStores, Round([AAll]/[TheStores],4) AS PerAll FROM Randy_F_FindFreq, Randy_Insp; Is this "sound"? TIA - Bob Without a join yo...

Write conflict error with ODBC link table
Hi, I have migrate my back end access tables to SQL Server. While editing data (ODBC link) from form, I receive Wirte conflict error 'The record has been changed by other user... Copying the change to the clipboard...'. The error allow me either copy the info to clipboard or drop change. In this case, how can I save my work to the table here? SF � "SF" <xyz@online.com.kh> ������ ��� ������ news:#F7F7OtcIHA.5160@TK2MSFTNGP05.phx.gbl... > Hi, > > I have migrate my back end access tables to SQL Server. While editing data > (ODBC link) from form, I...

Getting cells to automatically delete spaces
Hi all I have got a very large spreadsheet where the information is presente as: 111111/ 1 Notice the two spaces after the forward slash. There's about 10'00 cells in the column with varying numbers but all in the above format. I need to get the two spaces deled so that it will appear as: 111111/1 Is there a way i can get excel to proccess this for me. Any help will be much appreciated. Thanks in advance. Darre -- Message posted from http://www.ExcelForum.com Darren, Select the required area or a whole sheet, and do a simple Replace (Edit menu), asking Excel to replace s...

Why am I still getting SPAM?
I am getting unsolicited pop up advertisements when I start Money 2003 Standard Ed. Somehow the "turn off sponsorship & shopping..." box keeps getting unchecked! HOW? WHY? I PAID for this software. I wouldn't mind so much if it was free, but advertisements MUST come with free content of some sort. This is enough to make me change to another software package. ' Based on a finding reported in another thread, you might try turning off Money Express, then exiting/restarting Money, then changing this setting, then exiting/restarting Money, then turning MoneyExpre...