Querieing Sales Forecast per month

[Blake290]

We have a database of our sales forcast. Each manufactured item is forcasted per each monday of the month. Usually there are 4 records for each item in each month. Again, one record for each Monday. The fields in the records that I want to use are-Example:
[Item Num] [Date] [Qty]
400369 07/07/03 4
400369 07/14/03 5
400369 07/21/03 5
400369 07/28/03 6
400369 08/04/03 5
...

I want to query to allow the user to either enter the month desired, or have the user enter the begining date and end date. Then I want to reflect the total months forcast per item. I also want an adjacent field to reflect the remaining months forcast based on the time of the report is executed or in other words, based on the end user's computer clock.

I was able to format the date using the format command to change the date to appear like "mm yyyy". I thought this might make it easier to get the whole months forcast, but when I try to enter the criteria of "07 2003" on the formatted date, it comes back with nothing-blank.

I am aware of the "Now" function, to enter the criteria of the computer's clock. This would be used to gather the remaining month's forcast. However I don't know how to gather just the remaining month's records and ignoring the next month's records.

When All is done I need to look like this example based on the above table:

[Item Num] [Month Forcast] [Remaining Forcast]
400369 20 6

Blessings be upon you!

 

[PaulBricker]

Well, maybe I can help a little. To get a set of records based on a month and year "07 2003" out of your query, you would put an expression in the Field row of a new column

MyCriteria:Month([Date]) & "/" & Year([Date])
Then on the criteria line for that field put this
[Please enter month and year]

Then if you enter a value like

07/2003

you should get your records. The trick will be dealing with leading zero's in your month value. You will have to enter your critieria exactly as the info is displayed in the query so if your field MyCriteria displays
7/2003
then if you enter
07/2003
you won't return any records. So you can start here and see if it helps. Then if you can explain how your two examples relate to each other, it would help with the rest of the calculations. I don't see any relationship between you you list of records and your final sumation.
Any clarification will help.

Paul

 

[Blake290]

Thanks Paul,

The remaining forecast is the forecast that has not been consummed yet. In this case, the record in my table with the date of 7/28/03 would be the remaining forcast for the month of July. All previous records have been consummed. Yet I would like to reflect the total for the month and what remains yet in the forecast schedule for this month. Right now if I were my end user, he would be concerned about July. The July toatl forecast for item number 400369 is 20. The remaining forecast on the scedule for next Monday is 6. Next Monday is the last Monday in July.

Does this make sense?

I did figure out the date thing by using my second option of having my user enter a start and end Date. I used the criteria; Between [Please Enter srart] And [Please enter end]

To get the remaining forecst quantity, I'v taken an example of someone's use of the "Now" function: >=Now()-3 I don't know what the ()-3 stands for? I'm guessing that it truncates /03 of off 07/23/03 to leave the result of 07/23.

The problem is the this use of the "Now" says give me everything after today. I only want the everything after today, but within the current month. Could I do this > Now()-3 and = Now()-6? This is assuming that Now ()-6 is equal to 07 in the date 07/23/03.
Blessings
Blake

 

[PaulBricker]

Now() - 3 should return three days prior to today. I don't see that helping you. To get the number of whole days left in this month using Now() you can use this expression.

DateSerial(Year(Now()), Month(Now())+1,0)- Date()

I use the Date() function because Now() would return partial days based on Time. The DateSerial function gives you the last day of the current month. If you need to get the last Monday, that will take a different function. You could start by using some criteria like this and see how it goes.

Between DateSerial(Year(Now()), Month(Now()), 0) And Now()

will give you the dates between the first day of the month and Today.

Between Now() And DateSerial(Year(Now()), Month(Now())+1,0)

will return the days between Today and the end of the month.
Work with these and let me know where it gets you. You can substitute the Date() function for Now() if you don't see the values exactly as you expect them. If we need to work from Monday to Monday let me know and I may be able to write a function that will handle it that way.

Paul

 

[Blake290]

Thank you Paul. This is brilliant! I am amazed that there is such flexability that I can filter Dates like this. This is very valuable to me!

Bless You! Thank you! Bless you!....for your selflessness in takeing the time to help me.


Blake

 

[PaulBricker]

That's great Blake. Thank you. Let me know if you run into any other problems.

Paul