Flexible reporting with variable data types including lookups

[RECrerar]

HI, I've put this in general as the way I am doing things seems very long winded and so I was wondering if anyone had any better ideas. This is also the reason for the long explanation. For people with less time my current specific issue is the bit in bold at the end.

I was hoping someone would be able to give me some ideas on the best method to do the following.

My boss wants a great amount of felxibility on the output that can be generated by the database I'm creating primarily that any field can be compared about any other and a simple report with min max count etc with some nice graphs can be shot out.

I'm restricting this to fields in the main table "tblMainData", this table contains a lot of project data, some fields are text, some dates, some numbers and the vast majority contain IDs that are used to look up the field value from another table "tblCodeValues".

My thinking is that I have a form that the user uses to select which fields they want to analyse. This contains two list boxs. The first "lsMainCriteria" is a single select list box in which the user choses the main field they're interested in. The second "lsSubCriteria" is a multi select list box from which the user selects the fields they want to compare to the main criteria. Selections in this listbox will be limited to only those of similar datatypes (text + lookup fields, numeric, date) and then depending on the data type one of three corresponding simple generic reports will be populated with the data.

The problem I am having currently is with the lookup values as obviously the default value returned without doing a lookup is the codeID rather than the text. My first thought was to initially create a query that has all the looked up values in it and then base the rest of the process off this. However the first part of the flexibility comes from a filter by form form which works nicely off the original data but is very slow when based on a query of looked up data, so I thought I would leave the looking up of the actual values till as late in the process as possible when there will be the minimum amount of records and fields.

This is leading me to needing to include a Dlookup in an SQL statement I am constructing in VBA and I can't figure out quite where I need the quote marks to make it work. How do I write a dlookup in SQL withing VBA? I have tried:

 strLookUpSubRptQry = "DLookup([CodeNameShort], tblCodeValues, [CodeID]=[" & Me.lsSubCriteria.Column(1, varCategory) & "]) AS Field" & k

and

strLookUpSubRptQry = "DLookup('[CodeNameShort]', 'tblCodeValues', '[CodeID]=' [" & Me.lsSubCriteria.Column(1, varCategory) & "]) AS Field" & k

and a variety of other methods and still can't get it to work.

Both followed by

StrFullSQL = "SELECT [ProjectID], " & strLookUpSubRtpQry & " FROM qryFiltered

(this is a bit shorter than the actual code)

so if anyone can help with the specific issues or have any suggestions on other means to perform this task it would be appreciated. I know this isn't quite how Access is designed to work and I do have a backup to export the data to Excel to let them play about with it here, but I really would like to be able to incorporate this feature, and I'm sure it can be done however long winded

 

[RECrerar]

Ha typical after spending all that time writing the post I finally figured out the syntac it should be:

strLookUpSubRptQry = "DLookup('[CodeNameShort]', 'tblCodeValues', '[CodeID]=' & " & Me.lsSubCriteria.Column(1, varCategory) & ") AS Field" & k

Still if anyone has found some glaring issues with the way I am trying to do this, please still let me know

 

[The_Doc_Man]

In the "absolute" worst case (i.e. so general as to defy sanity), I would consider code that stepped through the table's collection of FieldDefs (i.e. Tables("tablename").Fields collection) to scan for the named fields to validate them. In such a scan, you would be able to know whether the field types were compatible.

However, if the tables are fairly static, I would consider pre-building some definitional tables that are copies of some parts of your metadata, then using the methods defined in the topic "Cascading Combo Boxes" as a way to control what is visible for a given lookup. At some point, I would have to build a dynamic query that contained the results of all the combo-box drop-down selections.

After that, there is the issue that the pretty graphs and such would have to built on-the-fly and we have had mixed reports of success for such attempts. MS Graph and Access sometimes have these ... moments ... where they don't get along so well. Which shouldn't stop you from trying. However, it might be that all you can do is build a query for which you could then manually build your graphs and do your separate statistical queries.

 

[gemma-the-husky]

What I generally do in most apps is provide an option that enables a full set of data with all potentially useful fields to be exported to a spreadsheet

then the user can filter and graph whatever info he wants, without slowing down your database. They might need to learn a few more excel skills, but its probably a lot easier than you writing loads of different extracts in access, that rarely get used, or even worse, trying to define a generic all purpose extract routine

 

[RECrerar]

Thanks for the responces.

I am definately going to have the export to Excel option but would like to have something else as well.

What I currently have is a table that contins the field names and a column to define the data types (since for example the fields containing IDs are actually defined as numeric but in the reports I would want them to be classed as text.

I then have a form that has on the left a listbox containing all fields from which the user can select one as the prime comparism field, and on the right a combo box containing the data types (text, date, numeric) which is used to filter a second listbox from which the user can select as many fields as they want as long as they are of the same data type.

I then use code create an array of data that is used to populate another table tblTempReportGen (not actually a temporary table in the true form) which has 4 fields to use for the reports as follows. Not to sure how to explain so I will use an example.

Say you have the following records being analysed

[B]LeadSite        Status          Importance          .........[/B]
STV              Won                X                    ....
PTH              In Progress       H                    ....
STV              Lost                M                    ....
STV              In Progress       L

And you were anyalsing LeadSite as the primary field against Status and Importance

I end up with a table with data below

[B]PrimayName  PrimaryValue   SubName           SubValue[/B]
LeadSite         STV                 Status              Won
LeadSite         PTH                 Status               In Progress
LeadSite         STV                 Status               Lost
LeadSite         STV                 Status               In Progress
LeadSite         STV                 Importance          X 
LeadSite         PTH                 Importance          H
LeadSite         STV                 Importance          M
LeadSite         STV                 Importance          L

Currently I onlu have this working for text fields and so I use a pre-existing table but my plan is to use code to create a temporay table which will alow me to vary the data type of the second and 4th fields.

I then create the report off this table with grouping on the SubName field. The chart is in the group footer and always has the X-Axis defined by the primary value and the y axis by the sub value of the current group. They are very simple charts (especially for the text values - just a count) The plan is to have a maximum of 9 pre created reports - one for each possible combination of data type (e.g Text-Text, Text-Numeric, Numeric-Date, Date-Numeric) and to call up the appropriate one.

At the end of the report I want to include the raw data for the selected fields, this is the part that is going to be tricky as obviously different fields will require different amounts of space so I will have to change the record source and location of the text boxes at runtime and also check that they fit. My current plan is to add another field to the table that defines the data type containing the required text box width for different fields and then use this to position the text boxes with code to check the total width of the report and if it exceeds the maximum start either start another line of values or pop up a message box asking the user to chose fields to exclude from the raw data (alternatively this check could be done first, which might actually be the better option)

So that is my current thinking. I know it seems like a lot of work for relatively small reward but that is what they want and I have about a week to get it sorted - plus I get paid overtime!

Does that sound like a reasonable method to you?

Also the other thread I have just posted is to do with this - because of the method I've chosen I will end up with lots of re-used code, which I would like to split up into small subs that I can call as and when required, but most of the code involves working with record sets and I can't seem to use a recordset if it is not all containied in the same sub. Why is this and is there a way round it?

(I am on Access 2003)