Query/Juntion table question

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have three tables... a contracts table, an employee table and a junction
table. I then have a query that returns employees based on the contract they
work on. This works for two of the contracts, but for some reason the query
is not returning any employees for the third contract. As far as I can tell
there are no typos and I have not done anything different with this third
contract information, so I can't figure out why the query doesn't return this
particular contract information.
 
Additionally, when the query returns the other two contracts, it returns an
instance of each record multiple times, equal to the number of people on the
contract (ex: if the contract has 3 people, it returns 3 instances of each
employee for a total of 9). Yet, if I filter the results, by last name A->Z
for example, it then returns the proper number of records (one for each
employee). Any idea why this happens?
 
Please post the SQL of your query.

(is there a chance you have not joined the tables in your query design?)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have all my joins in the right place

Being fairly new to this I'm not sure how to evaluate the SQL (and never
thought to do that to begin with).

Here is the SQL...

SELECT tblCTServicesDatabase.FirstName, tblCTServicesDatabase.LastName,
tblCTServicesDatabase.Categories, tblCTServicesDatabase.PhoneOne,
tblCTServicesDatabase.PhoneTwo, tblCTServicesDatabase.EmailAddress,
tblCTServicesDatabase.ContractPO
FROM tblCTServicesDatabase INNER JOIN (tblCTServicesContracts INNER JOIN
tblContractContactJoin ON
tblCTServicesContracts.ContractPO=tblContractContactJoin.ContractPO) ON
(tblCTServicesDatabase.ContractPO=tblCTServicesContracts.ContractPO) AND
(tblCTServicesDatabase.ContractPO=tblContractContactJoin.ContractPO);
 
Bob

I'm not sure why you are getting multiple records.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Something to try

Take 2 minutes and make a query with all fields from all three tables.

Make both Joins inner ("Only show records when the joined fields are equal)

See if it works.
 
Thanks for the info. While giving this a shot I realized 2 things that I am
assuming were the problems. The first one was a dumb blond moment. I forgot
to run an update query that should have been done. Secondly, one of my joins
that should be an inner one-to-many is listed as "indeterminate." Any idea
how I change that to an inner join?
 
Not sure I'm following, but here are a few thoughts and ideas:

Make sure that the linking fields in your two main data tables are set to be
primary keys. BTW, these values also have to be unique in those tables. If
you get an error message when you try to set them to be PK's, you probably
violated that rule, which is what I'm guessing your problem is. This would
force you to correct that by putting new unique values in that field.

Although your structures and joins must be designed accordingly, "one to
many" is really a description of your data (in particular, repetition or
lack therof in the data in the fields used for the join) rather than of the
join. In your case, "many" (repeated values) should exist only on the
junction table side of each join. Setting the field on the other side (as
described above) to be a PK forces the data enterer to follow this rule.
More commonly this is handled automatically by making that an autonumber
field.

Answering your specific question,

When your first "draw the line" to make the link, the default is an inner
join. So, on way, in the query design view is to just delete the join line
and redraw it.

Or, in the query design grid select the line/link, right click on it, and
check off "Only include rows from the joined fields from both tables are
equal"
 
Back
Top