Make Table from variable fields

[RECrerar]

Hi,

Firstly I've put this in here as it seems to cover most access areas and I'm sure it will involve VBA.

For some of the projects in my database (the once with a certain customer) we get back a rating on our performance. By boss would like us to be able to anaylse that feedback against multiple different criteria such as Year, Business Sector, Site etc

My vision on how this would work is that there would be a form on which you could select as may criteria as you wanted (out of a shortlist of sensible ones) to compare the data against and then click a button and a report would be generated showing all the anaysis grouped by different criteria.

My tables are set up as shown below. (the underscores are just spaces for the text)

tblMainData______tblTEB_Result
ProjectID_------__ProjectID
Site ____________Res1
BusinessSector___Res2
Year____________Res3
.. ______________Res4
... _____________Res5
..

All of the criteria are defined as text all of the results are numeric.

I am thinking that in order to produce this output i need a table/query with the following fields, where group relates on one of the comparison criteria (Site, Year etc), SubGroup is the possible options for that group (2008, 2007, 2009) etc and ProjectID is needed only to lookup futher project inforation as and when necessary:

tblGenReport
Group
SubGroup
ProjectID
Res1
Res2
Res3
Res4
Res5


My original thought was to use an append query with variable filed names to do this, however I found this link while searching which suggests creating an array in VBA and then using that to create the field.

http://www.access-programmers.co.uk/forums/showthread.php?t=102913&highlight=append+query+variable

So my questions are:

Does this sound like a sensible method to produce this output? I'm still fairly new to Access so this will tyake me a while to do and I'd rather not spend too long if I'm going down totally the wrong route.

If so, Is the option of producing an array containign all the data and then loading this into a table a better method than using append queries to produce this output?

If so, How do I fill a table from an array? I''m sure this is fairly simple and could find out but hey, worth asking?

Does anyone have any better idea?

Hope that all makes sense

Regards
Robyn

PS, not sure if it makes any difference but I will want charts in my report

 

[MSAccessRookie]

I am going to have to pass on a response to your question, but I would like to offer a little Forum assistance if I may. When you want formatted data, treat it as CODE. Surround the block with code markers "[ code ]" and "[ / code ]" (without the embedded spaces), and your post looks like this:

[FONT=Courier New]tblMainData      tblTEB_Result[/FONT]
[FONT=Courier New]ProjectID        ProjectID[/FONT]
[FONT=Courier New]Site             Res1[/FONT]
[FONT=Courier New]BusinessSector   Res2[/FONT]
[FONT=Courier New]Year             Res3[/FONT]
[FONT=Courier New]..               Res4[/FONT]
[FONT=Courier New]...              Res5[/FONT]