First 10 months of sales, seems simple, but proven to be a horrorcase.

[MarcelMegens]

I have a table with historic sales values, so an Item has 36 months starting (Field names are ranging from "Oct 04", "Nov 04", "Dec 04" ... to "Sep 07")
I also have a column name First month of Sale. which is filled with for example "Dec 04", for the next Item is might be "Jun 05" or "Aug 06").

I would like to get a Query where I just show sales values of the first 10 months after that first month of sale.

I have tried, and tried, and tried till my head exploded
It seems a simple task, but I am obviously overlooking something.
can anyone help me with some hints?

 

[MStef]

I have improvised something.
Look at "Demo10MonthsA2000.mdb" (attachment).
Look at Table1, Query1, Module1.
In query, column "fxDate:" is a date depending on column "f3FMoS".
In query, column "10mDate:" is a column "fxDate" + 10 months.
You need to adapt Module1.
I think it can help you.

 

[MarcelMegens]

Better example and Table attached here!

Thanks!

Helps a little, but not what I was looking for. I have attached a visual example of what I want. I have also created a Sales table with the right field names. Hope you can help.....

 

[stopher]

First and foremost, your data is badly structured for use in databases such as access. You need to Normalise it.

I've attached an example of how your data should be structured and also there is a query which gives the result you require.

hth
Chris

 

[stopher]

Oops, I only did 8 months but I'm sure you can work out how to make it 10 months.
Chris

 

[MStef]

I'm sorry, it is a big job.
Your table "Sales2 is not normalized well.
There are many redundance.
You have got "Dec 04" or "Feb 05" and s.o. in every record though it is 0 in it.
You have to normalize yor tables, (learn something about DATABASE NORMALIZATION).
In future use a DATE/TIME (Data type) for the field "First month of Sale", (not text).