By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,733 Members | 846 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,733 IT Pros & Developers. It's quick & easy.

Lost record question

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.