Lost record question

[Corky]

I have a very simple database. It tracks service calls. It has fields
for two technicians; Tech1 and Tech2. It captures their hours against
a labor category (electrical, plumbing, carpentry, etc.) I created a
query to summarize the hours for each techncian by pay period. It
works great. When I merely add a linked database to the query design
screen, without any other changes to the query, I lose several records
(23 of 126, for instance). The second table simply links a 2-letter
code for each labor category to simplify passing the accounting
information. What happened? I cannot sort this apparently simple one
out. Thanks for any help you can offer.

[Peter Russell]

Corky previously wrote:

I have a very simple database. It tracks service calls. It has fields
for two technicians; Tech1 and Tech2. It captures their hours against
a labor category (electrical, plumbing, carpentry, etc.) I created a
query to summarize the hours for each techncian by pay period. It
works great. When I merely add a linked database to the query design
screen, without any other changes to the query, I lose several records
(23 of 126, for instance). The second table simply links a 2-letter
code for each labor category to simplify passing the accounting
information. What happened? I cannot sort this apparently simple one
out. Thanks for any help you can offer.

The conclusion I would draw from this is that you have some records which
have an invalid labor category. These records don't find a match on your
newly added table. By default Access queries require data from both
tables to exist before including the record in the dynaset.
To change this behaviour, right-click the join line in the query grid and
select 'Join Type'.
Change the type so it selects 'All from ' your original table.

If you include fields from your second table in the query output it will
be obvious which categories do not exist in the second table.

Regards

Peter Russell

[Corky]

ru***@127.0.0.1 (Peter Russell) wrote in message news:<me**********************@russellscott.btinte rnet.com>...

Corky previously wrote:

I have a very simple database. It tracks service calls. It has fields
for two technicians; Tech1 and Tech2. It captures their hours against
a labor category (electrical, plumbing, carpentry, etc.) I created a
query to summarize the hours for each techncian by pay period. It
works great. When I merely add a linked database to the query design
screen, without any other changes to the query, I lose several records
(23 of 126, for instance). The second table simply links a 2-letter
code for each labor category to simplify passing the accounting
information. What happened? I cannot sort this apparently simple one
out. Thanks for any help you can offer.

The conclusion I would draw from this is that you have some records which
have an invalid labor category. These records don't find a match on your
newly added table. By default Access queries require data from both
tables to exist before including the record in the dynaset.
To change this behaviour, right-click the join line in the query grid and
select 'Join Type'.
Change the type so it selects 'All from ' your original table.

If you include fields from your second table in the query output it will
be obvious which categories do not exist in the second table.

Regards

Peter Russell

Peter,
Thank you very much. Your advice made the query work. I am stilled
challenged as to why the records were not visible in the first place.
I have gone back and re-entered the data, checked for extra spaces and
so forth; the data looks valid, but the records still do not appear,
even on a simple select query. I had to add a couple extra fields to
the table to work around the query problem.
Thanks again for your prompt and pertinent reply.

Corky Corcoran