Create a Sales Spend matrix

[Swifey]

Could you please help - Is there an easy way that I can create a sales matrix within access for example:

How may customers spend £10,000 plus, showing the number of customers and the total trading value
How may customers spend £50k - £99,999k, showing the number of customers and the total trading value
How may customers spend £25k – £49,999k, showing the number of customers and the total trading value
How may customers spend £10k - £24,999k, showing the number of customers and the total trading value
How may customers spend £5k - £9,999k, showing the number of customers and the total trading value
How may customers spend £2.5k - £4,999k, showing the number of customers and the total trading value
How may customers spend £00 - £2,499k, showing the number of customers and the total trading value

Example:
Matrix,,,,,,,,,,,,,,,,,No of customers,,,,,,,,,Trading Value
£10,000 plus, , ,,,,,,,,,,,,,,,,,,,,,,,,,,1,,,,,,,,,,133914.55
£50k - £99,999k, ,,,,,,,,,,,,,,,,,,,,,,,6,,,,,,,,,,437395.94
£25k – £49,999k,,,,,,,,,,,,,,,,,,,,,,,10,,,,,,,,,329815.29
£10k - £24,999k,,,,,,,,,,,,,,,,,,,,,,,,38,,,,,,,,,558374.03
£5k - £9,999k ,,,,,,,,,,,,,,,,,,,,,,,,,,,44 ,,,,,,,,305577.08
£2.5k - £4,999k ,,,,,,,,,,,,,,,,,,,,,,,,86,,,,,,,,,301199.32
£00 - £2,499k,,,,,,,,,,,,,,,,,,,,,,,,1735,,,,,,,,,,701820.13

Regards Swifey

 

[Parra]

What are the names of the fields? in your table.

An IIf function will need to be used and grouping, I can help but provide me with the field names.

Parra

 

[Swifey]

Hi my field names are as follows:

Customerno
linsellvalue

Thanks
Swifey

 

[Parra]

Create a query.

Add the field linsellvalue.
Look at the toolbar and press the sum icon, it looks like a big letter E
Go back to the field linsellvalue and where it says Grouping change to Where

In the column next to that in the field add:

Matrix: Iif([linsellvalue]>100000, “£100K”,Iif([linsellvalue]>50000, “£50K”,Iif([linsellvalue]>25000, “£25K”,Iif([linsellvalue]>10000, “£10K”,Iif([linsellvalue]>5000, “£5K”,Iif([linsellvalue]>2500, “£2.5K”,Iif([linsellvalue]>0, “£0K”,0)))))))

In the next column add the field Customerno
Where it says Grouping change to Count

Add the field Linsellvalue
Where it says Grouping change to Sum

It might be a bit confusing but I hope you understand, let me know if it helps. You should have 4 columns in your query.

 

[Swifey]

I'll have a go - Thankyou very much for your help Swifey

 

[Swifey]

Hi when I execute the query I get the following message

" Execute a query that does not include the specified expression "linesellvalue" as part of an aggregate function"

Swifey

 

[Parra]

Delete the first Linesellvalue and see if that helps.

 

[Swifey]

ok

 

[Swifey]

Perfect thank you for your help

Swifey