Make Table Query Question

Hello,

I'm having some difficulties creating a make table query. The source
table which I'm basing the make table query has the fields:

CIF
BusLine1-2001
BusLine1-2002
BusLine1-2003
BusLine1-2004
BusLine2-2001
BusLine2-2002
etc..etc.

What I am doing is grouping by CIF summing across all other fields and
creating a table. This works fine for the time being but on a month
to month basis the field names will change, some business lines may
have 2004 revenue in say Oct which didn't in Sept. I'm trying to
create this db so that very little changes to the queries will be
needed on a monthly basis. Is there a way to write a make table query
that groups by a known field (CIF) and sums across all other fields?
Someone sent me a function which takes in a recordset as a parameter
and spits out an array of the field names in that recordset, could I
use this somehow in my make table query or is there an easier way to
go about this?

Any help on this matter would be greatly appreciated.

Thanks in advance,

Sincerly,

Hanif Merali

re: Make Table Query Question

Question:

Why would the field names change from month to month?


"Hanif Merali" <rukee_@hotmail.com> wrote in message
news:44bfd18e.0411090637.3a29e600@posting.google.c om...[color=blue]
> Hello,
>
> I'm having some difficulties creating a make table query. The source
> table which I'm basing the make table query has the fields:
>
> CIF
> BusLine1-2001
> BusLine1-2002
> BusLine1-2003
> BusLine1-2004
> BusLine2-2001
> BusLine2-2002
> etc..etc.
>
> What I am doing is grouping by CIF summing across all other fields and
> creating a table. This works fine for the time being but on a month
> to month basis the field names will change, some business lines may
> have 2004 revenue in say Oct which didn't in Sept. I'm trying to
> create this db so that very little changes to the queries will be
> needed on a monthly basis. Is there a way to write a make table query
> that groups by a known field (CIF) and sums across all other fields?
> Someone sent me a function which takes in a recordset as a parameter
> and spits out an array of the field names in that recordset, could I
> use this somehow in my make table query or is there an easier way to
> go about this?
>
> Any help on this matter would be greatly appreciated.
>
> Thanks in advance,
>
> Sincerly,
>
> Hanif Merali[/color]

re: Make Table Query Question

The reason the files change is because someone sends me pivots of the
source files, the source files look like this:

CIF, Business, Year, Amount, RR, etc..etc..

the end result is to be able to create a report, there are 29 seperate
businesses, and thousands of CIF's (clients). The end result has to be
a table that has,

CIF, Business-PY, Business-CY, Business-YTD, .....

there will be PY, CY, YTD fields for all businesses

originally i had written queries to query each of the 3 source files
group by CIF, Year, sum across the AMOUNT and RR fields and update the
end result table, the way I went about this was to make temp tables for
each of the source files and update the end table from those temp
tables. This required too many queries..Is there a better way for me to
do this? The problem I am having is that the PY=2002, CY=2003, YTD=2004,
but when 2005 hits that all changes to 2003, 2004, 2005 respectively. I
also need revenues from 2001 to create a reconciliation report. The way
I went about it was to create crosstabs for each year and source file,
so source1 would have 2001, 2002, 2003, 2004 crosstabs with business
along on one axis and CIF on the other then use those crosstab temp
tables to update the report and have a macro that deletes them
afterwards. Is there a way to update the final result table with fewer
more complex queries? Any help is greatly appreciated.

Hanif

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Make Table Query Question

Why not keep all the values (Present Year, Current Year and Year To Date,
the same and add an extra field to capture the year. This way you dont have
to change the field names.

You know something is wrong with a database design when you have to keep
changing its structure to accomodate the data.

HTH
Paul

"Hanif Merali" <rukee_@hotmail.com> wrote in message
news:4191ab2d$0$14458$c397aba@news.newsgroups.ws.. .[color=blue]
>
>
> The reason the files change is because someone sends me pivots of the
> source files, the source files look like this:
>
> CIF, Business, Year, Amount, RR, etc..etc..
>
> the end result is to be able to create a report, there are 29 seperate
> businesses, and thousands of CIF's (clients). The end result has to be
> a table that has,
>
> CIF, Business-PY, Business-CY, Business-YTD, .....
>
> there will be PY, CY, YTD fields for all businesses
>
> originally i had written queries to query each of the 3 source files
> group by CIF, Year, sum across the AMOUNT and RR fields and update the
> end result table, the way I went about this was to make temp tables for
> each of the source files and update the end table from those temp
> tables. This required too many queries..Is there a better way for me to
> do this? The problem I am having is that the PY=2002, CY=2003, YTD=2004,
> but when 2005 hits that all changes to 2003, 2004, 2005 respectively. I
> also need revenues from 2001 to create a reconciliation report. The way
> I went about it was to create crosstabs for each year and source file,
> so source1 would have 2001, 2002, 2003, 2004 crosstabs with business
> along on one axis and CIF on the other then use those crosstab temp
> tables to update the report and have a macro that deletes them
> afterwards. Is there a way to update the final result table with fewer
> more complex queries? Any help is greatly appreciated.
>
> Hanif
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]