how to query for items inactive for 3 months in a row?

[smidra]

Hi there,

I am a complete access newbie, so sorry for a stupid question:

I have a database containing product's reference number, number of products sold, month and year. Each row contains number of products with one reference number sold in the specific month. This is an example of what it looks like:

reference no. / products sold / month / year
205 / 2 / 9 / 2004
205 / 2 / 5 / 2004
205 / 5 / 7 / 2003
717 / 7 / 9 / 2004
717 / 8 / 7 / 2004
717 / 9 / 6 / 2004
717 / 1 / 4 / 2004
545 / 5 / 8 / 2004

Now what I need is to query for products, which have (since the first product was sold) not been sold for 3 months in a row.
(in the example table, products 717 and 545 are ok, product 205 is what I'm looking for)

Thank you for any help, I'm completely lost in this :/

 

[Malcy]

Hi
Your structure seems a little strange but ignoring that you would need to firstly define the month you are looking at (perhaps use a combo box on a form to select month and year).
This gives you the fact that the month you are looking at is 10 and the year is 2004. Ideally populate the combo from a table which allocates a month number for the month/year combination - so Jan04 = 1, Feb04 = 2, Mar04 = 3 so that you can work across year changes easily.
So your combo box value is going to be 10 (Oct04)
To do it properly you would use DateDiff but lets dodge that at the moment.
You then want a query which adds the sales for the last three months - so
Query 1 = reference no, product sales, month between (ComboBoxValue) and (ComboBoxValue - 2) - ie 10 (Oct) or 9 (Sep) or 8 (Aug). This will list all sales over the last three (or rather two and a half) months. If you want full 3 then amend the sum.
Query 2 = Base on Query 1 but use totals. Group on reference no. and sum on product sales.
Then build a form based on query 2 to list values where SumOfProductSales = 0

Bit clunky and not best route but will give you what you want I think.
Hope this helps

 

[smidra]

Thanks for your answer

the structure is a mess, it comes from an SQL select on a large database of an ERP system :/ actually the data is more complicated, but I listed only the columns which matter in this case...

I was trying to use the DateDiff function together with tha DateSerial (to create a date from the "month" and "year"), but I was only able to determine sales in the LAST 3 months, not in any 3 months in a row.

Thank you very much, I'll try doing it your way, right now it seems this might be just what I need

 

[ecniv]

SELECT qrySubProductIDs.prodid, qrySubTotals.ProdID
FROM [select tblProdSoldInMth.prodid 
from tblProdSoldInMth group by tblProdSoldInMth.prodid]. AS qrySubProductIDs LEFT JOIN [SELECT tblProdSoldInMth.ProdID, Sum(tblProdSoldInMth.NumbSold) AS SumOfNumbSold, CDate("1/" & [sMonth] & "/" & [syear]) AS CombinedDate FROM tblProdSoldInMth
GROUP BY tblProdSoldInMth.ProdID, CDate("1/" & [sMonth] & "/" & [syear])
HAVING (((CDate("1/" & [sMonth] & "/" & [syear]))>=CDate(Now()-90)))]. AS qrySubTotals ON qrySubProductIDs.prodid = qrySubTotals.ProdID
GROUP BY qrySubProductIDs.prodid, qrySubTotals.ProdID
HAVING (((qrySubTotals.ProdID) Is Null));

Monster Sql statement

Essentially it is
- All prodids available - please note that this is from the previous posts attachment, and it would be better to use a complete list of all ProdIDs held elsewhere
- All Summed totals from 90 days ago to now (and future)


Gizmo, you wrote in code yet used bound forms?

Vince