Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Descision based on a query
From: Daryl S on 20 May 2010 15:46
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.
From: Tara on 20 May 2010 16:07
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.
From: John W. Vinson on 20 May 2010 16:11
On Thu, 20 May 2010 11:42:01 -0700, Tara <Tara(a)discussions.microsoft.com>
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]
From: Tara on 20 May 2010 16:20
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]
> > .
> >
From: Marshall Barton on 20 May 2010 17:38
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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Reminder - Microsoft Responds to the Evolution of Community
Next: Descision based on a query