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.