Unable to bring back all results from table with query

For a uni assignment we have a designated query that we have to create.

But when I run the query, I get one result returned to me. I should have two 
returned to me, as I have two records in the Problem table.

The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User 
Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code 
No], Problem.[Problem Description], Problem.[Current Activity Log], 
Problem.[Solution Description]
FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] = 
Problem.[Asset Code No]) INNER JOIN [Contact Log] ON Problem.[Problem Number] 
= [Contact Log].[Problem No]) ON User.[User ID] = [Contact Log].[User ID];

The thing is that when I do a select all from the problem table I get all 
the results, but I do not when I use the SQL described above.

Any ideas as to what may be causing this?

Cheers in advance
0
Utf
12/2/2009 3:13:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
859 Views

Similar Articles

[PageSpeed] 7

You are using inner joins which means that there must be a matching record in 
both tables or the record won't be returned. 

Try changing the joins from the Problem table to Left or Right joins. If the 
tables are small enough, you should be able to see which table doesn't have a 
matching record.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Karrot_Bex" wrote:

> For a uni assignment we have a designated query that we have to create.
> 
> But when I run the query, I get one result returned to me. I should have two 
> returned to me, as I have two records in the Problem table.
> 
> The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User 
> Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code 
> No], Problem.[Problem Description], Problem.[Current Activity Log], 
> Problem.[Solution Description]
> FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] = 
> Problem.[Asset Code No]) INNER JOIN [Contact Log] ON Problem.[Problem Number] 
> = [Contact Log].[Problem No]) ON User.[User ID] = [Contact Log].[User ID];
> 
> The thing is that when I do a select all from the problem table I get all 
> the results, but I do not when I use the SQL described above.
> 
> Any ideas as to what may be causing this?
> 
> Cheers in advance
0
Utf
12/2/2009 3:43:02 PM
Reply:

Similar Artilces:

convert sql tables with parent-child keys of a nested structure into a nested xml file
I have 5 tables in SQL Server. Each with the following design and a sample chain of the relationships from the root (WRL - World) UUS is the 'Code' of the first table and it is the 'Parent' value of the second table, etc. Parent varchar 3 Name varchar 60 Code varchar 3 WRL United States UUS <- UUS California UCA <- UCA North Coast UNC <- UNC Sonoma County USO <- USO Russian River Valley URR If I have 6 tables with these relationships from root to these vineyard appellation locations, how could I convert this nested, hiearchical data from SQL Server tables into...

unable to display check/radio box with transparent background
I am not able to get check boxes/radio buttons to be displayed with a transparent background in my dialogs. The general technique for displaying controls with transparent backgrounds is described in more than a few postings (e.g. http://groups.google.ca/group/microsoft.public.pocketpc.developer/browse_thread/thread/e5d77d8f30350f3a/22ea8e331b6cd993?lnk=st&q=dialog+bitmap+static+text+background+transparent#22ea8e331b6cd993 ). The general technique in MFC is as follows: HBRUSH CInsDialog:nCtlColor(CDC* pDC, CWnd* pWnd, UINT nCtlColor) { pDC->SetBkMode(TRANSPARENT); retur...

passing value from a form with IIF statement in a query
I have a form with a combo box that returns either a 1 or 2. If 1 is selected , the query would select the records that corresponds to the person with an ID of 1. If the value is 2, I want to select everyone who is NOT a 1. Using that value from the combo, I want to run a query that incorporates the following IIF statement: IIf([forms]![dlgRxRptByMonth].[repDoc]=1,1,>1) If I select the 1 in the combo box it produces the correct data. IF I select the value 2, the query runs without an error BUT produces no data. (I actually tried to insert the >1 as the value in th...

Combining 2 pivot tables
Hi, I have two pivot tables, and I would like to consolidate the data into one. Both tables are linked to the same source, and have identical data formats. However the final row for each table has a different name. Any ideas how I can get this data into a single pivot table? Thanks, RA What's in the final row of each table? RestlessAde wrote: > Hi, > > I have two pivot tables, and I would like to consolidate the data into one. > Both tables are linked to the same source, and have identical data formats. > However the final row for each table has a different name....

Clip art search gets no results (Publisher 2003)
One of our organisation's computers gets no results (or perhaps 1 or 2 items) when searching clip art for very common terms like dog or flags. These terms bring up many hits when searched on other computers. Things I've already checked (after reading Mary Sauer's suggestions): - The computer is connected to the internet - The cache has been emptied - The online content settings are correct You may not be able to use the Clip Organizer to view Web Collections when you run an Office 2003 program http://support.microsoft.com/default.aspx?scid=kb;en-us;839365 -- Mary Sauer MS...

I am unable to use some of the Quick Publications
I am using XP and Publisher 2000. When I try to open some of the Quick Publications and use the wizard I can see the selected item flash on the screen then it defaults to a blank sheet and the following message appears. Publisher cannot complete the operation I have tried the detect and repair procedure several times now and still cannot get to the available publications. The following article addresses the Clip Gallery, it also has information about the Pubwiz Folder. It wouldn't hurt to replace the Pubwiz, follow the instructions in the article. PUB2000: Error Messa...

How to Get Your Lover Back and Bring Back Lost Love !!!
t's great to see a group of people that is so passionate about How to get your ex back, or get your lover back and bring back lost love.People don't realize just how important it is to start to get informations and learn how to get your ex-back so that you will be free from that emotional trauma or torture that we face everyday due to relationships that has broken apart. This is why we need to make sure that the proper information is readily available for everyone. as this will make us happier and healthier . I've got a blog about my experiences with informations and learning h...

select status in a query
I have a table with account information. One of the fields is Account_Status, there are 6 possible choices for account status going from 1 - 6. Wha I'd like to do is run a qurey where the user can choose to return accounts with status 4 or 6. Any help appreciated. I can use "between and" ut this returns to much information if I need just 3 or 6. It also has to be user friendly as I do not want someone in the query. Any help appreciated. Thanks Jo See sample database at this site: http://www.cadellsoftware.org/SampleDBs.htm#FilterForm Look at how the listbox is set up (...

Create custom button on POS screen that calls sql query
How can i create custom pos button that call's sql query and show the result of query on pos screen or open another screen that resembles pos screen. You may as well write your own executable program, and just launch that program when you click on the custom POS button. "Sam Thakkar" wrote: > How can i create custom pos button that call's sql query and show the result > of query on pos screen or open another screen that resembles pos screen. ...

[SS 2000] Mirroring of only 1 table
Hi, in SQL Server 2000, is it possible to mirror just one table? Or only several tables of the database. Thanks in advance. Luigi Database Mirroring is at the database level. You can achiever higher granularity using Replication (assuming that feature suits you, of course). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Luigi" <Luigi@discussions.microsoft.com> wrote in message news:48897F0C-24C9-4894-8E27-DD84B7E31982@microsoft.com... > Hi, > in SQL Server 2000, is it p...

Backing Up Workbooks
Currently I have a macro button to backup my workbooks. I also have a save button. I would like to use only the save button but on a specific day of the week say Tuesday I would the macro also to do a backup. Can someone tell me the code to do this. I use the following to save the file. ActiveWorkbook.Save ActiveWorkbook.Close I use the following to backup the workbook. ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Splash").Select ActiveWorkbook.SaveAs Filename:="C:\Posto Pote\Backup\Posto Pote.xls", _ FileFormat:=xlExcel8, Password:="&...

Unable To Send Webpage via Windows Mail
I am using Windows Mail Vista SP2. I cannot forward a webpage via email. When I go to: FILE & SEND BY EMAIL, the option is not highlighted & therefore I cannot select it to transmit via email. Thanks for assistance. Jim Hodge Sounds like your Windows Mail doesn't have all its defaults. Open the Default Programs applet, which you can access either from the Start menu or via the Control Panel, then click the first item: "Set your default programs." After a few seconds, a list of programs comes up. Click on "Windows Mail". If it doesn't resp...

Select a query showing only selective post codes relating a relevant postal area
Hi please could you help as i would like to run a query only selecting relevant post codes from my database. The post codes i would like to select are from ST1 to ST4, ST6 to ST7. Thank you spending time to help this issue Chedd -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 I assume that those are the starting three characters of the post code and that the next character is a space. You could try a query using the criteria Between "ST1" and "ST4 zzzzzz" OR Between "ST6" and ...

Backing up info on flash drive
Hey out there! I just got a flash drive, it's not a big one, but it's a start. Trying to plan ahead in case anything happens to computer. Was wondering if there is a way to back-up all the pages I have bookmarked. I usually use Firefox for my browser & have quite a few pages bookmarked, as well as a few add-ons. Is there a way to save these to flash drive? I have all my pictures & music saved, any other suggestions on what else to put on flash drive? Anything important or crucial that I should not forget? Thanks for any help or suggestions! :0) -- Messag...

Exporting Queries to Excel Automatically
I have a table filled with information on different salesperson. Currently, to give them updates on their customers, I sort them in excel, then copy each single salesperson's data from the mastersheet into another excel files, save it and then email it to them. Is there a way in access to query each one of the sales people's info, sort by a field and then export each one of their data separately to excel? I hope this is clear. Grant ...

DPtoLP gives erroneous results
DPtoLP does not work as specified. When using GM_ADVANCED and a scaling factor, DPtoLP returns erroneous data. There are some severe roundoff errors in the implementation which render it essentially unusable. There is a *different* set of roundoff errors if the scaling is done by SetWindowExt/SetViewportExt. In all cases, the window origin is (0,0). I spent a couple hours looking for bugs in my logic before I just ran a for-loop and got the computations. They are summarized in http://www.flounder.com/msdn_documentation_errors_and_omissions.htm#DPtoLP I suggest caution when using this fu...

Pivot table drop down issue
I have Excel 2003. My pivot table drop down boxes do not display the OK or Cancel buttons. The buttons are there. I can click in the appropriate area to take the action. Just can't see a button. Has anyone else had this problem? Any known fixes? Thanks much. slb ...

Backing up Custom toolbars and macro buttons
Morning All, I have to backup my works pc and have lots of custom toolbars in Excel, and macro's linked to the toolbar buttons. What files do I need to backup to retrain this for my new pc? Im using office 97, new machine still has office 97. I believe the macros are in personal.xla? Not totally sure but what other files should I save? Many Thanks -- Adam ----------- Windows 98 + Office Pro 97 Adam, Personal.xls holds your (global) macros. Toolbars are stored in a file called Excel.xlb. This file would normally be stored in: C:\Documents and Settings\YourUserName\Application Da...

Front end back end sp question
Hello all I'm about to isntall sp2 for exchange on my servers. Whats the order for SP install, is it front end first then the backends? Also second question. I ahve around 15,000 items in a puiblic folder (NDR messages) Is there a way to delete all this items at once without using Outlook? My Outlook chokes when i try and delete them. Thanks 1. Update the Front End servers first. 2. Use the Recipient Policies to manager mailbox settings: 319188 How to use recipient policies to control mailboxes in Exchange 2000 and Exchange 2003 http://support.microsoft.com/default.aspx?scid=kb;...

Obtain Mcse,Mcsa,Mcdba,Mcsd.Net,Ccna,Ccnp Certificaitons Without Exams(Pay After Check Results)100% Passing Gaurantee..!!!!
All Certifications Like MICROSOFT,CISCO,COMPTIA,SOLARIS,ORACLE,SAP,MICOSOFT BUSINESS SOLUTIONS,EMC at your doorstep without exams..and with 100% passing guarantee...No need to go to any testing centre...we will do everything on behalf of you... We own several test centres worldwide and are able to offer you this fantastic service. If you would like a 100% guaranteed pass, then please read on further...... Mail only at this email id :- all_certs@yahoo.com We are able to offer a guaranteed pass for the following: Microsoft, Cisco, Comptia, Oracle, Solaris. Please send a request for any test th...

Excel 2003 default folder for Queries
Hello. How do I change the default folder for Data Sources queries? When I click on Data->Import External Data->Import Data..., it always defaults to "My Data Sources" in "My Documents". I want to change it to a different directory! Thanks in advance. I wanted to do this also. Following are the steps I took. I went into the registry and changed the folder name from My Data Sources to hoohah. Excel found hoohah in My Documents when I asked to Import Data. I then moved hoohah out of My Documents to GordStuff folder. When I went to Data>Import etc. Exce...

Memory Table vs. CTE
I had posted a week or so ago about a performance issue with Null-able variables being passed into stored procedures and the performance gains by setting those parameters to local variables declared in the stored procedure. I followed that advice and all was well. How ever, the stored procedure started to time out the again, running the stored proc on my local SQL server would take 3:45 to return 5 rows. Something was wrong. The stored procedure was using 4 CTE tables, one of those tables being referenced in 5 different locations through-out the stored proc. The only change I made t...

chart will not plot data from SQL query
1. External data query against SQL stored proc returns results to cell as expected 2. Chart works with discrete data in same cells 3. Chart -> Source Data -> Series -> Values shows the corrected cells 4. Same results embedded or chart sheet ...

Order Entry and Back Ordered Quantities
My client uses Dynamics GP v9.0 and enters all their orders with all their items marked to the QTY to Order field. This then shows all OPEN ORDERS with all ITEMS as needing to be ordered. They do this so that their Purchasing agent will order the items (whether they are available or not) as though the items are NOT available. It takes a LONG time to enter a line item, tab/enter through each field in order to move the QTY to INVOICE to ZERO and enter the QTY to ORDER with the correct qty. They do this BEFORE allocating so that the material does not get allocated (including s/n).... M...

Open Table Descriptions without closing Table Names
Currently if you open the Table Descriptions window from the Table Names window, the Table Names window closes. You can reopen the Table Name window by clicking on the lookup button, but all of the selections are back to the defaults. Please change to allow the Table Descriptions window to open without closing the Table Names window. Thanks. ---------------- 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" button in the message pane. If you do not see the button, follow th...