column header in access query

Question

I have a query that calculates totals for Year 1, Year 2, and Year 3 (these are my column headers).  I have a separate table X - that has values for Year 1, Year 2, and Year 3 -- these are not static years.  Is there a way to code into the query to pull in the Year values as headers from this table X?

Answer 1

If I understand correctly, I expect you would need a unique field in the first table (tblLinda). I added an autonumber field [ID].

Then create a cartesian/union query (qcarLinda) like:

SELECT ID, YearNum, [Year 1] AS TheValue

FROM tblLinda, TableX

WHERE [PeriodNum]=1

UNION ALL

SELECT ID,YearNum, [Year 2]

FROM tblLinda, TableX

WHERE [PeriodNum]=2

UNION ALL

SELECT ID,YearNum, [Year 3]

FROM tblLinda, TableX

WHERE [PeriodNum]=3;

Then create a crosstab based on qcarLinda:

TRANSFORM First(qcarLinda.TheValue) AS FirstOfTheValue

SELECT qcarLinda.ID

FROM qcarLinda

GROUP BY qcarLinda.ID

PIVOT qcarLinda.YearNum;

Answer 2

Also as a side note - these two tables do not have any common fieldsto link on.

Without something in common it would be like tossed salad.

How is your query 'headers'  Year 1, Year 2, Year 3 derived?

Post the SQL of your query.

Answer 3

currently my query has these three columns with the headers Year 1, Year 2, Year 3

table X has two columns setup as follows:   PeriodNum    YearNum

                                                                       1              2012

                                                                       2              2013

                                                                       3              2014

table x is not static and the years will always change, how can I change the Column headers in my query to read 2012, 2013, 2014 instead of it currently reading Year 1, Year 2, Year 3

I hope this explains it better.  Also as a side note - these two tables do not have any common fields to link on.

Answer 4

 Is there a way to code into the query to pull in the Year values as headers from this table X?

I do not follow - post example of data and what you want as result.