Query Help 03-16-10

I have two tables that you see below. The first table called
CompletesAndNonCompletes holds information on who completed a course
in the company. If the field called CompletionDate is not null then
that means that the person completed the course. (in other words if
there is a date in this field then that means that the person
completed the course.)

The second table entitled CoachingSession is a table on everyone that
took a follow up coaching session after they completed the course.
Username from the first table and ID4LIFE from the second table are
the related fields. A person that completed the course may have more
than one coaching session. I am trying [unsuccessfully] to create a
query that simply gives me a list of everyone that has completed the
course but has not yet had a coaching session. I am using Access 2007.
If anyone could help me with this I would be extremely appreciative.
Thank you

Table: CompletesAndNonCompletes
ID
Firstname
LastName
Username
CourseName
CompletionStatus
CompletionDate

Table: CoachingSession
CoachingRecordID
FirstName
LastName
ID4LIFE
NameOfCoach
DateOfCoachingSession
NotesFromCoachingSession
DateofNextCoachingSession
CommentsFromParticipant
CommentsFromCoach
FormPic


0
John
3/16/2010 2:59:25 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
831 Views

Similar Articles

[PageSpeed] 15

On 16 mrt, 15:59, John Menken <menken.j...@gmail.com> wrote:
> I have two tables that you see below. The first table called
> CompletesAndNonCompletes holds information on who completed a course
> in the company. If the field called CompletionDate is not null then
> that means that the person completed the course. (in other words if
> there is a date in this field then that means that the person
> completed the course.)
>
> The second table entitled CoachingSession is a table on everyone that
> took a follow up coaching session after they completed the course.
> Username from the first table and ID4LIFE from the second table are
> the related fields. A person that completed the course may have more
> than one coaching session. I am trying [unsuccessfully] to create a
> query that simply gives me a list of everyone that has completed the
> course but has not yet had a coaching session. I am using Access 2007.
> If anyone could help me with this I would be extremely appreciative.
> Thank you
>
> Table: CompletesAndNonCompletes
> ID
> Firstname
> LastName
> Username
> CourseName
> CompletionStatus
> CompletionDate
>
> Table: CoachingSession
> CoachingRecordID
> FirstName
> LastName
> ID4LIFE
> NameOfCoach
> DateOfCoachingSession
> NotesFromCoachingSession
> DateofNextCoachingSession
> CommentsFromParticipant
> CommentsFromCoach
> FormPic

I don=92t understand your table structure.

The Username in table CompletesAndNonCompletes may not be unique (a
person can have completed more than one course). So connecting
CompletesAndNonCompletes and CoachingSession on username won=92t work.
You can=92t tell which course the CoachingSes-sion belongs to.

In the CompletesAndNonCompletes you have 3 person related fields
(first name, last name and username). That means that for each course
a person takes you have to store these three fields. One should store
a person=92s name only once.

If the names in CoachingSession are not the name of the choach you are
in even bigger trouble.

I suggest you first reorganize your tables.


Groeten,

Peter
http://access.xps350.com
0
XPS350
3/16/2010 4:39:34 PM
Hi John.

  I think you can use the unmatched query wizard.  Join the 2 tables on the 
columns that you indicated.  Once the query is built, open it in design view, 
and add the not null criteria to your date column.

"John Menken" wrote:

> I have two tables that you see below. The first table called
> CompletesAndNonCompletes holds information on who completed a course
> in the company. If the field called CompletionDate is not null then
> that means that the person completed the course. (in other words if
> there is a date in this field then that means that the person
> completed the course.)
> 
> The second table entitled CoachingSession is a table on everyone that
> took a follow up coaching session after they completed the course.
> Username from the first table and ID4LIFE from the second table are
> the related fields. A person that completed the course may have more
> than one coaching session. I am trying [unsuccessfully] to create a
> query that simply gives me a list of everyone that has completed the
> course but has not yet had a coaching session. I am using Access 2007.
> If anyone could help me with this I would be extremely appreciative.
> Thank you
> 
> Table: CompletesAndNonCompletes
> ID
> Firstname
> LastName
> Username
> CourseName
> CompletionStatus
> CompletionDate
> 
> Table: CoachingSession
> CoachingRecordID
> FirstName
> LastName
> ID4LIFE
> NameOfCoach
> DateOfCoachingSession
> NotesFromCoachingSession
> DateofNextCoachingSession
> CommentsFromParticipant
> CommentsFromCoach
> FormPic
> 
> 
> .
> 
0
Utf
3/16/2010 4:58:01 PM
XPS350,
Sorry, I should've mentioned that there is only *one* course in this
situation.
Thanks.
0
John
3/16/2010 5:59:52 PM
On 16 mrt, 18:59, John Menken <menken.j...@gmail.com> wrote:
> XPS350,
> Sorry, I should've mentioned that there is only *one* course in this
> situation.
> Thanks.

As Chris said, the unmatched query wizard should do the job.
It results in something like:

SELECT CompletesAndNonCompletes.*
FROM CompletesAndNonCompletes LEFT JOIN CoachingSession ON
CompletesAndNonCompletes.Username = CoachingSession.ID4LIFE
WHERE CoachingSession.ID4LIFE Is Null;

Groeten,

Peter
http://access.xps350.com
0
XPS350
3/16/2010 8:40:18 PM
Reply:

Similar Artilces:

help help help IF function
i am new to excell and need help with a problem...... the problem is iam looking for a amount < = 3000.00 and iam to display in bold and in light green i have never had to deal with that i got as far as =IF<=3000 then iam lost or it that even right?? I'm far from a whiz at Excel but I'd use conditional formatting. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "kimmy" <kimmy@discussions.microsoft.com> wrote in message news:ADC77884-FC87-47A3-90F2-D2AAF957F5D1@microsoft.com... >i a...

multiple calendars 01-13-10
I have 2 calendars. How do I overlay the calendars and print a composite? How do I move all data from 1 calendar into the other so that I can delete the 2nd calendar? Can you tell us your version as the functionality varies between versions. To get you started copying from one folder to another... open the Calendar you want to empty | view in By Category view | select all | drag to the destination Calendar and release. Regards Judy Gleeson MVP Outlook www.judygleeson.com www.deskdoctors.com Are you sick of bad email practice? Get a copy of my paper "Implemen...

Consolidate 02-05-10
I have a list of over 2400 names in column A as firstnamelastname They need to be turned into email addresses w/ the same domain name. How do I do this? Basically I need to turn into firstnamelastname@domainname.com Thanks in Advance if this is idea looking for, without modifyiing A, in a new column: =A9&"@email.com" not sure if need to alter the name in email address, else repost with desired output and I will look at. "bobby769" wrote: > I have a list of over 2400 names in column A as > firstnamelastname > They need to be turned ...

work order form and report design help
I am building an automotive shop database. I have a table that lists all of the services and the prices. I have the main form created where all of the customer data is entered in and I have another form that builds the work order for the customer. How do I build the form to allow for different services for the same customer without having to create service type 1, service type 2, etc. I need them to be independent of each other and I then need to create a report to print out for the customer. On Tue, 23 Mar 2010 08:24:01 -0700, Jimmy <Jimmy@discussions.microsoft.com> w...

Graph Help- 12 month graph, but only want months that have passed
I need help setting up a graph. It is a simple bar graph, that has the months of the year on the x axis, and production in tons on the y axis. I have a table setup for this Jan to Dec, but obviously only have data for Jan through May. Is there a way I can set up the graph so it will only make the graph Jan through May, but as I add more data, and months go by, it will automatically add a new column for June, then July and so on and so on? I do not want the months that have no data to be graphed with a value of zero, I would rather them show up automatically when that month has data....

Help with Html Help in MFC
Hi; I am using Visual Studio .net 2003. I have created a CDhtmldialog. I also created a html help file using Html help workshop. According to some source from web saying that I can call this method: BOOL HtmlHelp( HWND hWndMain, LPCTSTR lpszHelp, UINT uCommand, DWORD dwData ), directly from anywhere in my code. So I include the htmlhelp.h and htmlhelp.lib to the project. But I don't know how to call this method, since there is a another htmlhelp method in Cdialog too: HtmlHelp( DWORD_PTR dwData, UINT nCmd = 0x000F ); Please give me a hand. Thx in advance. Pan Wilson. ...

Tables/ queries question
I have a table for recording college attendances, some of the courses are at Campus 1 and some are held at Campus 2. I've got a field for course code, course anme and a check box to show if the student is at campus 1. When I enter the student ID number into the form, at the moment the name of the course comes up automatically in the text book, as I think it's reading the information from another database on the system. At the moment I've got a check box on the Form to enter manually into the table whether the student is at Campus 1 or Campus 2. What I wondered is if...

Can I use a Report expression for queries?
Is it possible to use the following expression that is used in reports on queries? =[Sales]/Sum([Sales])*100 Or some expression to get the result. Thank you. No. In your query, you can refer to text boxes on a form, but not on a report. That's because of the way reports are formatted (sequentially.) Forms have a current record, which identifies which value you want. Reports don't. In the context of a query, there are other ways to get totals, such as DLookup(): http://allenbrowne.com/casu-07.html or a subquery: http://allenbrowne.com/subquery-01.html -- Allen Browne ...

VLOOKUP help!
I have 2 worksheets with ID numbers in separate comlums . When I use vlookup it gives me a N/A value in return. I have formatted cells as "text" as "general" - tried all sorts, but it still does not return a value. This is a simple vlookup action and I am getting no-where. I simply want to find out if the ID on 1 list is also on the other list! Please always post your formula. Formatting makes no difference. Some data may look like numbers but are actually text. Formatting doesn't change that. You can find out with the ISNUMBER() function. If numbers are actual...

PST Nightmare
Hey there, This is the last resort for sanity. My PST which holds ALL of my emails is on an external HD, which worked fine up until last week, is over the limit. I did find this out the hard way and ran the SCNPST as well as teh PST2GB things and still nothing. What confuses me is that once i ran the PST2GB thing it told me to complete it with the SCNPST but it would not do it :( The latest and greatest trimming through PST2GB has brought the size down to 1.9GB and some change but still no cigar... Now outlook is giving me the error:"..is not compatible with this versio...

Strange problem in BP Portal for GP10 with Queries
I just had a user come to me and tell me he is getting an error on the sales page saying the query does not exist. This is the out of the box query that has not been modified. When I go into query organizer I find the Purchase Order Line Item folder but it now has a (1) beside it with the query inside it. What causes the folders to be renamed. While looking at the queries I multiple folders with the (1) out beside them. Thanks for any information you can shed on this. Robert Fann rfann@nospam.omnipackaging.com ------=_NextPart_0001_BD46EE76 Content-Type: text/plain Content-Transfer-...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Email Help: Sending Outlook email from Excel VBA
Hi All, How do I select a specific property in outlook while sending email from excel? There is an option we can set in outlook outgoing emails call "Voting buttons" in that feature there is a custom option that we can select called "Have replies sent to" its a checkbox. I want it checked when i send an email from excel. Hope i made it clear. Thanks in advance If sending through the Outlook Object Model, use the MailItem.VotingOptions property and MailItem.ReplyRecipients collection. -- Dmitry Streblechenko (MVP) http://www.dimastr.com/ OutlookSp...

Help please - tried everything - nothing works
Hi I look after a network for a charity in the UK. We recently implemented web content filtering using Webmarshal from netiq. Users now access the internet using webmarshal's inbuilt proxy server. Since introducing this, users of MS Publisher can no longer access online clipart from within the Publisher programme - by doing a search. You can however view online content by browsing categories in clip organiser. I've been assuming that the issue is with the proxy server configuration and have added various microsoft.com addresses (the ones I've found reference to in Newsgroups) ...

Help me! About CTreeCtrl CustomDraw.
Hi: I use CustomDraw to custom color of my CTreeCtrl. All color was setted as I wish except the backcolor of left position line that always is white. Please help me! These is my source codes about I custom my CTreeCtrl: void MyTreeCtrl::OnCustomDraw(NMHDR* pNMHDR, LRESULT* pResult) { m_clrTextBk = ::g_ShowSetting.iTextBKColor;//the back color of my text m_clrText = ::g_ShowSetting.iTextFontColor;//the color of my text m_clrBkgnd = ::g_ShowSetting.iTextBKColor; //the color of background LPNMLVCUSTOMDRAW lplvcd = (LPNMLVCUSTOMDRAW)pNMHDR; switch(lplvcd->nmcd.dwDrawStage) { ...

Help needed with form problem
Workbook contains several forms of varying style and function. One form shows the results of 10 recent tests in a countdown fashion, showing just the results of one test, followed by a one-second delay, followed by just the results of the second test, and so on until the result of each of the 10 tests has been shown. Then the program proceeds on to the next step by calling up a different form. The problem . . . Sometimes when the program starts running one or two of the forms are blanked out ("white" with just the top header showing). The forms continue to function within the ...

Unmatch query
-- Thanks ...

No Smart Tag help: just a blank "MS Excel Help" window
When I invoke "help on this error" on a Smart Tag drop-down, a blank "MS Excel Help" window appears with no content. The general help pane that appears via <F1> etc. is fine - it's just the Smart Tag help option that doesn't show anything, just a blank window. Any suggestions? Win XP Home SP2 Excel 2003 (11.6355.6360) SP1 ...

Query based on multiple parameters
I would like to create a query where the user can input 2 or more parameters (part numbers), separated by comma and/or space and return information about that part. I already have a query that accepts one parameter. Is there a way to do this with more than one? I have read some posts that say to use a table to serve up the paraments and the user chooses one or more. However, a particular part or parts may not yet be in the table as they are new or unknown. Is there any way to do this without using an existing table but just accept input from the user on the fly? T...

Vlookup Formula help required?
I'm trying to run the following formula:- =IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2)) I need the formula to run in each cell from B2 to B10000. I know I can drag the formula down through each cell. But this increases the size of the sheet. Is there any way to have the formula run in each of these cells without dragging down? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ "Alan T" <Alan.T.x34nm@excelforum-nospam....

Not Receiving Mail 03-28-10
I'm not receiving any messages, except TEST messages I send to my self. Just loaded Windows 7 (clean) a couple of weeks ago. Migrated all of my old stuff from Windows Mail (Vista). I've tried everything, including turning off email filtering. Only one email account and it is POP3. Can something be corrupted? If you receive test messages you send to yourself, then your Windows = Live=20 Mail is working normally. Who is your provider? If you go to the = provider's webmail site, do you see messages there that failed to download? --=20 Gary VanderMolen, Microsoft...

I E 7 Browser 06-21-10
I am experiencing a problem with IE 7 when I open the browser. The browser closes 3 times and then on the 4th try it stays open. I would truly appreciate any help with this problem. On 06/21/2010 04:57 PM, worf49 wrote: > I am experiencing a problem with IE 7 when I open the browser. > > The browser closes 3 times and then on the 4th try it stays open. > > I would truly appreciate any help with this problem. try disabling add ons http://pcsupport.about.com/od/fixtheproblem/ht/disableie7addon.htm ...

[Help] Tape Backup Developing
In order to develop tape backup program under Windows, I have just read "Microsoft Tape Format v1.0", and think I should follow the guidance of MTF. The question is: 1) There are many complex control structures (as many as 8 types of HEADERs) to store information such as volume, directory and file name; 2) But the Windows API only provides very simple (low level) Read/Write operations. Does it means that I must manually fill out those structures in accordance with the MTF and write them to the tape one by one? That will be a very big burden to us programmer! Any suggestions or re...

HTML Help Redistribution
Do I have to redistribute and run hhupd.exe to ensure that html help files can be displayed on a target computer or does statically linking in htmlhelp.lib satisfy the requirements ? "Ian Semmel" <anyone@rocketcomp.com.au> wrote in message news:%23yvQkPhsIHA.4840@TK2MSFTNGP05.phx.gbl... > Do I have to redistribute and run hhupd.exe to ensure that html help files > can be displayed on a target computer or does statically linking in > htmlhelp.lib satisfy the requirements ? I believe every version of Windows since Windows 95 has included the HTML help engine....

Help using Tables
Hi guys! I need some help. I have a table that has a percentage range. Like this. Less than 90% 0 90.0% 90.9% $ 2,625 91.0% 91.9% $ 3,413 92.0% 92.9% $ 4,200 93.0% 93.9% $ 4,988 94.0% 94.9% $ 5,775 95.0% 95.9% $ 6,563 96.0% 96.9% $ 7,350 97.0% 97.9% $ 8,138 98.0% 98.9% $ 8,925 99.0% 99.9% $ 9,713 100.0% 100.9% $ 10,500 101...