Nope, you have to built an index that forbid duplicated pairs.
To build an index on more than a single field, open the table in design
view, open the index window,
- give a name for the index, in the first column,
- type one of the field making the index in the second column
- specify an ordering, if wanted
- in the bottom left, change the Unique from No to Yes
- get to the next line in the grid, but type nothing in the first
column,
- specify another field in the second column
That will create an index forbidding duplicated pairs (of field). Sure,
if
your table already have duplicated pair, Access won't let you save the
design. So, be sure you don't have duplicate pairs, first.
The two junctions tables, those I named Requirements and
EmployeeTrainings, MUST NOT have duplicated pairs: after all, a
certificate
does not ask you take the a given training twice; neither is it expected
to
have an employee completing with success the same training twice
Hoping it may help,
Vanderghast, Access MVP
Thanks Gary,
It worked. One problem I found is that if there is a duplicate value
(which
happens) in the data entry for an associate, then they do not show up
in
the
query results.
Would I have to change the count to maybe Dcount?
greg
:
Open [tbl data entry] in design mode
and see how spelled "training code"
Open [tbl Labs] in design mode
and see how spelled "training code"
adjust to correct spelling in
[tbl data entry].[training code] = a.[training code]
"GMac"wrote:
Ok.......I got the query to work, but there is still a problem. When
I
run
the query it is asking for a training code. Why?
And the query results are not right. It is listing all certificates
for
each
associate instead of looking at the training codes and seeing if
each
associate has met the criteria for each certificate. example
certificate A
needs training codes A,B,C,D to be complete in order for certificate
A
to
be
earned. The data entry table lists all training codes completed by
each
associate. I think I'm getting closer, but still need help!
Thanks
:
there should be no space on either side of
decimal point...maybe get you closer...
SELECT
[tbl Associate].[Associate #],
[First Name],
[Last Name],
[tbl Certificates].[Certificate],
a.[Certificate code]
FROM
(
(
[tbl data entry]
INNER JOIN
[tbl Labs] AS a
ON
[tbl data entry].[training code] = a.[training code]
)
INNER JOIN
[tbl Associate]
ON
[tbl data entry].[associate number] = [tbl associate].[associate #]
)
INNER JOIN
[tbl certificates]
ON
a.[certificate code] = [tbl certificates].[certificate code]
GROUP BY
[tbl Associate].[associate #],
[ First Name],
[Last Name],
[tbl certificates].certificate,
a.[certificate code]
HAVING
((count(*)) =
(SELECT
COUNT(*)
FROM [tbl labs]
WHERE
[certificate code] = a.[certificate code])));
"GMac" > Michel,
Upon correcting the errors you have listed, I get this:
Invalid use of '.','!', or '( )' in Query Expression [tbl data
entry] .
[training code] = a.[training code] I occured when I entered the
missing )
you had listed. My code looks like this now:
SELECT [tbl Associate]. [Associate #], [First Name], [Last Name],
[tbl
Certificates]. [Certificate], a.[Certificate code]
FROM (([tbl data entry] INNER JOIN [tbl Labs] AS a ON [tbl data
entry].
[training code] = a.[training code])
INNER JOIN [tbl Associate] ON [tbl data entry]. [associate
number] =
[tbl
associate]. [associate #])
INNER JOIN [tbl certificates] ON a.[certificate code] = [tbl
certificates].[certificate code]
GROUP BY tbl Associate.[associate #],[ First Name], [Last Name],
tbl
certificates. certificate, a.[certificate code]
HAVING ((count(*)) = (SELECT COUNT(*) FROM tbl labs WHERE
certificate
code
= a.[certificate code])));
Thanks again for all your help, this thing is driving me crazy!
greg
:
A ) is missing before the last INNER JOIN.
The GROUP BY clause need [ ] around the illegal fields name:
GROUP BY tbl Associate.[associate #], [First Name], [Last Name],
tblcertificates.certificate, a.[certificate code]
and also in the WHERE clause of the sub-query in the main HAVING
clause:
a.[certificate code]
.... a good reason to NOT use illegal names for fields (use the
caption
property, if you want names easy to read, automatically
displayed
on
user
interface).
Hoping it may help,
Vanderghast, Access MVP
Michel,
OK, I was right on what I did. I entered thr code in SQL view
with
the
correct names of my tables. I am getting a syntax error in the
JOIN
operation
that I can't figure out. Here is the code: Any ideas?
SELECT [tbl Associate]. [Associate #], [First Name], [Last
Name],
[tbl
Certificates]. [Certificate], a.[Certificate code]
FROM (([tbl data entry] INNER JOIN [tbl Labs] AS a ON [tbl
data
entry].
[training code] = a.[training code])
INNER JOIN [tbl Associate] ON [tbl data entry]. [associate
number] =
[tbl
associate]. [associate #]
INNER JOIN [tbl certificates] ON a.[certificate code] = [tbl
certificates].[certificate code]
GROUP BY tbl Associate. associate #, First Name, Last Name,
tbl
certificates. certificate, a.certificate code
HAVING ((count(*)) = (SELECT COUNT(*) FROM tbl labs WHERE
certificate
code
=
a.certificate code)));
Thanks,
greg
:
The code is just one query, enter the code in SQL view.
The tables that are not obvious are EmployeeTrainings and
Requirements.
EmployeeTrainings list each training that each employee got,
on
pair
by
record, no duplicated pairs.
Requirements is a table that list all the trainings needed to
get
one
certificate, but, again only one (training-certificate pair)
per
record,
no
duplicated pairs.
Other tables are just there to convert numbers_id into
meaningful
text
description.
Hoping it may help,
Vanderghast, Access MVP
news:
[email protected]...
