SQL Query- Totally Lost? 1

[wayne2715]

I have 3 tables, Stock,Sales and Customers

The Stock file links to the Sales by StockCode, the Sales Links to the Customers by CustCode
The Stock file has a field that on specified products has Points allocated to it.

I need to produce a report where each customer has all of the Stock items that has points allocated to it listed regardless of whether there have been any sales or not. Eg:

Customer1 StockItem1 Salesqty=1
Customer1 StockItem2 Salesqty=0
Customer1 Stockitem3 Salesqty=2
Customer2 StockItem1 Salesqty=0
Customer2 StockItem2 Salesqty=0
etc.

I cant get my query to produce this.

Here is what I have so far.

SELECT
customer.`title`,
stock.`stockcode`,
stock.`title`,
stock.`zpoints`,
sales.`qtyinv`
FROM
`stock` stock LEFT OUTER JOIN `sales` sales ON stock.`stockcode` = sales.`stockcode`,
`customer` customer
WHERE
sales.`custcode` = customer.`custcode` AND
inventry.`zpoints` > 0.00
ORDER BY
customer.`title` ASC

Please can someone help me with this. Thanks

 

[HowardHammerman]

How can you link customers to stock if a customer has never purchased a particular item from stock?

Can't be done.

You can start with Customer, link to sales, then to stock.
Use left outer join both times.

That will give you all customers and their sales and the stock items that they purchased. But if a customer never purchased an item it won't selected.

Re-think your report. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[balves]

You can do it, but you need a little help beyond the 3 tables. You can only join what you have, so first you have to create something that has all possible combinations.

I'm just going to assume that you're using SQL Server or Oracle, although I'm sure it will work the same in Access.

- You need to create a view that shows all possible combinations of customers and stock items. This is a Cartesian product and usually you don't want one that's too big. Because you're selecting from 2 tables by not joining them. Like this:

CREATE VIEW vw_allcustomers_and_stock AS
SELECT Customer.Title, Stock.Title
FROM Customer, Stock
WHERE Stock.Zpoints > 100
ORDER BY Customer.Title

Then you can use that View in your Crystal Report and LEFT JOIN it to the Sales table. It's downhill from there.

 

[wayne2715]

Thanks balves, excellent I have done all you suggested in access and the result was perfect. I then loaded the query into Crystal however Crystal wont let me add a table after the query. It also wont let me add 2 querys. Is this normal?

I appreciate you help so far and if the above is the case I guess ill have to learn how to do reports in Access real quick.

Thanks Wayne
wayne@intersol.co.nz

 

[rogar]

Yes, that is normal. When you use a query or stored procedure as the data source, you must get all the data for the report in a single query or stored procedure. You cannot link a table and a query or two queries in CR. rewrite your query to include a link to the table you want to add.