Basic help with Access

[AccessFailure]

Hello,

I need to be able to use Access to give me a list of all our customers, and then a total sales figure for each month of the year.

The live data will come in as a table of all our orders, which will mean that there is more than one entry for each customer, these would need to be totalled depending on the month.

In excel terms this would need a SUMIFS, but I wonder if this is possible to do in Access, and if so how to do it roughly?

Thanks

[CJ_London]

without knowing your full table structure, I would suggest you need a group by query - something like

SELECT CustomerID, Year(OrderDate) As OrderYear, Month(OrderDate) as OrderMonth, Sum(Sales) As SumOfSales
FROM myTable
GROUP BY CustomerID, Year(OrderDate), Month(OrderDate)

[AccessFailure]

That's helpful thank you, will I have to do a seperate query for each month or can I run all the months off one query?

My date format is dd/mm/yyyy

[CJ_London]

the code I provided will run for all months

[AccessFailure]

Sorry where do I put that code?

[CJ_London]

in a query - what I have provided is 'pseudo code' (names are made up) because you haven't provided details of your table and field names so you will need to change them to match your real names (don't forget to use square brackets if you have spaces or non alphanumeric characters in your names). Hopefully you haven't used any reserved words in your names.

Open a new query, close the table selection window and then click on the sql option in the ribbon (top left) then copy and paste in the code and change the names to suit. You can then go back to the query design view so you can see what the query looks like in a GUI

In the future, if you want a response so you can simply cut and paste, provide details of your table and field names/types (text/number/date etc)

[AccessFailure]

Ajax,

Thanks for your help.

At the moment my data is in Linked ODBC tables, is it OK to use the data straight from here or does it need to go into a normal table first?

[CJ_London]

you should be able to run it from your linked odbc table (replace myTable with the name of your odbc table)

If in doubt - try it

[AccessFailure]

Thank you I will give it a go