Interactive Query to Reference user Defined Variables

T

Tim H

Good Day,

I am looking for a solution for where a user could interactively define up
to 50 criteria for returning a count of nonblank fields in a database of
approimately 3.4 million individual records. To put more specifically, there
is an incredible amount of permutations and I need for the users to be able
to define from 1 to 50 variables on demand, with variables that can range
from 1-27 each. Returning a count of non blank records.

Any assistance would be greatly appreciated.

Best Regards,

Tim
 
J

Jeff Boyce

Tim

No idea what you're talking about ... I can't visualize your data structure.
Can you provide an example?

"up to 50 criteria" ... does this mean you want to create a query that could
include zero, one, ... 50 different fields, or up to 50 different values in
a single field?

It may be that you can create a "dynamic" SQL statement, building it on the
fly from the criteria selected.

The responsiveness of the query will depend on both the total number of
records (your 3+ million) and on what indexing you have set.
 
T

Tim H

Hi Jeff, Thank you for taking the time to look at this.
The Data is stored in 115 Fields with approx 30,000 records inn each of
those fields - In excel terms 30,000 Rows X 115 Columns
The Data Itself is structered as follows (smaill sample)
Type Date Index1 Index2 index3 index4 index5
etc....
A 1/01/2000 -1 14 26
0 etc
B 1/01/2000 1 0
10

The interactive part in such that users will need to query by 1 to fifty of
these fields for example:
To Return a count of non blank records in All of the fields where the
interactive data would be: --- Where Index3=1 Where Type=A-C etc.....
The users could easily specify if they wanted to select all values for a
field - a specific one or a range.

This is currently being done on excel where all of this data is kept on a
spreadsheet using the dcount function and a range for criteri
=DCOUNT(Test!$B$2:$DX$28000,"Index27",Criteria!$A$4:$AX$5)/=DCOUNT(Test!$B$2:$DX$28000,"TotalCount",Criteria!$A$4:$AX$5)

The Range A4:AX5 is the Criteria where row 4 contains the field names to
filter by and row 5 contains the criteria - The values in row five will vary
by 1-27

Does this clarify at all?

Best Regards,

Tim
 
J

Jeff Boyce

Tim

If your table has 115 fields, there's a good chance that it is a ...
spreadsheet! To get full use of the features and functions of Access, you
need to use a well-normalized data structure.

Again, I can't tell from your description so far, but I suspect you'd find
your search task far easier if the data were normalized further.

Regards

Jeff Boyce
<Office/Access MVP>
 
T

Tim H

This was a spreadsheet at one time - Problem is that the data has been
refined and normalized as much as possible. What is left is used often and
necessary. I am not seeing many easy ways around this.

Thanks for your input,

Tim
 
J

Jeff Boyce

Tim

Perhaps your definition of "normalized" differs from mine...

A search in the tablesdbdesign newsgroup will reveal a strong consensus that
a well-normalized database (Access, SQL Server, whatever) table will only
rarely have as many as 30 fields.

There's no reason why you couldn't set up a well-normalized structure (empty
of data) in Access, then use queries against what you have now to populate
those tables.

I still (strongly) suspect your task would be much easier in a
well-normalized structure.
 
Top