Query showing inconsistent results.

R

Richard Horne

Hi guys, bit of a problem with a query. For some reason when I Select an
extra field to be dispalyed by the query, even though the extra field does
not have any parameters which affect the query, the results shown are
different.

I.e:

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time, Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete, *
FROM Phone_Log
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays 15 results which is the correct query. But when I add an extra
field only one result is shown, can someone correct my query...

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time, Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete, Company_Details.[Telephone
Number], *
FROM Company_Details INNER JOIN Phone_Log ON Company_Details.ID =
Phone_Log.Caller
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays only 1 result.
 
A

Allen Browne

Your 2nd query contains 2 tables.
Double-click the line joining the 2 tables in query design view.
Access pops up a dialog giving you 3 options.
Choose:
All records from Phone_Log, and any matches from ...

More info in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html
 
R

Richard Horne

Hi Allen

The extra field from the extra table in the second query - I actually want.
That's my point. For some reason when I insert that extra field into the
query, I don't get a true reflection of the results. I.e. I get one result
instead of the 15 I should be getting.

Allen Browne said:
Your 2nd query contains 2 tables.
Double-click the line joining the 2 tables in query design view.
Access pops up a dialog giving you 3 options.
Choose:
All records from Phone_Log, and any matches from ...

More info in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Richard Horne said:
Hi guys, bit of a problem with a query. For some reason when I Select an
extra field to be dispalyed by the query, even though the extra field does
not have any parameters which affect the query, the results shown are
different.

I.e:

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time,
Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete, *
FROM Phone_Log
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays 15 results which is the correct query. But when I add an extra
field only one result is shown, can someone correct my query...

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time,
Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete,
Company_Details.[Telephone
Number], *
FROM Company_Details INNER JOIN Phone_Log ON Company_Details.ID =
Phone_Log.Caller
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays only 1 result.
Click to expand...
Click to expand...
 
A

Allen Browne

Your query uses an inner join.
You can have the extra table, but you must use an outer join.

Check the article for details.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Richard Horne said:
Hi Allen

The extra field from the extra table in the second query - I actually
want.
That's my point. For some reason when I insert that extra field into the
query, I don't get a true reflection of the results. I.e. I get one result
instead of the 15 I should be getting.

Allen Browne said:
Your 2nd query contains 2 tables.
Double-click the line joining the 2 tables in query design view.
Access pops up a dialog giving you 3 options.
Choose:
All records from Phone_Log, and any matches from ...

More info in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html


Richard Horne said:
Hi guys, bit of a problem with a query. For some reason when I Select
an
extra field to be dispalyed by the query, even though the extra field
does
not have any parameters which affect the query, the results shown are
different.

I.e:

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time,
Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete, *
FROM Phone_Log
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays 15 results which is the correct query. But when I add an extra
field only one result is shown, can someone correct my query...

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time,
Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete,
Company_Details.[Telephone
Number], *
FROM Company_Details INNER JOIN Phone_Log ON Company_Details.ID =
Phone_Log.Caller
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays only 1 result.
Click to expand...
Click to expand...
Click to expand...
 
R

Richard Horne

Allen you superstar!

Thank you ever so much for your help, not only on this thread but all the
other times you've helped me.

Either you're an Access Genius or you have no life! ;) I think it's the
former....
 
W

wanghaijun

Richard Horne said:
Hi guys, bit of a problem with a query. For some reason when I Select an
extra field to be dispalyed by the query, even though the extra field does
not have any parameters which affect the query, the results shown are
different.

I.e:

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time, Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete, *
FROM Phone_Log
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays 15 results which is the correct query. But when I add an extra
field only one result is shown, can someone correct my query...

SELECT Phone_Log.[Tel ID], Phone_Log.Date, Phone_Log.Time, Phone_Log.[Taken
By], Phone_Log.Caller, Phone_Log.[Contact Name], Phone_Log.Regarding,
Phone_Log.For, Phone_Log.[Return Call Number], Phone_Log.Message,
Phone_Log.[Priority Level], Phone_Log.Completed, Phone_Log.Follow_Up,
Phone_Log.Quote, Phone_Log.PurchaseOrder, Phone_Log.Order,
Phone_Log.OutgoingCall, Phone_Log.EntryComplete, Company_Details.[Telephone
Number], *
FROM Company_Details INNER JOIN Phone_Log ON Company_Details.ID =
Phone_Log.Caller
WHERE (((Phone_Log.For)=7) AND ((Phone_Log.Completed)=False));

Displays only 1 result.
Click to expand...
 
Top