Re: Interactive Query to Reference user Defined Variables

Tech-Archive recommends: Fix windows errors by optimizing your registry

Tim

If your table has 115 fields, there's a good chance that it is a ...
spread***! 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>

"Tim H" <TimH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:706311BE-6FD0-415E-B082-6FFA3C4D6A6A@xxxxxxxxxxxxxxxx
> 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
> >
> >

.

Quantcast