update a field by inputting data in a different table

I have a database (Access 2007) that is used for holding data on and 
communicating with FE colleges. Obviously (?) each college has a number of 
contacts (up to 14) and these are represented in two tables – one for College 
Details and one for Contact Details which are linked by a one to many 
relationship.  Most colleges have one representative on one of 3 groups, but 
not all colleges are represented on all groups and some not on any. The 
membership of a group is noted by a Yes/No field in the Contact details.  
Getting a query to run to identify membership of groups is OK. But I can’t 
figure out how to get a query result with a row per COLLEGE that has no 
representation on either one or more of the groups. What I get is  a 
duplication of the college name (as they identify the number of contacts who 
are not members of a group). The only way I can figure it is to have a field 
in the College Table which mirrors the one in the Contacts Table, but I don’t 
want to enter the same data twice & cannot link the tables, so by completing 
one Yes/No field it automatically completes the Yes/No field in the other 
table.
0
Utf
1/21/2010 11:07:01 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
577 Views

Similar Articles

[PageSpeed] 24

Toppo -

You want a query that shows colleges with no memberships.
You can use the 'not exists' with a subquery to find these.
Try this, substituting your table and field names for mine.

Select CollegeDetails.CollegeName from CollegeDetails
Where not exists (Select 'x' from ContactDetails
where ContactDetails.CollegeID = CollegeDetails.CollegeID
AND ContactDetails.GroupMember = True)
-- 
Daryl S


"Toppo" wrote:

> I have a database (Access 2007) that is used for holding data on and 
> communicating with FE colleges. Obviously (?) each college has a number of 
> contacts (up to 14) and these are represented in two tables – one for College 
> Details and one for Contact Details which are linked by a one to many 
> relationship.  Most colleges have one representative on one of 3 groups, but 
> not all colleges are represented on all groups and some not on any. The 
> membership of a group is noted by a Yes/No field in the Contact details.  
> Getting a query to run to identify membership of groups is OK. But I can’t 
> figure out how to get a query result with a row per COLLEGE that has no 
> representation on either one or more of the groups. What I get is  a 
> duplication of the college name (as they identify the number of contacts who 
> are not members of a group). The only way I can figure it is to have a field 
> in the College Table which mirrors the one in the Contacts Table, but I don’t 
> want to enter the same data twice & cannot link the tables, so by completing 
> one Yes/No field it automatically completes the Yes/No field in the other 
> table.
0
Utf
1/21/2010 3:00:01 PM
Reply:

Similar Artilces:

vlookup w/ multiple data in a single cell
Is it possible to have a cell contain two pieces of informaton (e.g., A1 ="Me, You"), a vlookup statement to look for each pece of informaton in that cell (e.g., first "Me", then "You") in another sheet, and display the match in B1? Sheet 1 A1 = "Me, You" B1 = (after vlookup) "Art, Joe" Sheet 2 A1 = "Me" and B1 = "Art" A2 = "You" and B2 = "Joe" I tried all different kinds of =VLOOKUP with different functions, and I can't figure t out, of course I assume its possble to even use VLOOKUP ...

Need help on comparison of dates in different format.
Hi I have question pertaining comparison of dates in different format :- eg. 20 April , 2004 [Cell A1] 20-4-04 [Cell A2] I was using if not function. ie. if(A1=A2, True, False). The result always shown as False. Pls help as to how could I do correct way solving this problem. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi are both cells real date values or are they stored as 'Text'?. Check both values with =ISNUMBER(A1) =ISNUMBER(A2) th...

After Update Save
I have a form that I want to force one of the fields to update with a timestamp when any of the other field's data changes. Private Sub Form_AfterUpdate() Me.PESA_timestamp = CurrentUser() & " ~ " & Date End Sub When a change is made, it stays in the record and won't leave "Edit mode" until I hit the ESC key. How can I get it to accept my changes and allow me to proceed to the next record without manually hitting the ESC. Also. .is there a way to get the Login Name rather than the PC user name? -- Thank you, cs ~~~~~~~~~~~~~~~~~~~~~~~~ ...

merging and formating data
I have data in 2 columns that i need to merge. IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do i...

CRM 3.0 Updates?
Have there been any updates/upgrades to crm 3.0?? There are hotfixes that can be obtained to address specific issues via support. -- Matt Parks MVP - Microsoft CRM "Glenn Reed" <GlennReed@discussions.microsoft.com> wrote in message news:26E4A64F-5487-470D-98AC-095C06D0A684@microsoft.com... Have there been any updates/upgrades to crm 3.0?? There has not been a service pack or rollup yet if that is what you mean ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Glenn Reed" <GlennReed@discussions.microsoft...

Pivot table question #9
I recently had a problem with values not showing properly in my PivotTable, until it was poited out to me that I was using Text in some cells instead of numbers. Ideally, I would like to include text. Is there anyway at all that I can? My data comes from an Access Table by the way My reasoning. the data in the pivot shows scores overall of several years competition, and each meet has 2 reserves, who can step in for an excluded competitor. However, the reserves might not compete, but still need to be recorded as a competitor at the event. Rather than showing zero (0) as their score, I'd r...

How to save MS Money program updates?
I wish to archive software updates to Microsoft Money on my computer's disk drive. What I mean is, I would like to be able to obtain the latest version of the MS Money program without going through Microsoft's network servers. In particular, I would like to be able to 1. Install MS Money from the Microsoft CD-ROM that I bought. 2. Execute one or more update/installer files stored on my own PC. How to obtain the update/installer file(s) in step 2. above? I have the latest version, Microsoft Money Plus Premium 17.0.125.1415 expiring 11/23/2009. Thanks for any suggestions. -- David A...

Outlook 2003 Offline Address Book not updating
I now have 5 computers among 100+ that simply stopped updating the offline address book. They give no error message. nothing is recorded in the event logs. success is recorded in the sync log. it says it does it, but it doesn't. The most recent user reported that he couldn't see a person in his address book that had been hired 4 weeks ago. It is the same with all machines. 1. if I tell it to manually download the address book, it says it does and nothing changes. 2. if I create a new Outlook profile, it downloads a new address book at that time, but not after 3. If I create a ne...

how can i set up an automatic "date update" in excel?
In other words, i would like excel to update this cell with the current date every time when i open the document. Can i do that? one way: =TODAY() In article <D8818AFF-8489-4FF1-B94D-A08FB0B45F01@microsoft.com>, "Litzyam" <Litzyam@discussions.microsoft.com> wrote: > In other words, i would like excel to update this cell with the current date > every time when i open the document. Can i do that? One way =today() HTH "Litzyam" wrote: > In other words, i would like excel to update this cell with the current date > every time when i open t...

csv-files with '+' in field -> treated as formula
When I make up a CSV-file (from our oracle-database) we have to fill in a line like this: 9603144;01;TEST;+BE;01/01/1000;31/12/9999 When opening the file in Excel, the 4th field (+BE) is treated as a formula, but that is not an option. Anyone have an idea how to write it in the CSV-file so that it is treated as a text-field but without showing any quotes (so I would like to see +BE and not '+BE) Tnx already in advance! Dave found something myself: just add a space before the + and it's ok if anyone has another idea: post it >-----Original Message----- >When I make up ...

Difference between yyy and yyyy?
When typing custom formats, yyy appears to do the same thing as yyyy. Some online tutorials seem to indicate this is a hold-over. Does yyy have any official function? IlNxdWFyZSBQZWciIDxTcXVhcmVQZWdAUm91bmQuSG9sZT4gaGF0aCB3cml0dGVuOiBuZXdzOmRz NXZjNGRnMWdlcnAwZmhwdHJxNTBzc2k2NmtlbzJ2dDlANGF4LmNvbS4uLg0KPiBXaGVuIHR5cGlu ZyBjdXN0b20gZm9ybWF0cywgeXl5IGFwcGVhcnMgdG8gZG8gdGhlIHNhbWUgdGhpbmcgYXMgeXl5 eS4NCj4gU29tZSBvbmxpbmUgdHV0b3JpYWxzIHNlZW0gdG8gaW5kaWNhdGUgdGhpcyBpcyBhIGhv bGQtb3Zlci4NCj4gDQo+IERvZXMgeXl5IGhhdmUgYW55IG9mZmljaWFsIGZ1bmN0aW9uPw0KDQoN Ck5vLCBpdCdzIGp1c3QgZmFpbC1zYWZlIGFuYWxvZyBmb3...

change worksheet data without changing chart
I am attempting to create a scatter plot chart in Excel that will not get modified each time I sort my Excel worksheet. Does anybody know how to "lock" the chart or export it so that it is no longer linked to the worksheet? Thanks!!! Here's how to unlink a chart from its data: http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ shelly wrote: > I am attempting to create a scatter plot chart in Excel > that will not ge...

Lost Subject Field
I don't know how I did it but somehow I managed to delete the subject field in outlook so now when I go to create a new email to send to someone I only have the TO: line showing! I am unable to enter a subject! How do I get the subject back???? Go up to VIEW | MESSAGE HEADER. Kenny "Rockets" <wcrockets@hotmail.com> wrote in message news:00b601c3dc66$d8456fb0$a501280a@phx.gbl... > I don't know how I did it but somehow I managed to delete > the subject field in outlook so now when I go to create a > new email to send to someone I only have the TO: line &g...

will formulas be lost when moving data in a worksheet?
I have a P & L that contains 3 years of info. I have to delete the oldest year (2007) and put in our actual numbers for year ending 2010. Will I lose all of the formulas when I delete all of the 2007 numbers? Is there an easier way to do this? Edit/ Goto/ Special/ Constants Delete those. -- David Biddulph "Renae" <Renae@discussions.microsoft.com> wrote in message news:456322CE-EB1E-4616-8716-881CF545DC33@microsoft.com... >I have a P & L that contains 3 years of info. I have to delete the oldest > year (2007) and put in our actual numbers for y...

Problem starting different http connections from the same application
Hi, I have a multithreaded application in which one thread sends a request to a web server -and reads the response for a while (which is why its a separate thread). The other thread needs to send data periodically to the server - but this is where I run into problems. In the method that the second thread uses makes a new session , gets a new connection, creates a new http file - but fails to send the data. Looking at the ethereal captures, it seems that the second thread is sending requests through the same tcp connection that the very first request (that hasn't returned yet) we...

won't recognize two different cd's
I am trying to rip two differentcd's, the media player insists they are the same and copies one over the other. What do I do? "Pat" wrote: > I am trying to rip two differentcd's, the media player insists they are the > same and copies one over the other. What do I do? If you purchased the CDs or have them on their originally produced discs, you shouldn't have a problem. If they were burned to CD from another device, try changing the 'quality' settings under the rip menu to the highest setting. They might be on low quality CD's that ...

2 Subforms, can't change link fields for one
I have two subforms on my main form. The second is a copy of the first, but I want to link it to separate fields on the main form. When I open the main form in design view, the first subform shows in design view, but the second is just a blank control. The controlsource field is set to the same as the first. When I try to change the link child/master fields, it says: "The expression you entered refers to an object that is closed or doesn't exist." and blanks it out. What am I doing wrong? Thanks, =Ray= ...

Importing data #2
Importing data into customer, item, suppliers, and GL master files. This is a brand new company and I need to import the master files from another system. I am able to easily dump each file to excel, but how do I import into GP? -- Gino S. GPK Computers Gino, Usually you would use an import tool like Integration Manager or eConnect. If this is a one time import, you're probably better off with Integration Manager. -- Victoria Yudin Dynamics GP MVP "Gino@GPK" <gino@gpk.net.au> wrote in message news:5B6CAB1C-FA16-408A-8242-8B1B9E80B011@microsoft.com... > Impo...

InputBox: how do I hide user input
I am trying to write a script that gets a password from the user then logs into a remote computer. The InputBox function doesn't seem to have an option that masks the user input with other characters (eg. "*"). How can I do this? For ease of distribution I'd like to be able to do this in a single script - rather than using a separate library or other file. TIA. On Feb 16, 11:21=A0pm, Robert S <robert.spam.me.sensel...@gmail.com> wrote: > I am trying to write a script that gets a password from the user then > logs into a remote computer. =A0The...

Autofilling data on a form
I get myself into the darndest things. I have a requirement that calls for creating a form in Excel that automatically inserts dates which change by calendar quarter and state. The user would like to be able to make one entry and have the form autofill the correct dates throughout the form. Ideally, a single form would be usable for several states, but individual state forms are the "fall back". Any help anyone could offer would be greatly appreciated. -- Al Aplin Crestview, Florida Hi Al this should be possible but i'll need more info (and the real fun thing is tha...

Copy & Paste Filtered Data
When I filter a column, then copy that column and paste into another worksheet (using F5/Special/Visible cells only), the pasted data is not the same as the data I wanted to copy. The pasted data omits certain rows, and I have no idea why. Does anyone know how I can do that correctly Similary, if I use the Advanced Filter command to get unique records only, I tried both ways and compared the data (results were not the same). 1st way is to filter the data in place, and the 2nd way is to copy the results to another location. When I tried both methods, the copied results were different (some...

Reestablishing Outlook Files from a Different Location
Just recovering from a crash with a new hard drive. Using XP with Outlook 2003 and need to change from the default path to c:\exchange for the location of all Outlook Files, Mail Boxes, Address Book, Contacts, Folders, etc. Managed to change path for Address Book, but not the rest. Not on network Need to recover old e-mail, folders, etc. Any and all assistance gratefully appreciated. -- Les Les <Les@discussions.microsoft.com> wrote: > Just recovering from a crash with a new hard drive. Using XP with > Outlook 2003 and need to change from the default path to c:\excha...

Two-Axes Pivot Table Chart
Hi Folks - Here's my dilemma ...I have a pivot table that looks like this: Cat1 Cat2 Cat3 Unit Data Unit1 SumOfCatCount 50 60 70 CountOfCat 1 2 3 Unit 2 SumOfCatCount 55 60 70 CountOfCat 3 4 3 Unit3 SumOfCatCount 75 75 80 CountOfCat 2 5 1 I can make a nice...

Formulas for birth year and age in user defined fields
I'm using outlook 2002. I want to create 2 user-defined fields in my contacts, but am having trouble figuring out how to do it: 1. Year of birth field, based on the value entered in the birthday field - I created a new user defined field, with the Type set as Formula, and the formula set as: Year ([Birthday]). The year is displayed, but formatted as a number (1,957) rather than a year (1957). How can I get the number to be displayed without the comma? 2. Age, based on difference between birthday and present date - Is there a simple formula to accomplish this? I haven't been ab...

add data labels in pivotchart
Hi everyone, i want to add data labels in pivotchart (of course it is easy) but when i change some of indexes in the category fields or series fiels - all data labels disappear. And then i must add data labels manually again, it is very annoying. I have no idea what to do, probably i should ask someone who is experienced in VBA to write a code or something else...... IF you don't know how to do this, i will appreciated any kind of help, advices, whatever.... ...