jdelano, I wish you wouldn't back out. I would certainly be interested to see how you would create a report similar to the report arnegp did in the file he attached earlier. There are only three projects but his query #4 returns five records because both project 1 and project 3 each have two issues.
anelgp, I'm confused. My undertanding is to create relationships you have a Primary Key in the main table e.g., e.g,. pkProjID and then the associated tables have a primary key and a foreign key linking it to the main (or other tables). e.g., pkExtensions and fkProjects/pkIssues and fkProjects ect.
I don't understand what you mean by: "you have created index on NumDays field (both on tblExtensions and tblIssues)" I just don't see that.
One other issue. While I was tinkering with the attached file (trying to consolidate define my method, yours, and jdelano's), I suddenly started getting an error when I attempted to run your 2_qryProject issues. The error was occurring in the ConcatRelated function on this line:
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
I fixed that by changing that line to:
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Any idea why that error occurred? Later after rebooting the PC, I change it back to your line and it is working again. Is there a reason for DBEngine(0)(0) vice CurrentDb?
Thank you both. I have a long, long way to go and a lot to learn. Unfortunately, unlike with Word, I rarely delve into Access and quickly forget what little I learn
Reply With Quote