Re: Interactive Query to Reference user Defined Variables

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
spread*** using the dcount function and a range for criteria
=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


"Jeff Boyce" wrote:

> 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.
>
> --
> Regards
>
> Jeff Boyce
> <Office/Access MVP>
>
> "Tim H" <TimH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:8316D47E-8F2D-4ED7-98E3-8649C3C05939@xxxxxxxxxxxxxxxx
> > 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
>
>
.