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
|
|||
|
|||
Left join in query
I have a query set up with a one-to-many left join on two tables in order to
pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. |
#2
|
|||
|
|||
Left join in query
Tara -
I suspect you may have some criteria in the second query that doesn't take into account the null values for the cases where there is data for the 'one' table and not for the 'many'. We can only help if you post the SQL for both queries... -- Daryl S "Tara" wrote: I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. |
#3
|
|||
|
|||
Left join in query
Thanks Daryl...
Here is the SQL for the query that works: SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date, IIf(tblAmendedDates.F2F Is Null,tblContact.F2F,tblAmendedDates.F2F) AS F2FAmended, IIf(tblAmendedDates.TeamMtg Is Null,tblContact.[TeamMtg],tblAmendedDates.TeamMtg) AS TeamMtgAmended, IIf(tblAmendedDates.CPC Is Null,tblContact.CPC,tblAmendedDates.CPC) AS CPCAmended, IIf(tblAmendedDates.CC Is Null,tblContact.CC,tblAmendedDates.CC) AS CCAmended, IIf(tblAmendedDates.OC Is Null,tblContact.OC,tblAmendedDates.OC) AS OCAmended, IIf(tblAmendedDates.Travel Is Null,tblContact.Travel,tblAmendedDates.Travel) AS TravelAmended, IIf(tblAmendedDates.NS Is Null,tblContact.NS,tblAmendedDates.NS) AS NSAmended, IIf(tblAmendedDates.Court Is Null,tblContact.Court,tblAmendedDates.Court) AS CourtAmended, IIf(tblAmendedDates.Training Is Null,tblContact.Training,tblAmendedDates.Training) AS TrainingAmended, IIf(tblAmendedDates.StaffMtg Is Null,tblContact.StaffMtg,tblAmendedDates.StaffMtg) AS StaffMtgAmended, IIf(tblAmendedDates.Admin Is Null,tblContact.Admin,tblAmendedDates.Admin) AS AdminAmended, IIf(tblAmendedDates.DSOther Is Null,tblContact.DSOther,tblAmendedDates.DSOther) AS DSAmended, tblContact.PostedDate, tblContact.DSTypeID, tblContact.OCTypeID, tblContact.numID, IIf(tblAmendedDates.Notes Is Null,tblContact.Notes,tblAmendedDates.Notes) AS NotesAmended FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID = tblAmendedDates.ContactID; Here is the query (that doesn't work) that I'm trying to use the above query in: SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID, QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended, QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended, QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended, QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended, QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended, QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended, QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID, QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended, QryAmendedContacts.numID FROM QryAmendedContacts, tblDates WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And [tblDates]![EndDate])); "Daryl S" wrote: Tara - I suspect you may have some criteria in the second query that doesn't take into account the null values for the cases where there is data for the 'one' table and not for the 'many'. We can only help if you post the SQL for both queries... -- Daryl S "Tara" wrote: I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. |
#4
|
|||
|
|||
Left join in query
Tara wrote:
I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. Your second query probably has a criteria that weeds out the records from the many side table that were not there to begin with. Any criteria that compares a field in the many table to anything will do that. -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
Left join in query
That's essentially what Daryl said to look for too, but I can't find it. The
only criteria is on PostedDate which originally comes from tblContacts, the "one" side. "Marshall Barton" wrote: Tara wrote: I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. Your second query probably has a criteria that weeds out the records from the many side table that were not there to begin with. Any criteria that compares a field in the many table to anything will do that. -- Marsh MVP [MS Access] . |
#6
|
|||
|
|||
Left join in query
Tara -
Do you have an example of a record from the first query plus a record from tblDate that you think should be in the results of the second query, but isn't there? -- Daryl S "Tara" wrote: Thanks Daryl... Here is the SQL for the query that works: SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date, IIf(tblAmendedDates.F2F Is Null,tblContact.F2F,tblAmendedDates.F2F) AS F2FAmended, IIf(tblAmendedDates.TeamMtg Is Null,tblContact.[TeamMtg],tblAmendedDates.TeamMtg) AS TeamMtgAmended, IIf(tblAmendedDates.CPC Is Null,tblContact.CPC,tblAmendedDates.CPC) AS CPCAmended, IIf(tblAmendedDates.CC Is Null,tblContact.CC,tblAmendedDates.CC) AS CCAmended, IIf(tblAmendedDates.OC Is Null,tblContact.OC,tblAmendedDates.OC) AS OCAmended, IIf(tblAmendedDates.Travel Is Null,tblContact.Travel,tblAmendedDates.Travel) AS TravelAmended, IIf(tblAmendedDates.NS Is Null,tblContact.NS,tblAmendedDates.NS) AS NSAmended, IIf(tblAmendedDates.Court Is Null,tblContact.Court,tblAmendedDates.Court) AS CourtAmended, IIf(tblAmendedDates.Training Is Null,tblContact.Training,tblAmendedDates.Training) AS TrainingAmended, IIf(tblAmendedDates.StaffMtg Is Null,tblContact.StaffMtg,tblAmendedDates.StaffMtg) AS StaffMtgAmended, IIf(tblAmendedDates.Admin Is Null,tblContact.Admin,tblAmendedDates.Admin) AS AdminAmended, IIf(tblAmendedDates.DSOther Is Null,tblContact.DSOther,tblAmendedDates.DSOther) AS DSAmended, tblContact.PostedDate, tblContact.DSTypeID, tblContact.OCTypeID, tblContact.numID, IIf(tblAmendedDates.Notes Is Null,tblContact.Notes,tblAmendedDates.Notes) AS NotesAmended FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID = tblAmendedDates.ContactID; Here is the query (that doesn't work) that I'm trying to use the above query in: SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID, QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended, QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended, QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended, QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended, QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended, QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended, QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID, QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended, QryAmendedContacts.numID FROM QryAmendedContacts, tblDates WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And [tblDates]![EndDate])); "Daryl S" wrote: Tara - I suspect you may have some criteria in the second query that doesn't take into account the null values for the cases where there is data for the 'one' table and not for the 'many'. We can only help if you post the SQL for both queries... -- Daryl S "Tara" wrote: I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. |
#7
|
|||
|
|||
Left join in query
tblDates is just a one-record table that is used to set date parameters for
various queries in the database. Do you just mean a record from the first query that should show up in the second as well, based on that date parameter? If so, then yes - but I'm afraid I'm not sure how you want me to represent it here... Are you just asking for some general information about the record? If so, then for example - I have a record from the "one" table (tblContact) with a ContactID of 18792 that falls within the required date parameters that are set in the second query. It does not have a related record in tblAmendedDates (the "many" table) but it does show up in my initial query thanks to the left join. "Daryl S" wrote: Tara - Do you have an example of a record from the first query plus a record from tblDate that you think should be in the results of the second query, but isn't there? -- Daryl S "Tara" wrote: Thanks Daryl... Here is the SQL for the query that works: SELECT tblContact.ContactID, tblContact.CaseID, tblContact.Date, IIf(tblAmendedDates.F2F Is Null,tblContact.F2F,tblAmendedDates.F2F) AS F2FAmended, IIf(tblAmendedDates.TeamMtg Is Null,tblContact.[TeamMtg],tblAmendedDates.TeamMtg) AS TeamMtgAmended, IIf(tblAmendedDates.CPC Is Null,tblContact.CPC,tblAmendedDates.CPC) AS CPCAmended, IIf(tblAmendedDates.CC Is Null,tblContact.CC,tblAmendedDates.CC) AS CCAmended, IIf(tblAmendedDates.OC Is Null,tblContact.OC,tblAmendedDates.OC) AS OCAmended, IIf(tblAmendedDates.Travel Is Null,tblContact.Travel,tblAmendedDates.Travel) AS TravelAmended, IIf(tblAmendedDates.NS Is Null,tblContact.NS,tblAmendedDates.NS) AS NSAmended, IIf(tblAmendedDates.Court Is Null,tblContact.Court,tblAmendedDates.Court) AS CourtAmended, IIf(tblAmendedDates.Training Is Null,tblContact.Training,tblAmendedDates.Training) AS TrainingAmended, IIf(tblAmendedDates.StaffMtg Is Null,tblContact.StaffMtg,tblAmendedDates.StaffMtg) AS StaffMtgAmended, IIf(tblAmendedDates.Admin Is Null,tblContact.Admin,tblAmendedDates.Admin) AS AdminAmended, IIf(tblAmendedDates.DSOther Is Null,tblContact.DSOther,tblAmendedDates.DSOther) AS DSAmended, tblContact.PostedDate, tblContact.DSTypeID, tblContact.OCTypeID, tblContact.numID, IIf(tblAmendedDates.Notes Is Null,tblContact.Notes,tblAmendedDates.Notes) AS NotesAmended FROM tblContact LEFT JOIN tblAmendedDates ON tblContact.ContactID = tblAmendedDates.ContactID; Here is the query (that doesn't work) that I'm trying to use the above query in: SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID, QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended, QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended, QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended, QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended, QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended, QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended, QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID, QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended, QryAmendedContacts.numID FROM QryAmendedContacts, tblDates WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And [tblDates]![EndDate])); "Daryl S" wrote: Tara - I suspect you may have some criteria in the second query that doesn't take into account the null values for the cases where there is data for the 'one' table and not for the 'many'. We can only help if you post the SQL for both queries... -- Daryl S "Tara" wrote: I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. |
#8
|
|||
|
|||
Left join in query
On Thu, 20 May 2010 11:42:01 -0700, Tara
wrote: Here is the query (that doesn't work) that I'm trying to use the above query in: SELECT QryAmendedContacts.ContactID, QryAmendedContacts.CaseID, QryAmendedContacts.PostedDate AS [Date], QryAmendedContacts.F2FAmended, QryAmendedContacts.TeamMtgAmended, QryAmendedContacts.CPCAmended, QryAmendedContacts.CCAmended, QryAmendedContacts.OCAmended, QryAmendedContacts.TravelAmended, QryAmendedContacts.NSAmended, QryAmendedContacts.CourtAmended, QryAmendedContacts.TrainingAmended, QryAmendedContacts.StaffMtgAmended, QryAmendedContacts.AdminAmended, QryAmendedContacts.DSAmended, QryAmendedContacts.DSTypeID, QryAmendedContacts.OCTypeID, QryAmendedContacts.NotesAmended, QryAmendedContacts.numID FROM QryAmendedContacts, tblDates WHERE (((QryAmendedContacts.PostedDate) Between [tblDates]![StartDate] And [tblDates]![EndDate])); As Daryl and Marshall said, you're using a criterion on the query field PostedDate. For those records where no query record exists, that field will be NULL - and NULL is in fact *not* Between those two dates. Try changing the WHERE clause to WHERE QryAmendedContacts.PostedDate Between [tblDates]![StartDate] And [tblDates]![EndDate] OR QryAmendedContacts.PosteDate IS NULL -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Left join in query
I have to leave for the day soon and I won't be able to get back to this
until Monday morning. Thanks so much for all of your help and I hope I can touch base with both of you again on Monday to pick your brains some more about this issue. "Tara" wrote: That's essentially what Daryl said to look for too, but I can't find it. The only criteria is on PostedDate which originally comes from tblContacts, the "one" side. "Marshall Barton" wrote: Tara wrote: I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. Your second query probably has a criteria that weeds out the records from the many side table that were not there to begin with. Any criteria that compares a field in the many table to anything will do that. -- Marsh MVP [MS Access] . |
#10
|
|||
|
|||
Left join in query
John Vinson also said the same thing so be sure to read his
reply. Maybe he explained it in a way that is more understandable. -- Marsh MVP [MS Access] Tara wrote: I have to leave for the day soon and I won't be able to get back to this until Monday morning. Thanks so much for all of your help and I hope I can touch base with both of you again on Monday to pick your brains some more about this issue. "Tara" wrote: That's essentially what Daryl said to look for too, but I can't find it. The only criteria is on PostedDate which originally comes from tblContacts, the "one" side. "Marshall Barton" wrote: Tara wrote: I have a query set up with a one-to-many left join on two tables in order to pull all records from one table (the "one" side) and the corresponding records from another table (the "many" side), if they exist. It works well, but only in THAT query. In other words, if I run that query, the records show up as I want them to. But, if I then use that query in yet another query to further manipulate the data, the only records that show up are the records in the many table. Why and how can I fix it? I can post the query if needed. Your second query probably has a criteria that weeds out the records from the many side table that were not there to begin with. Any criteria that compares a field in the many table to anything will do that. |
|
Thread Tools | |
Display Modes | |
|
|