Use a Table to drive Query field selections

[Joelle921]

I have 5 tables which were set up across rather than down - ie
fields are Jan04,Feb04...Sept04,Oct04...Jan05,Feb05...Aug05,Sept05

I currently have a query/report which is pulling the correct rolling12
months from the tables - but it requires the query/report to be
modified each month - ie change the Oct04 field to Oct05

I'd like to create a table that would select the correct fields -
ie
01-2005 use Jan04fill,feb04fill...dec04fill,Jan04Service,feb04Service
02-2005 use Jan05fill,Feb04fill...dec04fill,Jan05Service,feb04Service
03-2005 use Jan05fill,Feb05fill...dec04fill,Jan05Service,feb05Service

I've gotten it to work in test by directly modifiying the report's
field controlsource but as there are 168 controls - performance is an
issue

is there any way to use the table to select the fields in the query??
I could then make a table which would be replaced each month that would
have the fields which need to be displayed on the final report

Thanks in advance

 

[Van T. Dinh]

Quote:

"I have 5 tables which were set up across rather than down - ie
fields are Jan04,Feb04...Sept04,Oct04...Jan05,Feb05...Aug05,Sept05"

That will foreever be the problem for you since the structure violates the
Relational Database Design Theory. You have repeating groups (each group
consists of one Field). In addition, this structure means that you need to
add Fields as time goes by. The consequence is that you cannot design the
Queries / Forms and Reports for the ever increasing number of Fields (well
there is a limit of 255 Fields max in a Table but that is about 200 more
than you should ever need for a properly-structure Table). At least, you
will need to modify the design of objects regularly.

In a properly-design and implemented database, you should never add Fields
just to handle adding regular data (Fields are only added for changed
requirements). Additional data should be handled by adding Records and not
Fields. John Vinson says: "Records are cheap, Fields are expensive" since
additional Fields require design change of other Access objects that follow
from the Table.

Another the problem is that you store data in your Field names that database
engine cannot get. Jan05, Feb05, ... are data in your database and they
should be stored in Field values and NOT Field names.

Suggest you check out RDDT books and find out how to re-structure your
database / Tables before continuing with designing other objects in your
database.

 

[Chris2]

I have 5 tables which were set up across rather than down - ie
fields are Jan04,Feb04...Sept04,Oct04...Jan05,Feb05...Aug05,Sept05

As has already been mentioned, the above table structure is something
of a problem.

I currently have a query/report which is pulling the correct rolling12
months from the tables - but it requires the query/report to be
modified each month - ie change the Oct04 field to Oct05

I'd like to create a table that would select the correct fields -
ie

Tables cannot select columns. Tables just sit there until Queries are
executed against thems.

01-2005 use Jan04fill,feb04fill...dec04fill,Jan04Service,feb04Service
02-2005 use Jan05fill,Feb04fill...dec04fill,Jan05Service,feb04Service
03-2005 use Jan05fill,Feb05fill...dec04fill,Jan05Service,feb05Service

I've gotten it to work in test by directly modifiying the report's
field controlsource but as there are 168 controls - performance is an
issue

You could, in theory, construct a system of VBA DAO code that would
alter the Queries and Reports in exactly the rolling system you'll
need.

But I wouldn't want to be the one who had to write it. All that
procedural code would go around defeating the purpose of MS Access.

What you really want to look at is:

1) Are you designing/maintaining this db? If so, fully normalize the
db structure. This *will* solve your current problem.

2) Are you stuck with the db structure you have (for whatever reason)?
If so, then you may well want to consider a data extraction operation
that "normalizes" the data in intermediary tables for your reporting
purposes. This will also solve your problem.

3) Go with continuing manual alterations to everything.

4) Write the VBA DAO nightmare I mentioned above.

is there any way to use the table to select the fields in the

query??

Yes, via VBA DAO code.

You would loop through the fields collection to locate the field
names. Then, you would assemble an SQL statement by concatentating
the field names with the SQL statements. Changing the Reports would
be considerably more involved.

Good luck.


Sincerely,

Chris O.

 

[John Spencer]

If (repeat if) you are stuck with the current structure, then the only way I
know to handle the situation would be to build the SQL using VBA. You could
probably build the entire SQL as you need it.

Your best bet is to restructure your tables. Without further information, I
can only suggest that part of the design would include a field that
contained what you are now using for field names.

 

[Joelle921]

Since I dont' own or admin this db or the others that also use the
above tables - a complete restructure is out of the question.

So assuming I can extract the info and create my own normalized tables
- with each month having a seperate row - is there an easy way to the
take all of these fields and report them in a crosstab manner by month
with YTD and Rolling 12 totals

ie Jan Feb Mar Apr ..... Dec ... YTD Rolling12
Service
Turn
WOS
LT
...
GMROI

Crosstab doesnt work as I can only return 1 value and there are 15 that
I need on this report. Ive tried creating 15 different crosstabs
pulled together in 1 query but performance is lousy - especially if I
allow user criteria selection

I have another similar db that is normalized I ended up creating a
report that had a subreport for each month and total - but it's been a
REAL pain as whenever they make a change I need to change all 14
subforms

any thoughts??

thanks

 

[Chris2]

Since I dont' own or admin this db or the others that also use the
above tables - a complete restructure is out of the question.

So assuming I can extract the info and create my own normalized tables
- with each month having a seperate row - is there an easy way to the
take all of these fields and report them in a crosstab manner by month
with YTD and Rolling 12 totals

ie Jan Feb Mar Apr ..... Dec ... YTD Rolling12
Service
Turn
WOS
LT
..
GMROI

Crosstab doesnt work as I can only return 1 value and there are 15 that
I need on this report. Ive tried creating 15 different crosstabs
pulled together in 1 query but performance is lousy - especially if I
allow user criteria selection

I have another similar db that is normalized I ended up creating a
report that had a subreport for each month and total - but it's been a
REAL pain as whenever they make a change I need to change all 14
subforms

any thoughts??

thanks

Joelle921,

Many, but I'm not operating on enough information to say.

Although meant for microsoft.public.sqlserver.programming, the
following
link is still applicable for microsoft.public.access.queries:
http://www.aspfaq.com/etiquette.asp?id=5006, and is excellent when it
comes to detailing how to provide the information that will best
enable
others to answer your questions.


Sincerely,

Chris O.