Interactive Query to Reference user Defined Variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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>
 
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
 
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.
 
Back
Top