0

I'm having an issue with left outer join query in Microsoft Access. following query works fine in SQL Server but its not working in M.S Access. using C#..

Select Sales.ID as 'Sale ID', 
     Sales.Date,Customer.Name as 'Customer',        
     Sale_LINE_ITEMS.ITEMID as 'Item ID',
     Items.description as 'Item Description',Items.Model as 'Model',
     Items.brandquality as 'Brand Quality' ,
     Sale_LINE_ITEMS.qty as 'Quantity',
     Sale_LINE_ITEMS.UNITPRICE as 'Unit Price' ,
     SALE_LINE_ITEMS.discount as 'Discount' , 
     Sale_LINE_ITEMS.qty*Sale_LINE_ITEMS.UNITPRICE as 'Orignal Price',
     Sale_LINE_ITEMS.qty*(Sale_LINE_ITEMS.UNITPRICE-Sale_LINE_ITEMS.discount) as 'Discounted Price',
     Sale_LINE_ITEMS.TOTAL as 'Net Price'  
from Sales, Customer, Items, Sale_LINE_ITEMS 
left outer join Sales p 
  on p.ID=Sale_LINE_ITEMS.SID 
where Sale_LINE_ITEMS.SID=Sales.ID 
  and Sales.Cid=Customer.id 
  and Sale_LINE_ITEMS.ITEMID=Items.id 
  and Sales.Date in (select date from Sales where date like '"+month+"/%' and date like '%/"+year+"') 
order by Sales.Date,Sales.ID

enter image description here

MJB
  • 7,639
  • 2
  • 31
  • 41
Mujahid
  • 114
  • 1
  • 3
  • 14
  • Just curious why you are using both old and new styles of joins? Also, what error are you seeing? Does it give any indication of what the error is, or does it just return nothing? – MJB Nov 30 '15 at 18:00

2 Answers2

0

I think that this line may be your problem

select date from Sales where date like '"+month+"/%' and date like '%/"+year+"'

The wildcard symbol in msaccess is * try

"select date from Sales where date like '" & month & "/*' and date like '*/" & year & "'

Sheils
  • 323
  • 2
  • 22
0

I know this is rather old but for anyone looking, here's my take: You're mixing join methods by creating a list with commas but then adding a left outer join on the Table "Sales". Then you put a WHERE condition on Sales. This destroys the left outer join and it just becomes an inner join. SQL Server provides for this last part by allowing you to put conditions as part of the LEFT OUTER JOIN clause (not using the token WHERE) and SQL Server respects this condition while maintaining the join, but MS Access doesn't allow this.

halnwheels
  • 31
  • 2