query help please

[jlaurer]

Hey all, I am having a little trouble with three queries i need to make, The first one is how do i find out if any products are being held in inventory that are not selling, 2nd is Is their any customers who have not made a purchase this year, and 3rd is are the sales growing or shrinking. With this last one i figured you would just look sum up how much they made per year but when i am looking i only see they have 2011 results, anyone see anything diff?


I have 5 tables
i cant post the file cause its to big but i will post a pic of the relationships to show what fields there are.

[aytee111]

Your form picture was too small for me to read.

1 - one or more queries - total qty in inventory by product, total sales for a period (whatever period that is - last 6 months? year?) - for all zero qty's list the product

2 - customer linked to sales, total the qty sold and display zero qty's

3 - again there is a need for period - by month? by year? total sales for each period (using DatePart) by product(?) then use a crosstab query. This can also be made into a graph which will impress your bosses!

[jlaurer]

Thanks for the response. Although i do not really understand what you mean on some of those responses. I have posted anther picture. So on the first one, what do you mean one or more queries?So your sayin take my qtyonhand and also prodname, than how do i go about getting sales for 1 year? on number two i am taking the qtyordered suming it by total and also have the productname listed as well. in criteria i am putting <1 for qtyordered but nothing is showing up. and the third one what is datepart?

[aytee111]

1. THe reason I said one or more queries is that I have seen these kinds of things done in one query but I am not that advanced so always do it separately so that I can check each step of the way. So if no one else is answering you then this is all I can say! I think I can read the new picture.
a) Ordered qty - use the Ordline table, total the qty, group by product and year - use the function DatePart to get the year (google it)
b) when you have those qty's, make a new query based off that one and bring in the Product table, join by Product, ignore inventory qty=0 and ignore Total qty (from first query) > 0
That should give you all products with a qty in inventory with no sales. Are you doing this one year at a time? In the first query, will you check that the year is 2011? It is probably best to do it one year at a time, much simpler. Then you don't need to use DatePart, you can just use Between Jan 1 and Dec 31. Probably less confusing.

2) There is a query wizard which allows you to find non-matches (I've just remembered). Use the wizard to find records on the Customer table with no matching records on the Orders table. Again you can refine the query by putting in date criteria the same as before. The product name is probably what is causing you problems as if there are no orders then there will be no products.

3) If your period is annually, sales per year, then you will need DatePart. Total the order qty by year. Use the crosstab query wizard to give you the progressions (first create the select query then after that the crosstab one using the select query as its basis)

Hope this helps.

[jlaurer]

what do you mean group by product and year? Like do i put them in the query but uncheck the show box? what do you mean bring the product table in? and how u say for the first query >0 and 2nd is =0?i dont have a way to group by the whole month, the order date is like 1/1/2011.)
thanks for your help a lot!!

[aytee111]

Have you tried using the wizard for all your queries? You really are very green!

Start at the very beginning. Do the first query (1a), select the table you need, select the columns you need, enter your criteria, click on the totals button. Get this query working, don't even think about the others at the moment - focus!