Home Page link  

MS Query and SQL - getting records when there is no supplier

  
Groups | Search | About
User Password
Register Now! Lost Password?

Point-Of-Sale Software - - MS Point Of Sale software discussed here

 Post an article  get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content  add this group's latest topics to your Google content  YahooMyWeb Yahoo!  Google Google  Windows Live Favorites Windows Live  del.icio.us del.icio.us  digg digg  Add to Netscape Netscape
Subject Author Date
MS Query and SQL - getting records when there is no supplier Jason 11-29-2006
Posted by Jason on November 29, 2006, 11:38 am
Please log in for more thread options
 I am trying to write a query where the following columns are present:

Item.Description
Item.Price
Department.Name
Supplier.Name

The problem is that when I set up the joins in MS Query, the records that
don't have an associated supplier assigned are not returned.

When I try to change the join type on Item.SupplieID-Supplier.ID, it says
outer joins are not allowed with more than two tables in the query.

Any ideas on how I can get it to return all records even if there is no
supplier?

Thanks,

Jason



Posted by Irina on November 29, 2006, 1:13 pm
Please log in for more thread options
 Hello Jason,

Use the following SQL statement:

select item.description, item.price, supplier.suppliername, department.name
from item
        left join supplier on item.supplierid=supplier.id
        left join department on item.departmentid = department.id

Please tell me if this is what you are searching for.

Irina Stanca


"Jason" wrote:

> I am trying to write a query where the following columns are present:
>
> Item.Description
> Item.Price
> Department.Name
> Supplier.Name
>
> The problem is that when I set up the joins in MS Query, the records that
> don't have an associated supplier assigned are not returned.
>
> When I try to change the join type on Item.SupplieID-Supplier.ID, it says
> outer joins are not allowed with more than two tables in the query.
>
> Any ideas on how I can get it to return all records even if there is no
> supplier?
>
> Thanks,
>
> Jason
>
>
>

Posted by Glenn Adams [MVP - Retail Mgmt on November 29, 2006, 2:07 pm
Please log in for more thread options
I'm not familiar with MSQuery, but if it will let you insert the SQL
command directly, it would be:

select item.description, item.price, department.Name, supplier.Name
from item
left join department on item.departmentid = department.id
left join supplier on item.supplierid = supplier.id

Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@tibercreek.com
----------------------------------------------
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information.


Jason wrote:
> I am trying to write a query where the following columns are present:
>
> Item.Description
> Item.Price
> Department.Name
> Supplier.Name
>
> The problem is that when I set up the joins in MS Query, the records that
> don't have an associated supplier assigned are not returned.
>
> When I try to change the join type on Item.SupplieID-Supplier.ID, it says
> outer joins are not allowed with more than two tables in the query.
>
> Any ideas on how I can get it to return all records even if there is no
> supplier?
>
> Thanks,
>
> Jason
>
>

Posted by Jason on November 29, 2006, 2:51 pm
Please log in for more thread options
Glenn,

Kiss yourself for me. Worked perfectly.


> I'm not familiar with MSQuery, but if it will let you insert the SQL
> command directly, it would be:
>
> select item.description, item.price, department.Name, supplier.Name
> from item
> left join department on item.departmentid = department.id
> left join supplier on item.supplierid = supplier.id
>
> Glenn Adams
> Tiber Creek Consulting



Similar ThreadsPosted
Update Supplier ID By Deparment ID SQL Query March 16, 2008, 4:38 pm
Have Stores Create Records in HQ March 14, 2006, 9:22 pm
Credit Card Records April 11, 2006, 2:35 pm
total records in item table December 8, 2005, 7:45 pm
Returning records from Recordset object November 26, 2006, 12:26 am
HQ users should be able to add ship-to records from the stores. February 3, 2007, 1:51 pm
Worksheet 250 too large with 5000 records? June 21, 2007, 1:39 pm
Notes from Customer Records disappearing December 21, 2007, 9:57 am
remove time card records August 9, 2008, 7:45 am
Insert missing records into ItemDynamic (HQ) November 12, 2008, 6:05 pm

Contact Us | Privacy Policy
This site is not affiliated with Intuit - makers of Quickbooks and Quicken software
This site is not affiliated with Sage Software - makers of Peachtree accounting software
XML SitemapXML Sitemap