0

So I have an acces table : Data

Data is made of :

  • Client ID
  • Product ID
  • Sell Category ( exemple : Sell Dones, Sell Planed ,Forecast Sales) ,written all in letters
  • Quantity sold .
  • unitary price.

I am trying to build a single Acces query which would display things like :

|Client ID||Product ID||Total Sell Dones||Total Sell Planed||Total Forecast Sell||Quantity Sell Dones||Quantity Sell Planed||Quantity Forecast|

How can I do that ?

Like if I had only one type of sell and one single quantity I could simply do :

SELECT (sells*quantity) AS [Total Sells]

But the thing here is have different quantity and different types of sells could I put IF into the select ?

Something like :

Select (IF Data.Sell Category="Planned Sell" THEN quantity Sold*Unitary Price
ELSE 0) as [Total Planned Sell] , etc etc (for every single one ?

The thing is I am using acces 2003 and I dont think I can create a sql function in acces 2003 and use it in my select , but if I can I am willing to do so , else if I can only use "select,delete,update " in acces 2003 I guess I will have to do it like this

Andre
  • 26,751
  • 7
  • 36
  • 80
Dodge
  • 31
  • 3
  • I think you are looking for a [crosstab query](https://support.office.com/en-us/article/Make-summary-data-easier-to-read-by-using-a-crosstab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8) – Andre Jul 28 '17 at 10:41
  • looks like it could be my solution. Thank you for the edit and thank you for your answer. – Dodge Jul 28 '17 at 10:50
  • Advise not to use spaces in names. And looks like two crosstabs, review http://allenbrowne.com/ser-67.html#MultipleValues – June7 Jul 28 '17 at 23:40

0 Answers0