Query Questions

  • Thread starter Thread starter Lostguy
  • Start date Start date
L

Lostguy

Hello!

Some Query Basics questions that am getting wrapped around:

a) What is better: Creating a separate query and then basing the
report off that query (report and separate query), or just creating
the query by clicking the 3 dots from the report's record source
property (report with integrated query)? I have many queries that are
only being used by one associated report, so I thought if I did the
recordsource query route, it might make my db smaller or run faster or
something since I can delete all the separate queries?

b)
tblEmployee: EmployeeIDpk, LName
tblStuff: StuffIDpk, StuffDesc
tblEmpStuff: EmployeeIDfk, StuffIDfk

If you want to see Employee wth Stuff, is it better to: Drag the LName
and StuffDesc to the query (from 2 tables), or drag the two fk's from
the tblEmpStuff (same table) to the query, and then display the fk's
as LName and StuffDesc?

c) My report shows those duties that have employees assigned to them,
and not the unassigned duties (those with null for employee info. How
to you set the criteria of "data is OK, and NULL is also OK" using the
Query Builder? Here is the SQL if that helps:

SELECT

tblExtraDuty.ExtraDuty,
tblEmployees.RateorTitle,
tblEmployees.FName,
tblEmployees.MName,
tblEmployees.LName,
tblSections.SectionName,
tblExtraDuty.ExtraDutyIDpk,
tblEmpExtraDuty.ExtraDutyDate,
tblExtraDuty.ExtraDutyIndex,
tblEmployees.EmployeeIDpk


FROM

(tblSections INNER JOIN tblEmployees ON tblSections.SectionIDpk =
tblEmployees.tblSectionsIDfk)

INNER JOIN (tblExtraDuty INNER JOIN tblEmpExtraDuty ON
tblExtraDuty.ExtraDutyIDpk = tblEmpExtraDuty.ExtraDutyIDfk)

ON tblEmployees.EmployeeIDpk = tblEmpExtraDuty.EmployeeIDfk;



??

I appreciate all the help and direction I have received from everyone!

VR/Lost
 
It would be better to post three questions as three separate posts.

A) It makes no difference in terms of perceived performance or database size.
The record source query route actually stores the query in the database.
There is some slight overhead with having a stored query.

My personal choice is to only make separate queries when
== I am going to use it in more than one location
== I need to nest queries
== If the query is too long or complex to store in the record source property
of a form or report
== If the query is too long or complex to store as the row source property of
a combobox or listbox

B) It depends on what you want to see. If you only need to see the LName and
StuffDesc, then drag them to the fields to be displayed.

If you are saying that you have made the fields in tblEmpStuff LOOKUP fields
then I would certainly avoid using them directly in the query. WHY? Because
if you enter criteria against the fields or sort by the fields
tblEmpStuff.EmployeeIDfk or tblEmpStuff.StuffIDfk you will probably get
unexpected results. You might see "Spencer" displayed but the actual stored
value might be 1237. So to find records for "Spencer" you would need to
search for 1237.

C) Using the query builder you need to build a series of queries. The first
query would be to list the employees and all the possible duties.

SELECT tblEmployees.EmployeeIDPk,
tblEmployees.RateorTitle,
tblEmployees.FName,
tblEmployees.MName,
tblEmployees.LName,
tblExtraDuty.ExtraDutyIDPk,
tblExtraDuty.ExtraDutyIndex,
tblExtraDuty.ExtraDuty
FROM (tblEmployees Inner JOIN tblSections
ON tblEmployees.SectionIDFk = TblSection.SectionIDPK), tblExtraDuty

== In the query design view, add the three tables
Join tblSections and tblEmployees, but have NO JOIN to tblExtraDuties and
select the fields.

Save the query.

SELECT Q.*, tblEmpExtraDuty.ExtraDutyDate
FROM TheSavedQueryName as Q LEFT JOIN tblEmpExtraDuty as EED
ON Q.EmployeeidPK = EED.EmployeeIDFk
AND Q.ExtraDutyIDPk = EED.ExtraDutyIDfk

== Create a NEW query
== Add the saved query and tblEmpExtraDuty
== Join the query to the table on EmployeeID and ExtraDutyID fields
== double-click on each join line and in the dialog box for each select the
option (2 or 3) that says ALL from the query and only matching from the
tblEmpExtraDuty table.

This could probably be done in one query but you could get an ambiguous join
error on it.








John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Sir,

Excellent help! And I considered 3 separate posts, but I am always
worried about my "leach factor", so I try to post whenever I have
exhausted Googling, and I minimize the number of posts. <shrug>

I tried c) above, but I must have did something wrong. Here are the
two SQL's:

qryDone1 (8400 records):
SELECT tblEmployees.EmployeeIDpk, tblEmployees.RateorTitle,
tblEmployees.FName, tblEmployees.LName, tblExtraDuty.ExtraDutyIDpk,
tblExtraDuty.ExtraDutyIndex, tblExtraDuty.ExtraDuty
FROM tblExtraDuty, tblSections
INNER JOIN tblEmployees ON
tblSections.SectionIDpk=tblEmployees.tblSectionsIDfk;

qryDone2 (8404 records):
SELECT QueryDone1.*, tblEmpExtraDuty.ExtraDutyDate
FROM QueryDone1
LEFT JOIN tblEmpExtraDuty ON
(QueryDone1.ExtraDutyIDpk=tblEmpExtraDuty.ExtraDutyIDfk) AND
(QueryDone1.EmployeeIDpk=tblEmpExtraDuty.EmployeeIDfk);

I was running out the door when I looked at this, so maybe the real
result is 4 records and I have a join going the wrong way...??

VR/Lost
 
Perhaps I misunderstood your goal. Are you trying to create a list of duties
that an employee has not done? I was trying to give you a list of every
employee and every duty and the day the duty was done or a blank (null) for
the dutydate if the employee had not accomplished said duty.

If so, you need to add a where clause to the second query.

SELECT QueryDone1.*, tblEmpExtraDuty.ExtraDutyDate
FROM QueryDone1
LEFT JOIN tblEmpExtraDuty ON
(QueryDone1.ExtraDutyIDpk=tblEmpExtraDuty.ExtraDutyIDfk) AND
(QueryDone1.EmployeeIDpk=tblEmpExtraDuty.EmployeeIDfk)
WHERE tblEmpExtraDuty.ExtraDutyDate IS NULL

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top