If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Questions
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 |
#2
|
|||
|
|||
Query Questions
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 Lostguy wrote: 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 |
#3
|
|||
|
|||
Query Questions
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.tblSectionsID fk; qryDone2 (8404 records): SELECT QueryDone1.*, tblEmpExtraDuty.ExtraDutyDate FROM QueryDone1 LEFT JOIN tblEmpExtraDuty ON (QueryDone1.ExtraDutyIDpk=tblEmpExtraDuty.ExtraDut yIDfk) AND (QueryDone1.EmployeeIDpk=tblEmpExtraDuty.EmployeeI Dfk); 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 |
#4
|
|||
|
|||
Query Questions
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.ExtraDut yIDfk) AND (QueryDone1.EmployeeIDpk=tblEmpExtraDuty.EmployeeI Dfk) WHERE tblEmpExtraDuty.ExtraDutyDate IS NULL John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Lostguy wrote: 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.tblSectionsID fk; qryDone2 (8404 records): SELECT QueryDone1.*, tblEmpExtraDuty.ExtraDutyDate FROM QueryDone1 LEFT JOIN tblEmpExtraDuty ON (QueryDone1.ExtraDutyIDpk=tblEmpExtraDuty.ExtraDut yIDfk) AND (QueryDone1.EmployeeIDpk=tblEmpExtraDuty.EmployeeI Dfk); 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 |
Thread Tools | |
Display Modes | |
|
|