EOF error from joined tables - need query help.

[dcwave]

This is probably simple to solve.
I have two tables - sales and checklist.


Sales Table
ID - AutoNum
Date - Date
CustName - Text
SaleAmount - Currency

Checklist Table
ID - AutoNum
SaleID - Num
IntroLetter - Date
Review -Date
InstallComplete - Date

I need to create a page that shows the "pending completetion" sales.

Because the Checklist table has no data yet, the query I had returns no records (EOF - True?)

How do I write the query to return the custname where InstallComplete = ""?

I've tried

SELECT sales.DateSold, sales.CustomerName
FROM checklist INNER JOIN sales ON checklist.SaleID = sales.ID
WHERE checklist.InstallComplete=""
GROUP BY sales.DateSold, sales.CustomerName

This did not return anything.

Thanks for the help

 

[DotNetGnat]

do a LEFT OUTER JOIN instead of INNER JOIN

-DNG

 

[gmmastros]

SELECT sales.DateSold, sales.CustomerName
FROM   sales Left JOIN Checklist 
         ON checklist.SaleID = sales.ID
         and checklist.installcomplete=""
GROUP BY sales.DateSold, sales.CustomerName

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

DNG,

Since the 'left' table is included in the where, you effectively get an inner join even if you specify left join. Moving the where condition to the join condition should make it work properly.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dcwave]

Thanks for the quick replies.


I tested this

SELECT sales.DateSold, sales.CustomerName
FROM   sales Left JOIN Checklist 
         ON checklist.SaleID = sales.ID
         and checklist.installcomplete=""
GROUP BY sales.DateSold, sales.CustomerName

The error I get when running it in Access directly is "Join Type Not Supported"
Sorry, I should have mentioned that I am connected to an Access db.

The checklist.installcomplete="" is what thows the error.

Any thoughts?
Thanks

 

[gmmastros]

Try...

SELECT sales.DateSold, sales.CustomerName
FROM   sales Left JOIN Checklist 
         ON checklist.SaleID = sales.ID
Where  checklist.installcomplete Is NULL
GROUP BY sales.DateSold, sales.CustomerName

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dcwave]

Thanks George, I think that worked!