Calculating MTD and YTD Sales for report

[alsoszaa]

I have been stuck on this one for a while. I have an Access 2003 Database that I need to calculate the MTD sales and the YTD sales of parts ordered

Here are the relevant tables and thier attributes
Part -
PartNum
Description
UnitPrice
OnHand
ReorderPoint

Orders -
OrderNum
OrderDate
CustomerNum
CustomerPONum

OrderDetail -
PartNum
NumOrdered (Quantity)
QuotedPrice
NumShipped

If I have left anything off that may be vital please ask and I will supply the info. I need this info ASAP though.
I think the answer has something to do with the Date() and CurrentDate() but I have no clue. Please let me first know the queries I need to run and that should be it. I just need to put MTD and YTD Sales in a report.

I REALIZED THIS SHOULD BE A QUERY THIS WILL BE POSTED IN QUERY INSTEAD

 

[Kempes]

I think this may help.
In your query, Go to a fresh column and insert this into the field.

DateFormula: Year([OrderDate])

Leave Table and Sort blank.

In Criteria enter the following.

Year(Date())

Check the results and you should find it works.

Same can be done using Month instead of year.

 

[Kempes]

Alternatively, and easier.

Type this is your OrderDate Criteria.

Year([OrderDate])=Year(Date())

(Same for Month)

 

[alsoszaa]

Sorry I tried the first suggestion and I got a column with the years listed

the second suggestion I got a column with "-1"
I need to have the field post the sum of the cost of all parts ordered in the current year and another query with the sum of the cost of all parts ordered for the current month.

 

[Kempes]

Can you post the db?

 

[alsoszaa]

How do I post it?

 

[Kempes]

Use the Post Reply (not quick reply) and there will be a paperclip icon.

 

[alsoszaa]

Copy of Database for help with Part cost total for YTD and MTD

Hopefully this worked...

 

[Kempes]

Not sure if this is what your after but have added a YTD and MTD query.

I have also altered some data to test it.

Your date field was set a Text not Date/Time.

As for totalling, I usually do this on the report itself or you can use a cross tab query with YTD or MTD and include it in your Report as a Sub Report.

 

[alsoszaa]

Actually...

This has one little flaw

here is the objective:

For each part, I need to list the following in a report

PartNum
description
UnitPrice
MTD Sales
YTD Sales
InStock
Allocated
ReorderPoint

I need to group the PartNum field so it only shows each individual part. then adds the total sales of that part for the year and for the month

Last but not least, I need it to show an asterisk (*) for each part for which the InStock is less than the reorder point.

Is this possible???

 

[alsoszaa]

Don't worry about it anymore. I figured it out!