Hi folks... I'm hoping someone can help me with a query... I'm using Access 97, and I need to do a query which I will then use for a sales report. The sales report needs to compare 'this month's sales' with 'last month's sales.'

I have an 'Order' table, with fields

order #
bouquet name
quantity of bouquets
order date

I need to display how many of each bouquet were sold this month, and how many last month.

My date field is formatted dd/mm/yy

I'm wondering if I need to do two seperate queries and then group those results correctly on my report?

Any advice would be much appreciated!

You can nest an IIF() function inside the Sum() function in a Totals query.

Type/paste in the SQL View of a new query (using the correct field names):-

SELECT Order.[bouquet name],
Sum(IIf(Format([order date],"yymm")=Format(Date(),"yymm"),[quantity of bouquets],0)) AS [This Month],
Sum(IIf(Format([order date],"yymm")=Format(DateAdd("m",-1,Date()),"yymm"),[quantity of bouquets],0)) AS [Last Month]
FROM [Order]
GROUP BY Order.[bouquet name];

Jon, thankyou, that works perfectly, returns exactly the information I need...

I would just like to say, I did take the time to read the help in Access, and also to search these forums before I posted here begging!

=]

Also, I'm wondering, would that query have been possible using criteria in design view, rather than doing the SQL?

Thanks again for your help.

Yes, you can do it in Design View. If you switch the query to Design View, you can see how Access has placed the query in the grid for you.

When you build a Totals query in Design View, you can click on the Totals button on the tool bar to display the Total: row in the query grid.

Thanks Jon, you've been a great help. I'm not a huge fan of databases, I much prefer erm, I'm not sure what I prefer, but I don't particularly enjoy Access! But I must say, you and this forum have proved very useful, and I'm sure that I'll be trying out a few more things as I continue reading in these forums.

Thanks again.

=]