tracking number of weeks on sale. help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm writing queries to track the sale of books. I want to track the number of weeks that a new book has been on sale and I use a weekly feed of data about the books. Conceptually, I know I should pull out the new ones that meet my criteria (which is a certain number of them on sale in the store), then label that first appearance as 'week 1' and then keep track of the weeks as time goes by.

Technically, i have NO idea how to do this. Can someone PLEASE help?? It would be MUCH appreciated!
 
Can you give an example of some of the data you enter each week. I have an
idea but it would help to see your data first so I don't suggest soemthing
that won't work.

Kelvin

Iris said:
I'm writing queries to track the sale of books. I want to track the
Click to expand...
number of weeks that a new book has been on sale and I use a weekly feed of
data about the books. Conceptually, I know I should pull out the new ones
that meet my criteria (which is a certain number of them on sale in the
store), then label that first appearance as 'week 1' and then keep track of
the weeks as time goes by.
 
Hi Kelvin --

Thanks so much for responding.

I'm new to all this, so am not quite sure what data would be helpful to you. The feed that I get has a whole bunch of stuff, but the only piece of data that I'm really interested in for doing this is the # of items in stock. Basically, I want to pull the items where the inventory was 0 last week and then 50 this week (indicating that it is new). I then want to stamp this week as the first week of sale, and then keep track of it as the weeks go by. Ultimately, I would like to end up with two columns -- one that says the week it went on sale, and then one that automatically calculates the number of weeks on sale.

Is that making sense? If there's anything else I can get you that would be more helpful, please letm eknow.

I came across the date difference function -- =DateDiff ("ww," [on sale week], [this week]) -- and feel that that COULD be a piece of the puzzle, but i'm just not sure how to get that to work... Especially since I don't really know how to sort out the items that I want in the first place.

I'm so lost!
 
Assuming your table has the folloiwng fields:

Book ID InvDate Stock
001 1/1/2003 50
001 1/8/2003 20
001 1/15/2003 5
001 1/22/2003 0

You want to know that book 001 went on sale on 1/1/2003 and lasted for 3
weeks. I suggest making a query of this table. Set the query to an
aggregate type (click on the summation button in the tool bar). In the
query, include as the fields

Book ID StartSale:Min(InvDate)
Length:DateDiff("ww",Min(InvDate),Max(InvDate))

For Book ID set the group row to Group By, and for the rest set to
Expression. This should give you the result:

001 1/1/2003 3

Let me know if you need more help.

Kelvin

iris said:
Hi Kelvin --

Thanks so much for responding.

I'm new to all this, so am not quite sure what data would be helpful to
Click to expand...
you. The feed that I get has a whole bunch of stuff, but the only piece of
data that I'm really interested in for doing this is the # of items in
stock. Basically, I want to pull the items where the inventory was 0 last
week and then 50 this week (indicating that it is new). I then want to
stamp this week as the first week of sale, and then keep track of it as the
weeks go by. Ultimately, I would like to end up with two columns -- one
that says the week it went on sale, and then one that automatically
calculates the number of weeks on sale.
Is that making sense? If there's anything else I can get you that would
Click to expand...
be more helpful, please letm eknow.
I came across the date difference function -- =DateDiff ("ww," [on sale
Click to expand...
week], [this week]) -- and feel that that COULD be a piece of the puzzle,
but i'm just not sure how to get that to work... Especially since I don't
really know how to sort out the items that I want in the first place.
 
Back
Top