Pivot by multiple condition

[harryklein]

Hi,
I would need to create a query from the table below:
ID, Key, Name, Period

It would be a Pivot, as I need the sub-amounts of two different months, like June 2018 and Sept 2017.
I managed to write a query using the Transform and Pivot functions, but when I add the 2nd period, it results the same amount for both periods which is obviously incorrect, and the graphic desgner doesn't even allow me to edit it like this.
What am I doing wrongly?

TRANSFORM Sum(addSupSource.Eur) AS SumOfEur
SELECT addSupSource.Name, addSupSource.EUR, addSupSource.sKey, addSupSource.Period
FROM addSupSource
WHERE (((addSupSource.Name) Like "*Doe*") AND ((addSupSource.Period)=#6/1/2018# Or (addSupSource.Period)=#1/9/2017# Or (addSupSource.Period)=#1/9/2017#))
GROUP BY addSupSource.Name, addSupSource.EUR, addSupSource.sKey, addSupSource.Period, addSupSource.Period
PIVOT addSupSource.Period;

Thank you

[Minty]

You appear to have added the same period as a criteria twice ?

(addSupSource.Period)=#1/9/2017# Or (addSupSource.Period)=#1/9/2017#))

And your dates are confused - one is formated dd/mm/yyyy, and the other mm/dd/yyyy

Perhaps show some sample starting data and your expected results in table format?

[harryklein]

No, I didn't want to use the same period twice, I was confused a bit..
So What I would like to get back is:

Name, sKey, sumOfPeriod1(=September, 2017), sumOfPeriod2(=June, 2018)

(the sum of amounts were entered for a Name on an account in Sept 2017 and June 217. I need to compare them so I need two different numbers)
I have one period field as Date and an amound field as Number.

All date is stored with the first day of the month, there is no like 2nd, 3rd etc of any month)
Thank you.

[Minty]

Okay - but in your original query you used

(addSupSource.Period)=#6/1/2018# Or (addSupSource.Period)=#1/9/2017# 
                      mm/dd/yyyy                         dd/mm/yyyy

So one date is formatted incorrectly based on your statement that

"All date is stored with the first day of the month,"

My tip - make a simple select that pulls in the records you want with the correct criteria.
Now change that into a totals query and check again and make sure it's still pulling the correct data.

Now either save that and create a crosstab query from it or try converting it to a cross tab changing the criteria based fields to Where clauses (do this in the designer).

[harryklein]

Yes, this is how I arrived to the code I posted.
I guess, I am not aware well enough of the rules of creating pivot.

[Minty]

Can you stripdown any confidential data and post up your basic table data in a zipped database?
This should be pretty straight forwards to solve.

[harryklein]

Can you stripdown any confidential data and post up your basic table data in a zipped database?
This should be pretty straight forwards to solve.

Yes, that is possible, is there a way to attach a file here?

[Minty]

Yes in the bottom of the reply window is a "Go Advanced" option, click that and you will see a paperclip "Attachments" option.
You will need to compact and repair the stripped down version, and then zip it, as the file size is pretty restricted.

[harryklein]

file attached

[Minty]

Does this give you what you were after?

TRANSFORM Sum(myImport.EUR) AS Amount
SELECT myImport.sKey, myImport.Name
FROM myImport
WHERE (((myImport.Period)=#8/1/2017# Or (myImport.Period)=#7/1/2017#))
GROUP BY myImport.sKey, myImport.Name
PIVOT myImport.Period;

[harryklein]

Yes, absolutely, thank you very much. In the meantime I understood even the logic .
Thanks again for your efforts, I would by you a drink, if we can arrange it

[Minty]

I happily accept most forms of Alcohol as payment

[harryklein]

if you have paypal and tell me how much usually do you pay for a beer, I ready to keep my promise

[Minty]

Not required - assistance is provided FOC.

Edit - And my local pub is "bloody expensive !"