Next date formula?

[unc2plo]

I get a list of items from my supplier that has the weekending dates certain items are on sale. An item could be on sale any number of times in a year - every week, or every 3rd week. It is completely random.

I currently have a query that tells me every occurance in the next 2 weeks. What I want to do is buy enough product on the current deal to make it until the next sale date (whenever that is). Is there any sort of criteria I can enter that will show me only the next date an item is on sale, but not show me every upcoming date? There could be 10 more on the list, but I am only concerned with the next date.

Does that make sense?


Thanks,
David

 

[clares]

Hi David

Unless I have read it wrongly,

The problem I would have is How do you know how much to order, if you don't know how long it is too last you!

Kindest Regards

 

[unc2plo]

I don't know how much to order because I don't know when the next sale date is. It could be different for different items.

It could be on sale next week, or 5 weeks from now. If it is on sale next week I would buy just what I needed to make it through next week, but if it is on sale in 5 weeks I would buy 5 weeks worth.

I know how many units I sell to my customers a day, so I can figure out how many to buy based on teh next sale date. My goal is to only buy when an item is on sale, and buy enough to make it to the next sale date.

Thanks,
David

 

[clares]

I'm sorry not sure that I can help. I work in a stationery retail outlet to coperate businesses and we operate a stock and order system and we have regular special offers from manufactuers and suppliers alike. However we often know when a 'special is coming up and how long it will lasts for.

We generally have items that are conisdered as being stocked items and they are ordered regardless. When these items are on offer we would stock up on them anyway and perhaps (according to volume of sales) order say three months worth, maybe six months. It doesn't matter because your going to sell them anyway (nonshelf life items) and take advantage of the best price. We would look at the movements over the past three months say, take away the current stock level, and order the difference.

There may be other factors that we taken into consideration,but it depends on size on company, available warehouse facilities, turnaround etc

Kindest regards

 

[bat17]

How to do it will depend on your data structure. You must have some sort of indicator to show if a price is a Sale or Normal price and you should be able to filter on that field.

Peter

 

[unc2plo]

The list they send us only has the item number, the sale price and the effective dates. So I know that item 12345 is on sale the week of 10/24, 11/14 & 11/21.

What I want to do is pull a query taht will tell me when teh next sale date is after today, but only the next sale date. I would want to buy enough on 10/24 to make it through 11/14, but on 11/14 I would not buy as much since teh item is on sale the following week as well.

The next effective date of the sale price is reallly what I need to get at.

Thanks,
David

 

[bat17]

You could probably do it with a group query with the date field set for Min and with a criteria of greater than today.

HTH

Peter

 

[unc2plo]

Sorry I was off for a few days.

Anyway, Here is an excel shot of what the original table looks like. As youcan see Item number 1940 is on promo on 11/8 and again on 12/20. I just want to see the 11/8 date, but not teh 12/20.

Does that make sense?

Next Date Q.xls
	A	B	C	D
1	Item #	Effective Date	EndDate
2	1940	08-Nov-03	05-Dec-03
3	1940	20-Dec-03	16-Jan-04
4	2142	01-Nov-03	21-Nov-03
5	2142	27-Dec-03	16-Jan-04
6	2270	01-Nov-03	21-Nov-03
7	2270	27-Dec-03	16-Jan-04
8	2271	01-Nov-03	21-Nov-03
9	2271	27-Dec-03	16-Jan-04
10	2273	29-Nov-03	19-Dec-03
11	2273	03-Jan-04	23-Jan-04
Next Date Q