Week No Design

[danz013]

Hi Guys,

I set up a sales system back in September for our business. We sell hot tubs. The database incoporates all sales info, we then use the info to generate invoices, delivery manifest etc.

We also use the database to produce reports and graphs of sales. It will show me a graph of the last 3 months sales for example.

The problem I have is that the system is built on week numbers. So, on the graphs for example, sales are group by their weekly amounts.

The problem I have is that when 2009 comes, it's going to be like the year 2000 for my database. When i created the database I didn't really consider how I'm going to deal with the problem of going from week 52, all the way to week one.

If I sell a tub in january, my graph will show a sale in week one, the rest of the year will all be black whilst sales will start again at week 30.

Do you guys have any idea how I could change the database to get around such problems?

Your help would be much appreciated.

 

[namliam]

Include the year as either a seperate field or in the weeknumber field.

Then sort by Year + Weekno to get your report right.

 

[danz013]

Ahh yes! Thanks alot!

Just a quick one. Can you remind me how to extract the year part of a date?

 

[namliam]

Year(date())

 

[gemma-the-husky]

i would consider just storing the appropriate date for the item

the "format" function can be used to automatically tranlate the date into a week number, within any queries. there are various user defnied settings to determine when your week one starts