Multiple table query displays only one record

I have a table the has information about grant applications. I have another
table that has the names and addresses of the schools related to the
applications. When I do a querry it either shows only one record or all of
the information form one or the other depending on whetner I click 2 or 3 in
the properties window. How do I get it to show all of the records from all of
the columns that I have chosen from both tables?

0
Elaine_Moore
5/17/2010 10:05:12 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1433 Views

Similar Articles

[PageSpeed] 26

Elaine_Moore wrote:
>I have a table the has information about grant applications. I have another
>table that has the names and addresses of the schools related to the
>applications. When I do a querry it either shows only one record or all of
>the information form one or the other depending on whetner I click 2 or 3 in
>the properties window. How do I get it to show all of the records from all of
>the columns that I have chosen from both tables?

Full outer joins require basically unioning a left join and a right join.
e.g.

SELECT <fieldlist>
FROM a LEFT JOIN b
ON a.fieldx = b.fieldy
UNION ALL
SELECT <fieldlist>
FROM a RIGHT JOIN b
ON a.fieldx = b.fieldy

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

0
PieterLinden
5/17/2010 11:12:04 PM
Hi Pieter,

Here is the SQL for my query. I am not sure what you are saying it should
look like. I am a beninner here, so please be patient with my ignorance.

SELECT tblM2010Excel.FileNumber, tblM2010Excel.[Teacher Name],
tblSchoolsExcel.ID, tblSchoolsExcel.SchoolName, tblSchoolsExcel.StreetAddress,
tblSchoolsExcel.City, tblSchoolsExcel.Province, tblSchoolsExcel.PostalCode
FROM tblSchoolsExcel INNER JOIN tblM2010Excel ON tblSchoolsExcel.[SchoolName]
= tblM2010Excel.[School Name];

PieterLinden wrote:
>>I have a table the has information about grant applications. I have another
>>table that has the names and addresses of the schools related to the
>>applications. When I do a querry it either shows only one record or all of
>>the information form one or the other depending on whetner I click 2 or 3 in
>>the properties window. How do I get it to show all of the records from all of
>>the columns that I have chosen from both tables?
>
>Full outer joins require basically unioning a left join and a right join.
>e.g.
>
>SELECT <fieldlist>
>FROM a LEFT JOIN b
>ON a.fieldx = b.fieldy
>UNION ALL
>SELECT <fieldlist>
>FROM a RIGHT JOIN b
>ON a.fieldx = b.fieldy

-- 
Message posted via http://www.accessmonster.com

0
Elaine_Moore
5/18/2010 6:28:13 PM
Reply:

Similar Artilces:

multiple flyers per page?
Hello. I need to make a custom template with publisher. It will be one 8 1/2 by 11 paper divided horizontally into three parts so that there are three fliers that can be printed. each flier needs to be double printed so I need to make it a double sided project like a postcard or brochure layout. I can figure out text and image stuff. But I don't know how to make a basic custom project that is divided into three panels horizontally (so 8 inches by 3.83 inches roughly) Also an additional customization would divide each 3.83 by 8 inch flyer into two parts so one 8 by 11 page would ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

OWA jumping mouse
I have two computers on my network that are having an interesting issue. When they are creating a message and typing away in the body of the message, randomly when they hit a key, any key, they system will act as if the mouse was clicked. If the mouse is over the send button, it would have sent the email. If the mouse was over the start button, the start menu would have come up. Where ever the mouse was left at, it will act as if it was clicked. It is very random but often. This only happens in OWA, not in outlook or any other place. Both systems are running XP pro SP2 with IE 6 SP2. ...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Multiple Simultaneous Outlook Sessions
Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous Outlook Sessions open at one time? If so, how is it setup to send and receive from the same profile I would choose and save the replies back into the same profile? thx "Art" <Art@discussions.microsoft.com> wrote in message news:433A0AAA-9DB0-4411-954F-5165374B049D@microsoft.com... > Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous > Outlook > Sessions open at one time? If so, how is it setup to send and receive from > the same profile I would choose and sa...

Display of UML State Transition Event
Dear Group, I am attempting to use the UML Statechart with a couple of states and a transition between them. I select properties, Events, Change Event type, say, and can see ChangeEvent1 in the property window but... ....when I Ok back to the drawing the ChangeEvent1 is not displayed by the transition. I can enter actions in a similar way and they are displayed. How cna I get the event visible too in the drawing? Surely this is the most important aspect of a transition i.e. what caused it, regards, Colin Smith ...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Associating Multiple Existing Contacts to an Account, Until CRM 2.x
I have racked my pea-sized brain for endless days for a way to associate multiple existing contacts (or accounts) to an account. My client has accounts that have many contacts (or accounts) involved at different stages of acquiring a new account. The logical method would be to add a custom attribute with lookup functionality into the existing CRM contact or account objects but this is not possible with CRM 1.x. The resulting UI would allow an end user to associate different contacts (or accounts) to an account. Example: Account: ABC - AccountPhysician: (existing account or con...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Cant read one instance of recurring appointment
The problem we're having is a little different that what I've seen posted. A user randomly gets the message "Cant read one instance of recurring appointment. close any open appointments and try again, or recreate the appointment" several times a day. When you click OK the same message appears several times. Outlook doesn't indicate which recurring appointment is having the problem and the message just pops up at various times even if the user isn't in the calendar. We've deleted the Frmcache file and run the clean profile switch, but that hasn't worked. Has an...

Problem displaying expected results with CString
I am writing a MFC program to import data from a single table database into a normalized database with numerous tables. The first component opens a recordset object to the database and performs some basic tests on the field value, and the plan is to write the records out to a spreadsheet that fail to meet any of the criteria defined for the field. Along with writing out the record, I want to populate a comments field describing what failed. I have tried to implement this with a CString variable; I initialize it to a blank string each time a new record is examined, and then as each field is che...

CRM Email Displays Size=2>
One of my users is experiencing an issue when they save an email that is tracked within CRM the email displays in CRM with "Size=2>" directly in front of certain lines of the email. So for example "Size=2>" will appear in front of someone's comments in the email or in front of their name. Does anyone know why "Size=2>" is displaying in front of the lines of an email? Thanks. Mike H. "Mike H." wrote: > One of my users is experiencing an issue when they save an email that is > tracked within CRM the email displays in CRM wit...

me too, problem with import... only 15 records
me too, problem with import... only 15 records.... also in one of my customers crm i need a fast answer.... I dont see a question to answer...? JUAN MARKETING wrote: > me too, problem with import... only 15 records.... also in one of my > customers crm > > i need a fast answer.... Hehe yeah, what is the question!? what are you trying to import? Sorry, I don't know the reason, bu each time i try to import x number of accounts or contacts, MSCRM only import 15 records... Promise, everything is ok... columns, format csv from excel, size, records.... I say that, becau...

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...