Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Descision based on a query
From: Tara on 20 May 2010 14:02
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.
From: Daryl S on 20 May 2010 14:21
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.
From: Tara on 20 May 2010 14:42
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.
From: Marshall Barton on 20 May 2010 14:45
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]
From: Tara on 20 May 2010 15:36
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]
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Descision based on a query