HELP!! with complex recursive date query!

[rubetube56]

Hello all,

I need help writing a 'recursive' date query where the user would enter in the date range (ex. 1/05/06 - 11/11/06). What I want the query to do is go through each month and count up the number of orders, then go through all of the orders and give a total number of orders. Sounds simple in principle, but I just can't figure it out.

Any help is greatly appreciated. Thanks!

 

[traingamer]

How about

SELECT Sum(Order.Amount) AS SumOfAmount, Year([orderdate]) & "-" & Month([orderdate]) AS Mon
FROM Order
WHERE (((Order.orderdate) Between [startdate] And [enddate]))
GROUP BY Year([orderdate]) & "-" & Month([orderdate])
ORDER BY Year([orderdate]) & "-" & Month([orderdate]);

as a start?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rubetube56]

This didn't even compile for me

 

[lespaul]

perhaps if you post your table & field names along with some sample data and your expected results based on that sample, you'll get something a little closer to what you really need.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[rubetube56]

Well basically it didnt compile because I don't understand the Access SQL syntax that well.

For instance, I did not realize that Sum(Order.Amount) should be Sum([Order].[Amount]). That is just goofy SQL, that is why I don't understand Access that well.

 

[lespaul]

actually you only need brackets around table and field names that contain a space.

[Table Name].[Field Name], TableName.FieldName is valid.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins