Prev: parent child link query
Next: Querying for Extended ASCII Characters
From: mark on 14 Apr 2010 12:47
I'm trying to create a query such that when the user runs it, the proper
columns (fields appear). We have 12 columns, one for each month of our
fiscal year (which goes from July to June). Basically, we run the query once
a month. When we run it, for example in April, the months prior to April &
including April (this would be July thru April), will have $0. The months
after April (May and June) will have dollars. When we run this query in
April, we'd rather not see each of the columns for July thru April because
all the records have all $0 in those columns.

Searching the web, I've seen things regarding crosstab queries and union
queries, but I don't think I've figured them out. One way I think I could do
this would be to have 12 queries (each one including/excluding the
appropriate months). Then, on a form the user selects the appropriate month
(or maybe the month would be discerned from the run date if I could figure
that out). Then, the appropriate query would run based on the month selected
(or from the run date). But I'd rather not create 12 queries if I didn't
have to.

In reality, we have several groups of fields which have a field/column for
each month, so if there's a way to eliminate $0 fields/columns from the query
(which we later export to Excel for manipulation), that would be helpful.

The sources for my query are 2 joined queries, which are the result of many
queries. I'm using MS Access 2003.

Thanks,

~Mark

From: KARL DEWEY on 14 Apr 2010 13:58
>>We have 12 columns, one for each month of our fiscal year (which goes from
July to June).
You need to fix your data structure. What you have now is a spreadsheet and
not a relational database. Do not have a field per month, have a date field
and a data field.

Use a union query to correct your data like this --
SELECT #1/1/2010# AS MyDate, [January] AS MyData
FROM MyTable
UNION ALL SELECT #2/1/2010# AS MyDate, [February] AS MyData
FROM MyTable
UNION ALL SELECT #3/1/2010# AS MyDate, [March] AS MyData
FROM MyTable
....
UNION ALL SELECT #12/1/2010# AS MyDate, [December] AS MyData
FROM MyTable;


--
Build a little, test a little.


"mark" wrote:

> I'm trying to create a query such that when the user runs it, the proper
> columns (fields appear). We have 12 columns, one for each month of our
> fiscal year (which goes from July to June). Basically, we run the query once
> a month. When we run it, for example in April, the months prior to April &
> including April (this would be July thru April), will have $0. The months
> after April (May and June) will have dollars. When we run this query in
> April, we'd rather not see each of the columns for July thru April because
> all the records have all $0 in those columns.
>
> Searching the web, I've seen things regarding crosstab queries and union
> queries, but I don't think I've figured them out. One way I think I could do
> this would be to have 12 queries (each one including/excluding the
> appropriate months). Then, on a form the user selects the appropriate month
> (or maybe the month would be discerned from the run date if I could figure
> that out). Then, the appropriate query would run based on the month selected
> (or from the run date). But I'd rather not create 12 queries if I didn't
> have to.
>
> In reality, we have several groups of fields which have a field/column for
> each month, so if there's a way to eliminate $0 fields/columns from the query
> (which we later export to Excel for manipulation), that would be helpful.
>
> The sources for my query are 2 joined queries, which are the result of many
> queries. I'm using MS Access 2003.
>
> Thanks,
>
> ~Mark
>
 | 
Pages: 1
Prev: parent child link query
Next: Querying for Extended ASCII Characters