Select columns based on user input range

[MeeMee]

Hi,

I have a table in access which has the following columns:

ID Pname Aug10 Sep10 Oct 10 Nov10 Dec10

and more column for the months until the year 2015.

I have a form which has two comboboxes that lets the user selects the start MMMYY and the end MMMYY. I am not able to find a way to write a query that selects the correct columns based on the user's input.

For example if the user selects Aug10 and Nov11, the result should be the data and columns between these two selections.

Aug10, Sep10, Oct10 ........ Nov11 columns should be selected.

Any suggestions ?

Thanks.

[Stewart Ross]

Sorry, but your current table design is not even in first normal form - you have the year-month repeated as a separate column (a repeating group). You will not be able to design queries to retrieve such data properly without normalising your data first. You can calculate year-month totals using a crosstab query for instance - but in this case you would just be querying on a transaction date in your table. You should NEVER be storing totals under month headings as attributes of your table.

We have an article about database normalisation and table structures which gives an excellent introduction to this topic.

What you have at present is what you may well have had if you were working in Excel - but Excel is at heart a powerful calculating system for flat-form tables, not a relational database.

-Stewart

[MeeMee]

ya i know that it is not normalized, problem is they want me to use the excel sheet and import in access and not make changes, i knew it is impossible to do but thought of asking if there are other indirect ways , thanks anyway.

[Mihail]

"They", 100%, are not programers. So, "they" should never say HOW TO solve a problem. If they know "how to" why they don't accomplish the task ? On the other hand, YOU are programmer. So, you can explain that you can solve the problem BUT in your own way. Or... leave them and looking for a clever owner.

I know: this isn't an Access answer. But, I encountered the same problem as MeeMee a few years ago. And, after I try different amiable ways to solve the situation, I must leave my job.

Good luck, MeeMee !
And sorry again to the forum members and visitors.

[NeoPa]

Why not consider separating the data out into normalised form after you import the data from Excel?

[Mihail]

Sorry again for my previous post. It was one of my bigger frustrations.

I think NeoPa suggest should work. But I think (again) that is better to manage dates using Excel tools. Create new sheets in Excel. In this sheets arrange your dates in a normalised tables (using Excel tools) then import this tables in Access.

Related:
Provide more information about the whole task. Maybe someone have better ideas.