Query that picks different column from a table based on the inp va

[Luda]

I have to create a query that will pick different column based on the month.
ID january february march
1111 20 30 40
2121 20 30 40
3333 20 30 40
If I'll create a table or form that will have january as inp value how can i
make a query to select just January column, and next month just February etc

 

[Jerry Whittle]

If that is how your table looks, you have a problem. For example what happens
when you reach january of 2010? You already see the problem with data in
different columns and the trouble with finding it.

Your table should look like this:

ID TheDate TheValue
1111 1 Jan 09 20
1111 1 Feb 09 30
1111 1 Mar 09 40
2121 1 Jan 09 20
2121 1 Feb 09 30
2121 1 Mar 09 40
and so on

Notice that the middle field is actually a date. That way you can just ask
for a certain month and year in the criteria and return just those records.
You can even store multiple years worth of data this way.

 

[karl dewey]

Use a union query to correct your data structure --
SELECT ID, #1/1/2009# AS TheDate, [january] AS TheValue
FROM YourTable
UNION ALL SELECT ID, #2/1/2009# AS TheDate, [february] AS TheValue
FROM YourTable
UNION ALL SELECT ID, #3/1/2009# AS TheDate, [march] AS TheValue
FROM YourTable;

 

[Allen Browne]

If you build a table like that, you have built a spreadsheet in Access; you
have not built a database.

A relational database would have a related table, where you add a row each
month, instead of many similar columns in the one table.

The fields of this table will be like this:
- TheID Number relates to the ID of your main table
- TheMonth Date/Time first of the month, (e.g. 1/1/2009 for January)
- TheValue Number the 20 or 30 or whatever number is for this
month.

Now there is only one field to search to read the number, and you use
whatever criteria you want under TheMonth to return just that month's
values.