query results

I have a query that pulls data from 2 tables all works fine if there is data
in both tables. Unfortunatly the possibility exsits that one table might not
have any data therefor not exist for that customer and those results do not
show in my form where the query displays.  I do not know sql very good so am
having a problem getting results to show when the table is missing

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

0
jomara
1/31/2008 4:40:15 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
658 Views

Similar Articles

[PageSpeed] 41

In the query design window double click on the line between the two tables 
until a dialog box pops up. Try the second option. If that doesn't work, try 
the third option.

The first option, the default, is an Inner Join where you need matching 
records in both tables.

The second option is a Left Join where it will show all the data in one 
table and only matching records in the other.

The third option is a Right Join which does the same thing as the second 
option but with the tables switched.

Now if the second option and third option both kind of work but don't return 
all the expected records, you may well have an Outer Join which Access 
doesn't support. There are ways around it. Reply back here if that's the case.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"jomara via AccessMonster.com" wrote:

> I have a query that pulls data from 2 tables all works fine if there is data
> in both tables. Unfortunatly the possibility exsits that one table might not
> have any data therefor not exist for that customer and those results do not
> show in my form where the query displays.  I do not know sql very good so am
> having a problem getting results to show when the table is missing
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> 
0
Utf
1/31/2008 5:09:01 PM
Thanks for the help worked fine cant believe i didnt think of that

Jerry Whittle wrote:
>In the query design window double click on the line between the two tables 
>until a dialog box pops up. Try the second option. If that doesn't work, try 
>the third option.
>
>The first option, the default, is an Inner Join where you need matching 
>records in both tables.
>
>The second option is a Left Join where it will show all the data in one 
>table and only matching records in the other.
>
>The third option is a Right Join which does the same thing as the second 
>option but with the tables switched.
>
>Now if the second option and third option both kind of work but don't return 
>all the expected records, you may well have an Outer Join which Access 
>doesn't support. There are ways around it. Reply back here if that's the case.
>> I have a query that pulls data from 2 tables all works fine if there is data
>> in both tables. Unfortunatly the possibility exsits that one table might not
>> have any data therefor not exist for that customer and those results do not
>> show in my form where the query displays.  I do not know sql very good so am
>> having a problem getting results to show when the table is missing

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

0
jomara
1/31/2008 5:14:28 PM
Reply:

Similar Artilces:

Unable to insert Access queries
When trying to use ms-query to import data from an Access database, I've found that it doesn't allow me to open some of the queries (views) from access. It seems that the queries I have trouble importing have the nz() function in calculated fields of the access queries (views) (or in a sub-query). Is this simply not supported or is there a way to work around it? In the Access query, instead of Nz, use an IIF formula. For example: EstHrsCalc: IIf(IsNull([EstHrs]),0,[EstHrs]) Craig A wrote: > When trying to use ms-query to import data from an Access database, I've found that...

Question on Query 06-06-07
I have created a query that searches a database based on a zip code entered by the user. The question is: The database contains both 3 digit(first 3 digits) zip codes and 5 digit zip codes. I want the user to enter all 5 digits and still be able to pull up the 3 digit codes that match. How do I configure the query to do this? -- Dirk Are you saying that the 3 digit and 5 digit zip codes will be in the same field in the table? WHERE [ZipCode] = [What Zipcode?] OR [ZipCode] = Left([What Zipcode?], 3) If not, and they're in separate fields, you'll want WHERE [ZipCode] = [Wha...

Duplicate date prompts when using Macro to run Crosstab Query
Hello, I am having an issue when I run a Crosstab Query via a Macro. I get prompted for duplicate Start and End Date. This only happens when I run the Query via Macro. When I run the Query without using the Macro it works fine and only prompts me once for Dates. Does anyone know what I am doing wrong? Please help! VMR -- Message posted via http://www.accessmonster.com I can't see a valid reason why the parameters should pop up twice only when the query is executed by macro. (What version of Access is this?) A workaround might be to create a little unbound form with a couple...

How to add multiples records from a querie
Hi. I have a unmacth querie that inserts me into a table the records that are note match. My problem is that when I have one one unmatched record i need to insert that record 12 times. I have to insert 12 time each record that I have diferent. What happens is that anytime I insert a record I have a repeat it 12 times so I can have: Periodo: 1 2 3 4 5 6 7 8 9 10 11 12 All the fields are equal but the this filed must be diferent. I can I insert 12 records automatically? Regards, Marco Try creating a table named Period with a field named period containing numbers 1 (one) through ...

access query 05-24-07
I have a query in my database which includes the following fields from various tables: orderID stockID qty price stockLevel reorderLevel However I cant figure out how to display only the results where 'stockLevel' is less than 'reorderLevel'. ...

images in select query
I have images in a folder and path is saved in a table called art_master i want to insert images in a field in select query im using sql 2008 Thanks On Tue, 4 May 2010 15:19:51 +0530, "Capri" <NoEmail@NoDomain.com> wrote: >I have images in a folder and path is saved in a table called art_master > >i want to insert images in a field in select query > >im using sql 2008 > >Thanks > Hi This is not possible in a SELECT query, you may want to look converting the files to be stored in the database using the filestream dataty...

using a date variable in query criteria
Hello I want to use date variables in the criteria of a query. When I put Between #1-1-07# and #1-30-07# it works but if I try to use a date variable it doesn't work. In the query by example view. The field in the database is a string but I convert to a date using Datevalue([work_order_received]). I want to calculate a date like this in the Field: dateback: datefrom: ( year(Date())-1 , 1 ,1) dateto: (year(Date())-1,12,31) When I try to use the variables (datefrom dateto) in the criteria statement this doesn't work ...

query run in subform and not in separate window
hi, i've created my form and have a query that runs within a subform. This is working fine except that the same query runs in a separate window also. How do I prevent the separate window from opening but only run within the subform. thanks heaps What do you mean by having "a query that runs within a subform"? What are you doing to run it? What kind of query is it? If it's a SELECT query, typically you'd set it as the RecordSource for the form being used as the subform, and not run it. If it's an Action query (INSERT INTO, DELETE, UPDATE, SELECT ... INTO), you ...

Query on zip codes
I have a query that searches a database for matching zip codes. The user enters the desired 5 digit zip code and the query displays the necessary information. The database has zip codes stored as 3 digit (first 3 digits of zip) and 5 digit. How can I write the query to find both the 3 and 5 digit matches? -- Dirk "Dirk_Bob" <DirkBob@discussions.microsoft.com> wrote in message news:347CDA14-9807-445A-8E94-4C729A6119F6@microsoft.com... > I have a query that searches a database for matching zip codes. The user > enters the desired 5 digit zip code and the query disp...

Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...

Emailing Query Results in Excel
Hi. I am using Microsoft Query in Excel, and the results come back fine; however, when I go to Send To--Mail recipient, then choose "sheet", it sends my spreadsheet without my Excel Query data. How can I email the results of my query to another person? I have tried this several times, and every time you see the outline of the spreadsheet, but no data. Please help!? Thanks, CJ Hello CJ, Try copying the data and then Paste Special/Values to another location. HTH, Greg "CJ" <anonymous@discussions.microsoft.com> wrote in message news:1936c01c41c46$b5aa93c0...

report on a cross tab query
subs View profile More options Feb 7, 2:38 pm Newsgroups: microsoft.public.access.queries From: subs <subbu1...@gmail.com> Date: Sun, 7 Feb 2010 11:38:53 -0800 (PST) Local: Sun, Feb 7 2010 2:38 pm Subject: access report on a cross tab query Reply | Reply to author | Forward | Print | Individual message | Show original | Remove | Report this message | Find messages by this author i have a cross tab query which gives an output like below with following columns------- ozip dzip week no1 week no2 week no3 columns week no1 , week no2 and so on could be dynamic-- it ...

Turning off action query confirmation messages
How do I turn off ALL confirmation messages for make-table and append queries when running them from a macro in an Access 2007 .accdb database? I've tried following a 2004 posting which says: "If you're using a macro to run the query, put SetWarnings False before and SetWarnings True after running it. Doug Steele, Microsoft Access MVP " .... but they aren't accepted as a valid commands in the macro setup. I don't know SQL; I'm using plain vanilla Make-Table and Append queries called out in OpenQuery commands in a macro. The confirmation message...

Linking Same Tables (Current/Previous) in a Query / Sum is multiplying value results
I'm trying to link two tables in Access...both tables contain the samefields with YTD totals. I need to subtract YTD Total between the two,to determine the difference. I have the tables linked via 2 fields.I group by the two fields, and sum on YTD. It is multiplying thevalues and I'm ending up with numbers in the 200,000 instead of max ofabout 5,000.I've tried doing queries separately, doing the sum, and then linkingthe two new queries...only to have the same results.I've tried turning them into Make-Table Queries, creating the tablesand running the new query off of the 2 ta...

Query based distribution list problem
Running exchange 2003. Our query based lists work fine except the sender gets an error message for accounts that have expired. Is there a way to get around this? TIA...Bob Did you mean accounts that are disabled? Accounts with expired passwords shouldn't have an issue receiving mail, afaik. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Fuzzy Logic" <bob@arc.ab.caREMOVETHIS> wrote in message news:Xns974A7C1C06982bobarcabca@207.46.248.16... > Running exchange 2003. Our query based lists work fine ...

Displaying Query Results in a Textbox
Hello I am having a problem getting the results of a query to appear in a textbox. Does anyone know how to do this? Thanks JJJJS Please give a few details. -- -Larry- -- <craigalaniz@pobox.com> wrote in message news:1178829331.981143.115990@n59g2000hsh.googlegroups.com... > Hello > > I am having a problem getting the results of a query to appear in a > textbox. > > Does anyone know how to do this? > > Thanks > > JJJJS > ...

Query plan isn't doing an index seek
If you have the following: SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID = ws.VendorID) FROM Work ws What I get is a Hash Match from 2 index scans. |--Compute Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName])) |--Hash Match(Right Outer Join, HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as [ws].[VendorId])) |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR])) |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS ...

Aggregate query and Albert Kallal's Multiselect example
Hi All, I am attempting to build a continuous form that will allow users to 'purchase' items. The recordsource is an aggregate query that groups by part number and a boolean 'Rush' field, and sums the quantity ordered. The query works the way I want it too... Now I am trying to have a checkbox on this form for users to select, and that would mark the item(s) for ordering. I added in the 'ID' field that is the PK for the 'parts' - and used this while following Albert's Multi-Select example. The problem is that there can be 2 of the same items to be orde...

One result from multiple memo fields 04-25-07
If I have duplicated this post I am sorry, my computer froze, so I wasn't sure if the first post got thru. I am an access newbie so I am not even sure this can be done... I need a report to pull material listing from a memo field [Notes] or show which jobs have missing materials... My report has the following record source: SELECT tblJob.Job, tblNotes.Notes FROM tblJob LEFT JOIN tblNotes ON tblJob.ID=tblNotes.JOBID; The report has a Group header on Job and a [Material] text field with the following control source: =IIf([Notes] Like "Material*",[Notes],"No Material listin...

Name of the sheet where the formula result is
I use max()and min() in the result sheet to define the max and min values in 10 sheets where test data are. How can I display the sheet name of the result from the calculations? For example, if the result of max() is from sheet 1? Thanks a lot Catherine You will probably need a macro to search the sheets for the values. I would probably be a good idea to keep searching to the end of the las sheet to check for duplicates -- Message posted from http://www.ExcelForum.com This might not work for you, but I'd lay out part of my summary sheet like: Sheetname Max (this formula...

Problem with parameter query using between []...
I am trying to build a parameter query for which I would enter two values and have all the values in between returned. For example, a list of numbers from 1 to 200. On the query I have the following on the Criteria: Between [Enter Product min number] And [Enter product max number] & "*" So when I run the query first window pops up, I enter 100, then second one pops up, I enter 150 and after that I get a message saying that the expression is typed incorrectly or it is to complex. Also, I am trying to get a query of the same kind where I can type on the parameter something like ...

Very difficult query (?)
Hello I've been struggling with this all day, and although I think I'm near a solution (actually I thought that 2 hours ago!) it now involves 4 nested queries and is 'grinding' horribly (i.e. takes 30-40 seconds to run), and I'm convinced that there must be a better way to get what I need! I really hope someone can help with this. I have a table [x confirmed] which includes the following fields: 'employeename' (text) 'month number' (integer) 'cont rate' (number - percent) 'nhsp ees' (number - currency) 'pen pay' (numb...

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]...

Error using Data Query within Excel 2000
I receive the error Invalid bracketing of name 'xls" hi not enough infomation. post example of "invalid Bracketing" also data query code. regards FSt1 "thunter" wrote: > I receive the error Invalid bracketing of name 'xls" ...

Report Results
I'll do my best I can to explain. I have a report that is based on criteria entered from a form. On the form the user has to input 2 sets of dates. The current month and the previous month(which would be the previous year). So if they put in 07/01/07-07/31/07 for the current year and 07/01/06-07/31/06 for the previous year they would expect shipping totals for each. Report: The report does not have a record source. But it does have 3 subreports within it. Sales Rep Totals, Market Categories & Parts/Service are the 3 subreports. How can I get my data to show up correctly if my ma...