Union query field names

[ram]

Hello I was looking for help on the following questions.

I have imported an Excel file that tracks agents montly sales. The imported
table has the following three field names:
Agent ID Jan-1-2006 Feb-1-2006

What I would like is a union query that
has the following filed names:
Agent ID Date Sales

I don't know how to fill the date for each record.

Thanks for any help

 

[Douglas J Steele]

SELECT AgentID, #2006-01-01# AS SalesDate, [Jan-1-2006] AS Sales
FROM MyTable
UNION
SELECT AgentID, #2006-02-01# AS SalesDate, [Feb-1-2006] AS Sales
FROM MyTable

Strictly speaking, it's not necessary to repeat the field names after the
first subselect, so you could get away with

SELECT AgentID, #2006-01-01# AS SalesDate, [Jan-1-2006] AS Sales
FROM MyTable
UNION
SELECT AgentID, #2006-02-01#, [Feb-1-2006]
FROM MyTable


Note that I'm using SalesDate rather than just Date. Date is a reserved
word, and shouldn't be used for your own purposes. If you absolutely must
use Date, put square brackets around it:

SELECT AgentID, #2006-01-01# AS [Date], [Jan-1-2006] AS Sales
FROM MyTable
UNION
SELECT AgentID, #2006-02-01#, [Feb-1-2006]
FROM MyTable

 

[ram]

Thanks Douglas

SELECT AgentID, #2006-01-01# AS SalesDate, [Jan-1-2006] AS Sales
FROM MyTable
UNION
SELECT AgentID, #2006-02-01# AS SalesDate, [Feb-1-2006] AS Sales
FROM MyTable

Strictly speaking, it's not necessary to repeat the field names after the
first subselect, so you could get away with

SELECT AgentID, #2006-01-01# AS SalesDate, [Jan-1-2006] AS Sales
FROM MyTable
UNION
SELECT AgentID, #2006-02-01#, [Feb-1-2006]
FROM MyTable


Note that I'm using SalesDate rather than just Date. Date is a reserved
word, and shouldn't be used for your own purposes. If you absolutely must
use Date, put square brackets around it:

SELECT AgentID, #2006-01-01# AS [Date], [Jan-1-2006] AS Sales
FROM MyTable
UNION
SELECT AgentID, #2006-02-01#, [Feb-1-2006]
FROM MyTable


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello I was looking for help on the following questions.

I have imported an Excel file that tracks agents montly sales. The imported
table has the following three field names:
Agent ID Jan-1-2006 Feb-1-2006

What I would like is a union query that
has the following filed names:
Agent ID Date Sales

I don't know how to fill the date for each record.

Thanks for any help