M
Martin Newman
I have a database (grossly simplified) that records lumps of
lands(sites), the openers/managers of the sites, and events that are
associated with particular people on particular sites. (A particular
person may be associated with many sites and a particular site may be
associated with many people).
In "plain English" we are recording sending letters to site owners/managers
and their responses and our responses to their actions.
I have four tables:
A Sites table - represents bits of land.
Joined one-to-many to
A Sites_person_Event relationship table - (this table is also linked one
to one with a persons table)
Joined one to many to
An events table which contains dates, event type indicator & records
events associated with the particular person/site combination.
An added complication is that some of the Site_Person relationship records
are marked as "critical" with a boolean variable.
Now the database works just fine - no problem. The sites and people
tables both contain several hundred upto low thousands of records, the
events table rather more.
I am writing an automated query generator which enables users to
interrogate the database using a forms interface, in fact building really
quite complex SQL behind the scenes. Whilst the code for this requires
care to write, again it is generally fine and works.
But I am slightly stuck on trying to generate one particular sort of
query.
What I want is a query that shows me all the sites where ALL the critical
contacts have had ALL of a predefined set of events done to them. (In
plain English an example might be show me all the sites where we have
asked all critical contacts for permission to visit and got a positive
reply.)
Now I can write a mixture of code and queries to do this but what I was
wondering is is it possible to do this kind of thing purely in JET SQL. I
feel with the skillful use of IN clauses, and maybe counting entries
satisfying particular criteria, I ought to be able to, but I cannot quite
see how. Being able to do it entirely in SQL would enable me to fit this
type of query easily within the confines of the current user interface I
have for the user building her own queries, so I'd really prefer to do it
as an SQL query if at all possible.
Any thoughts?
I did try an alternative strategy which I could live with. I did manage
to develop a query which returned me all the sites where ALL the critical
contacts have had one particular event done to them. I then did inner
joins between all the queries (using the site and people primary keys) for
each of the events I was interested in (maybe only 3 or 4 at the most).
Unfortunately, although each constituent query ran in maybe 20 secs the
joined big query gobbled both CPU and memory and crashed access -
presumably something to do with the way Access optimisation works (or not
apparently), though I confess to not really understanding what was
happening.
This is all running on 2Ghz-ish machines with 512MB ish, so hardware isn't
a problem. Running Ac97 (As that is what my client has) but I could
probably upgrade to AccessXP, I think, if it would make things easier.
Thanks, in anticipation
Martin R Newman
lands(sites), the openers/managers of the sites, and events that are
associated with particular people on particular sites. (A particular
person may be associated with many sites and a particular site may be
associated with many people).
In "plain English" we are recording sending letters to site owners/managers
and their responses and our responses to their actions.
I have four tables:
A Sites table - represents bits of land.
Joined one-to-many to
A Sites_person_Event relationship table - (this table is also linked one
to one with a persons table)
Joined one to many to
An events table which contains dates, event type indicator & records
events associated with the particular person/site combination.
An added complication is that some of the Site_Person relationship records
are marked as "critical" with a boolean variable.
Now the database works just fine - no problem. The sites and people
tables both contain several hundred upto low thousands of records, the
events table rather more.
I am writing an automated query generator which enables users to
interrogate the database using a forms interface, in fact building really
quite complex SQL behind the scenes. Whilst the code for this requires
care to write, again it is generally fine and works.
But I am slightly stuck on trying to generate one particular sort of
query.
What I want is a query that shows me all the sites where ALL the critical
contacts have had ALL of a predefined set of events done to them. (In
plain English an example might be show me all the sites where we have
asked all critical contacts for permission to visit and got a positive
reply.)
Now I can write a mixture of code and queries to do this but what I was
wondering is is it possible to do this kind of thing purely in JET SQL. I
feel with the skillful use of IN clauses, and maybe counting entries
satisfying particular criteria, I ought to be able to, but I cannot quite
see how. Being able to do it entirely in SQL would enable me to fit this
type of query easily within the confines of the current user interface I
have for the user building her own queries, so I'd really prefer to do it
as an SQL query if at all possible.
Any thoughts?
I did try an alternative strategy which I could live with. I did manage
to develop a query which returned me all the sites where ALL the critical
contacts have had one particular event done to them. I then did inner
joins between all the queries (using the site and people primary keys) for
each of the events I was interested in (maybe only 3 or 4 at the most).
Unfortunately, although each constituent query ran in maybe 20 secs the
joined big query gobbled both CPU and memory and crashed access -
presumably something to do with the way Access optimisation works (or not
apparently), though I confess to not really understanding what was
happening.
This is all running on 2Ghz-ish machines with 512MB ish, so hardware isn't
a problem. Running Ac97 (As that is what my client has) but I could
probably upgrade to AccessXP, I think, if it would make things easier.
Thanks, in anticipation
Martin R Newman