query not showing everything I think it should

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several tables that store data captured on a single form (and
subforms). Basically, it is a form that I use to record activities. The
form has a subform that captures the names of the people involved.

I am trying to create a query that will identify all of the activities for
the month and who was involved. For some reason I only get three activities
in the query. Ultimately I want to run a report that groups the query by
individual.

Here is the sql that I currently have. As I said I am only getting 3
returns and I know there are at least 10 that should be returned.

TIA
 
SELECT qryChapsandcalls.ChaplainFirst, qryChapsandcalls.ChaplainLast,
sorry - here it is:

tblActivities.DateBegin, [zlookup CallNature].Activity,
tblActivities.CallSummary, tblActivities.Feedback, [zlookup Shift].txtDept,
[zlookup Shift].txtShift, [zlookup Station].Station
FROM (((tblActivities LEFT JOIN [zlookup CallNature] ON
tblActivities.CallMethod = [zlookup CallNature].ActivityID) LEFT JOIN
[zlookup Shift] ON tblActivities.Shift = [zlookup Shift].numShiftID) LEFT
JOIN [zlookup Station] ON tblActivities.numstationvisits = [zlookup
Station].StationID) LEFT JOIN qryChapsandcalls ON tblActivities.CallID =
qryChapsandcalls.CallID
WHERE (((tblActivities.DateBegin)>#4/30/2007#) AND (([zlookup
CallNature].Activity)<>"meeting"))
ORDER BY tblActivities.DateBegin;
 
I don't see anything wrong with the logic of your SQL, although it seems odd
that you would join the tblActivites.numStationVisits (which I would think is
a reference to the number of visits with the stations lookup. This should
not effect the number of records in your result set though because of the
Left JOIN.

First thing I would try is to remove all of the lookup links from the query
except the join to [zlookup_CallNature]. That way, you can actually see what
the "Activity" description is; but I would take it out of the where clause.
Then you can run the query and see what it returns with just the date part of
the query criteria, and can count the number of activities that are not
"meeting".

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Papa Jonah said:
sorry - here it is:
Click to expand...
SELECT qryChapsandcalls.ChaplainFirst, qryChapsandcalls.ChaplainLast,
tblActivities.DateBegin, [zlookup CallNature].Activity,
tblActivities.CallSummary, tblActivities.Feedback, [zlookup Shift].txtDept,
[zlookup Shift].txtShift, [zlookup Station].Station
FROM (((tblActivities LEFT JOIN [zlookup CallNature] ON
tblActivities.CallMethod = [zlookup CallNature].ActivityID) LEFT JOIN
[zlookup Shift] ON tblActivities.Shift = [zlookup Shift].numShiftID) LEFT
JOIN [zlookup Station] ON tblActivities.numstationvisits = [zlookup
Station].StationID) LEFT JOIN qryChapsandcalls ON tblActivities.CallID =
qryChapsandcalls.CallID
WHERE (((tblActivities.DateBegin)>#4/30/2007#) AND (([zlookup
CallNature].Activity)<>"meeting"))
ORDER BY tblActivities.DateBegin;


Dale Fye said:
Papa,

You forgot to include the SQL

Dale
Click to expand...
Click to expand...
 
Dale,
I'm not completely sure I understand your instructions. What I did was I
deleted the "WHERE" clause from the SQL. I got a longer return. What I
discovered was that many of the records were blank in the activity field.
Based on this discovery, I understand why I am getting such a poor return.
HOWEVER - when I look at the table (or the input form), none of the records
are blank. So I don't understand where the info is going in translation.
Any suggestions?

TIA

Dale Fye said:
I don't see anything wrong with the logic of your SQL, although it seems odd
that you would join the tblActivites.numStationVisits (which I would think is
a reference to the number of visits with the stations lookup. This should
not effect the number of records in your result set though because of the
Left JOIN.

First thing I would try is to remove all of the lookup links from the query
except the join to [zlookup_CallNature]. That way, you can actually see what
the "Activity" description is; but I would take it out of the where clause.
Then you can run the query and see what it returns with just the date part of
the query criteria, and can count the number of activities that are not
"meeting".

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Papa Jonah said:
sorry - here it is:
Click to expand...
SELECT qryChapsandcalls.ChaplainFirst, qryChapsandcalls.ChaplainLast,
tblActivities.DateBegin, [zlookup CallNature].Activity,
tblActivities.CallSummary, tblActivities.Feedback, [zlookup Shift].txtDept,
[zlookup Shift].txtShift, [zlookup Station].Station
FROM (((tblActivities LEFT JOIN [zlookup CallNature] ON
tblActivities.CallMethod = [zlookup CallNature].ActivityID) LEFT JOIN
[zlookup Shift] ON tblActivities.Shift = [zlookup Shift].numShiftID) LEFT
JOIN [zlookup Station] ON tblActivities.numstationvisits = [zlookup
Station].StationID) LEFT JOIN qryChapsandcalls ON tblActivities.CallID =
qryChapsandcalls.CallID
WHERE (((tblActivities.DateBegin)>#4/30/2007#) AND (([zlookup
CallNature].Activity)<>"meeting"))
ORDER BY tblActivities.DateBegin;


Dale Fye said:
Papa,

You forgot to include the SQL

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have several tables that store data captured on a single form (and
subforms). Basically, it is a form that I use to record activities. The
form has a subform that captures the names of the people involved.

I am trying to create a query that will identify all of the activities for
the month and who was involved. For some reason I only get three activities
in the query. Ultimately I want to run a report that groups the query by
individual.

Here is the sql that I currently have. As I said I am only getting 3
returns and I know there are at least 10 that should be returned.

TIA
Click to expand...
Click to expand...
Click to expand...
 
Papa,

Why do you have the numStationVisits field in the tblActivities joined to
[zlookup Station].StationID? It would seem to me that the numStationVisits
column represents the number of station visits, not which station, which is
what StationID represents (or should represent).

Likewise, why do you have tblActivities.CallMethod joined to [zlookup
CallNature].ActivityID? I would think you would have a field in
tblActivities called ActivityID which you would join to the ActivityID in
[zlookup CallNature], which would give you the ability to look at the
Activity field in the lookup table. Can you provide me with a list of the
fields in tblActivity? This might make it easier for me to help you.

Dale

Papa Jonah said:
Dale,
I'm not completely sure I understand your instructions. What I did was I
deleted the "WHERE" clause from the SQL. I got a longer return. What I
discovered was that many of the records were blank in the activity field.
Based on this discovery, I understand why I am getting such a poor return.
HOWEVER - when I look at the table (or the input form), none of the
records
are blank. So I don't understand where the info is going in translation.
Any suggestions?

TIA

Dale Fye said:
I don't see anything wrong with the logic of your SQL, although it seems
odd
that you would join the tblActivites.numStationVisits (which I would
think is
a reference to the number of visits with the stations lookup. This
should
not effect the number of records in your result set though because of the
Left JOIN.

First thing I would try is to remove all of the lookup links from the
query
except the join to [zlookup_CallNature]. That way, you can actually see
what
the "Activity" description is; but I would take it out of the where
clause.
Then you can run the query and see what it returns with just the date
part of
the query criteria, and can count the number of activities that are not
"meeting".

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Papa Jonah said:
sorry - here it is:
Click to expand...
SELECT qryChapsandcalls.ChaplainFirst, qryChapsandcalls.ChaplainLast,
tblActivities.DateBegin, [zlookup CallNature].Activity,
tblActivities.CallSummary, tblActivities.Feedback, [zlookup
Shift].txtDept,
[zlookup Shift].txtShift, [zlookup Station].Station
FROM (((tblActivities LEFT JOIN [zlookup CallNature] ON
tblActivities.CallMethod = [zlookup CallNature].ActivityID) LEFT JOIN
[zlookup Shift] ON tblActivities.Shift = [zlookup Shift].numShiftID)
LEFT
JOIN [zlookup Station] ON tblActivities.numstationvisits = [zlookup
Station].StationID) LEFT JOIN qryChapsandcalls ON tblActivities.CallID
=
qryChapsandcalls.CallID
WHERE (((tblActivities.DateBegin)>#4/30/2007#) AND (([zlookup
CallNature].Activity)<>"meeting"))
ORDER BY tblActivities.DateBegin;


:

Papa,

You forgot to include the SQL

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have several tables that store data captured on a single form
(and
subforms). Basically, it is a form that I use to record
activities. The
form has a subform that captures the names of the people involved.

I am trying to create a query that will identify all of the
activities for
the month and who was involved. For some reason I only get three
activities
in the query. Ultimately I want to run a report that groups the
query by
individual.

Here is the sql that I currently have. As I said I am only getting
3
returns and I know there are at least 10 that should be returned.

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top