Using similar calculated fields in report
I have a query that includes the following fields:
CompanyAdmin: [users.firstname] & " " & [users.lastname]
ExecContact: [users_1.firstname] & " " & [users_1.lastname]
EmergContact: [users_2.firstname] & " " & [users_2.lastname]
Users: [users_3.firstname] & " " & [users_3.lastname]
The query uses a "Company" table with contact ID fields and three copies of a "Users" table.
I get an error message when trying to create a report based on this query "This key is already associated with an element of this collection". If I eliminate all but one of the calculated fields in the report all is good.
Any suggestions?
CompanyAdmin: [users.firstname] & " " & [users.lastname]
ExecContact: [users_1.firstname] & " " & [users_1.lastname]
EmergContact: [users_2.firstname] & " " & [users_2.lastname]
Users: [users_3.firstname] & " " & [users_3.lastname]
The query uses a "Company" table with contact ID fields and three copies of a "Users" table.
I get an error message when trying to create a report based on this query "This key is already associated with an element of this collection". If I eliminate all but one of the calculated fields in the report all is good.
Any suggestions?
Hamed Nasr
If this , as specified in tags, an access report, then upload a sample database to check.
Please post the sql of the query.
ASKER
I will post both tomorrow. Thanks for the quick response.
"and three copies of a "Users" table"
Looks to me like you need 4 copies (the original + 3 copies) of the Users table.
Looks to me like you need 4 copies (the original + 3 copies) of the Users table.
ASKER
OK fyed, four. Why can't I report from it?
My guess is, that if you look at the joins in the query, you will see that one of the copies of the Users table is joined wrong.
Can you open the query in design view, make the top part where the joins are visible large enough to see all of the foreign keys and the joins to the copies of your Users table, then get a screen shot and post that back here.
The SQL would help too, but I'm guessing it will be obvious if you look at the query design.
Can you open the query in design view, make the top part where the joins are visible large enough to see all of the foreign keys and the joins to the copies of your Users table, then get a screen shot and post that back here.
The SQL would help too, but I'm guessing it will be obvious if you look at the query design.
ASKER
I will check it out and get back to you Monday. Thanks
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Just want to drop the question.