Converting a query to a WHERE condition

J

John S. Ford, MD

I have a form called frmSearchForm that reads search criteria and then forms
a SQL statement. This statement collects a subset of hospital admissions
using a Make-Table query. Each record in this "temp" table is a unique
hospital admission in tblAdmissions, primary key AdmissionIDNum.

I then joined this table to tblHospitalAdmissions using AdmissionIDNum as
the common field. The resulting query then becomes the underlying
datasource for a form, frmAdmissionEntry. The original SQL statement in
effect "filters" tblAdmissions for this form. I use a temp table because
the query uses aggregate functions and the joined datasource has to be
updateable.

This works well but at some point, this database will be moved to a
multi-user environment. I'm uncomfortable with having a temporary table be
accessed at the same time by different users.

Is there a way of converting my SQL query into a WHERE condition that can be
applied to the frmAdmissionEntry thereby eliminating the temp table and the
join and just use tblAdmissions as the datasource?

I suspect subqueries can be used but I REALLY don't understand them!

Thanks in advance!
John
 
M

Mr. B

John,

I want to be sure what you are talking about when you say "at some point,
this database will be moved to a multi-user environment". Are you talking
about having a split front-end/backend type application?

if s, have you considered having your "temp" table exist in the front-end
file? I realize that in most cases we all talk about having all tables to be
located in the backend file, but in this case if you want one use to have
exclusive use of the "temp" table then you can just have that table in the
front-end file and it will contain info that no other user will have access
to.

Just my thoughts.
 
G

Gina Whipp

John,

I use Temp tables but for mapping, however, I think the principal is the
same. Why not generate the Temp tables to the front end and only that user
will have access to it. You can then set it to delete the table either
On_Exit of the from or the database. (DoCmd.RunSQL "DROP TABLE
YourTempTableNameGoesHere") However, it should be noted that creating
these does cause bloating over time so you should try to use them sparingly.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John S. Ford, MD

I agree that I could use the temp table in the front end but I'm also
thinking that using a WHERE condition would be cleaner and faster than
reading and writing a fresh table each time someone uses the search form.

This may be particularly so when the table gets very large. I was sure
there was a way to do this with subqueries.

John
 
J

John S. Ford, MD

It's a pretty ugly query and I hate to have you people trudge through this
but here goes:

SELECT tblDATA_Admissions.AdmissionIDNum,
IsNothing(tblDATA_Admissions.PatientDischargeDate) AS InpatientTF,
qryFILT_LastTeam.LastTeamIDNum, qryFILT_LastAttending.LastAttendingIDNum,
qryFILT_LastResident.LastResidentIDNum, qryFILT_LastIntern.LastInternIDNum
INTO tblTEMP_AdmissionsPatients FROM (((tblDATA_Admissions LEFT JOIN
qryFILT_LastTeam ON tblDATA_Admissions.AdmissionIDNum =
qryFILT_LastTeam.AdmissionIDNum) LEFT JOIN qryFILT_LastAttending ON
tblDATA_Admissions.AdmissionIDNum = qryFILT_LastAttending.AdmissionIDNum)
LEFT JOIN qryFILT_LastResident ON tblDATA_Admissions.AdmissionIDNum =
qryFILT_LastResident.AdmissionIDNum) LEFT JOIN qryFILT_LastIntern ON
tblDATA_Admissions.AdmissionIDNum = qryFILT_LastIntern.AdmissionIDNum" & "
WHERE " & strFormWHERE & ";"

John
 
J

John S. Ford, MD

lol. Don't spend too much time on it Gina. My main goal in these
newsgroups is to learn general principles...not to have you guys develop my
applications for free!

John
 
H

Hans Up

It's a pretty ugly query and I hate to have you people trudge through this
but here goes:
Click to expand...

SELECT
tDA.AdmissionIDNum,
IsNothing(tDA.PatientDischargeDate) AS InpatientTF,
qLT.LastTeamIDNum,
qLA.LastAttendingIDNum,
qLR.LastResidentIDNum,
qLI.LastInternIDNum
INTO
tblTEMP_AdmissionsPatients
FROM
(((tblDATA_Admissions AS tDA
LEFT JOIN qryFILT_LastTeam AS qLT
ON tDA.AdmissionIDNum = qLT.AdmissionIDNum)
LEFT JOIN qryFILT_LastAttending AS qLA
ON tDA.AdmissionIDNum = qLA.AdmissionIDNum)
LEFT JOIN qryFILT_LastResident AS qLR
ON tDA.AdmissionIDNum = qLR.AdmissionIDNum)
LEFT JOIN qryFILT_LastIntern AS qLI
ON tDA.AdmissionIDNum = qLI.AdmissionIDNum"
& "
WHERE " & strFormWHERE & ";"

What is IsNothing? Access tells me "keyword not found". It looks like
IsNull might work there.

I don't understand what's happening around the WHERE keyword.

Are qryFILT_LastTeam, qryFILT_LastAttending, qryFILT_LastResident, and
qryFILT_LastIntern named queries which use aggregate functions, as you
mentioned earlier? Are they all based on tblDATA_Admissions?

Perhaps you could put all the "last" items into a subquery, then use
EXISTS to filter tblDATA_Admissions.

Allen Browne has a couple pages you will find helpful:

http://allenbrowne.com/subquery-01.html

http://allenbrowne.com/subquery-02.html

Good luck,
Hans
 
J

John S. Ford, MD

Hans, I really appreciate that you're trying to help me but I don't want you
people to waste so much time on this! I'm trying to discover general
principles here, not get you all to do my application for me for free!

IsNothing() is a function I made up that determines whether a field is empty
(0-length string, blank space, NULL, etc.).

Yes, qryFILT_LastTeam, qryFILT_LastAttending, qryFILT_LastResident, and
qryFILT_LastIntern are previously written queries using aggregate functions. Any joins I do with them lead to nonupdateable queries.
Click to expand...

I will check at the references you've cited. I'm sure this can be done with
subqueries. Thanks!

John
 
H

Hans Up

John said:
Hans, I really appreciate that you're trying to help me but I don't want you
people to waste so much time on this! I'm trying to discover general
principles here, not get you all to do my application for me for free!
Click to expand...

No worries, Dr. John! I understood your intention, and I respect it. I
didn't invest much time.

As a general principle, I find it easier to decipher SQL statements with
the type of formatting I showed you. Another helpful feature (for me)
is to use short descriptive aliases for the data sources (tables or
saved queries). Kind of like --- would you find these techniques
helpful, too?

If I've piqued your interest, check out "Instant SQL Formatter":

http://www.dpriver.com/pp/sqlformat.htm
IsNothing() is a function I made up that determines whether a field is empty
(0-length string, blank space, NULL, etc.).

Yes, qryFILT_LastTeam, qryFILT_LastAttending, qryFILT_LastResident, and
qryFILT_LastIntern are previously written queries using aggregate functions.

Any joins I do with them lead to nonupdateable queries.

I will check at the references you've cited. I'm sure this can be done with
subqueries. Thanks!
Click to expand...

I think you're probably right. (I wonder what's going on inside those
"qryFILT*" queries, though.) I'll be interested to hear how this looks
to you after you've had a chance to review Mr. Browne's pages.

Regards,
Hans
 
G

Gina Whipp

John,

I am back now and I see Hans has posted an answer... did it work for you?
(I have not looked at it, just got back from a Client.)

As for the free development... It's just as much 'fun' for us as it for
you. Personally, I learn alot by figuring out why something doesn't work!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John S. Ford, MD

Gina,

I'm still trying to figure out subqueries and how to execute Hans' advice.
Again, thanks for all the help!

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why is this query not updatable? 5
Passing information from one form to another 5
can't execute update query 1
Help on crosstab query 2
Foreign Key Issues 2
SQL query 1
eliminate data subset from query 2
Insert a crosstab to temp table 4

Top