MS Access SQL Question - Obtaining field name from last transaction.

[GreenTomato]

Hello,

I am trying to achieve the following using SQL in MS Access. The file I have is formatted in the following manner:

Field names: Customer Name, January 2012, February 2012, March 2012 etc.
Customer Name: Shows customer name
[Month] 2012: Shows the dollar amount purchased. Some are zeros.

That's all the information in the set of data.

I'm trying to write a SQL command that will create a new column in the table or in a report that will produce the date of the last purchase. For example:

Note: Data is from left to right:
Customer Name: John Smith
January 2012: $100
February 2012: $0
March 2012: $50
April 2012: $100
May 2012: $0
June 2012: $0
etc.

I'm hoping I can create a column that will automatically show "April 2012" as the output (in a new column) for John Smith, thus identifying when the last purchase was.

Thank you for all of your help and I apologize for the basic question.

Have an excellent weekend to you all!

 

[PHV]

I'd use a normalization union query as a base for an agregate query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GreenTomato]

Thank you for your response.

Would you mind posting what the code looks like? I'm sorry, I'm relatively new to SQL.

 

[PHV]

A starting point:
SELECT [Customer Name], MonthName(Max(theMonth)) FROM (
SELECT [Customer Name], 1 AS theMonth, [January 2012] AS theAmount FROM yourTable WHERE [January 2012]<>0
UNION SELECT [Customer Name], 2, [February 2012] FROM yourTable WHERE [February 2012]<>0
...
UNION SELECT [Customer Name], 12, [December 2012] FROM yourTable WHERE [December 2012]<>0
) U
GROUP BY [Customer Name]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886