Build a Dimensional fieldname in a query

[MarcelMegens]

As example, I have a table with an Item number, introduction year and a number of historical and future Sales periods set per year, these sales columns are listed Y1990, Y1991, Y1992....... Y2015.

Based on each items introduction year, I want to list the first 5 years of sales.

I wanted to create a dimensional fieldname eg: FirstYear: "Y"&[introduction year] to get the value of that respective year. (I currently just get a text saying "Y1995", and not the content )

Any help is appreciated!


(Note: I can't transpose the data in the tables for other reason)

 

[ajetrumpet]

You can probably do this with the query tool, but in order to help you, I would personally have to see the actual table you are pulling values from (because of the complexity of the query).

 

[MarcelMegens]

Examples

if YearZero=3
this field should be something like this:
0A: [0]*[Models].["y"&YearZero] and should give me 0A: [0]*[models].[y3]

And A1

1A: [0A]*[Models].["y"&YearZero+1] should give me 0A: [0]*[models].[y4]

(The duplication by not linking the calculation Model is intentional, so 6 demand pattern are generated by item nr)

 

[ajetrumpet]

Marcel,

I understand the Item Number field, and the Roll-out date field, but other than that, I'm not quite following. How about answering some questions here:

**In your DEMAND table, what do the numbers represent in the "numbered" fields? This comment of yours:

a number of historical and future Sales periods set per year, these sales columns are listed Y1990, Y1991, Y1992....... Y2015.

doesn't make sense. There are no columns with names in 5-digit format. Does each column (that is numbered) in the DEMAND table represent a different sales period? If so, are those periods supposed to be yearly time frames? You say you have "historical" and "future" sales data. Which columns are the historical periods and which ones are the future periods?

Based on each items introduction year, I want to list the first 5 years of sales.

Well, the intro. years are well-defined, so there's no problem getting to that. Where do the first 5 years of sales come from? Columns 3-7 in the DEMAND table? Actually, if this is all you want, and I'm right about the "numbered" columns, the only thing you are going to need here (to view the data that you want to view, in one single query) is the IIF() statement.

I wanted to create a dimensional fieldname eg: FirstYear: "Y"&[introduction year] to get the value of that respective year. (I currently just get a text saying "Y1995", and not the content )

I don't see anything in your test query that relates to what you are saying here. I don't see any text like "Y1995".

**I would also help me a lot if you could explain what the "Calculation" numbers in the MODELS table mean. It looks like you created that table with a make-table query (based on calculations), but I can't tell what they are, because the query has been deleted (just mentioning this, it may not be relevant).

**Also, from your comment about the "link", I assume you want the duplicate records in the query that you are getting now. Just thought I'd point that out...