How do i fill a 2 Diminisional Array wit data from MS Acess Table

I have 2xTable (Main and Task).  Main has 1000 records with a FIELD named TaskXAssigned (the X is based on a Task number).  Task has multiple tasks (Fields=ID and Task)

I want to create a DOUBLE FOR loop that will COUNT the number of EACH task assigned (TaskXAssigned).  The OUTSIDE FOR loop counts the number of TASKs in the Task table and the INSIDE FOR loop uses the number of records in the Main table.

I=Dcount(Task table) =25 Tasks...........there are a total of 25 Tasks
Z=Dcount(Maintable)=1000 records.need to find out how many of these have data in TaskXAssigned *where X=1-Dcount)
FOR  vTask=1 TO Dcount (Whatever I equals- # Tasks)
    FOR vRecord=1 TO Dcount (Whatever Z equals - # Reords)

    HELP!!!      'NEED to create a variable for Counter based on Task number (like ctr1 for Task1, ctr2 for Task2, etc)

           vTaskAssigned = "Task" & I & "Assigned"      'This will become Tasl1Assigned, Task2Assigned, etc
           (If IsNull(DLookup(vTaskAssigned, "Main")) Then     'Nothing happens
                  CREATED counter is incremented by 1  
                  STORE variable and counter in ARRAY
    NEXT vRecord
NEXT vTask

What I hope to have at the end is something SHOWING me :
  Task1=Counter1 and # of records with Task1Assigned
  Task2=Counter1 and # of records with Task2Assigned
  Task3=Counter1 and # of records with Task3Assigned  etc
A report will be generated :
Task Number              # of Records
1                                       200
2                                       124
3                                        5

Please help!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
What does the actual data in your table look like?
NorieAnalyst Assistant Commented:
How exactly are the 2 tables structured?
jurictaAuthor Commented:
1.  The Dcount works fine and give accurate data (correct number count for tasks)
2.  Stuffing the counter (equates to Task number) and number count into an array is my issue
3.  Data is "specialized" so I am attaching two pictures with SAMPLE data
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Dale FyeOwner, Dev-Soln LLCCommented:
What you mean is that your data is not normalized, not "specialized".  Personally, I'm against loops, for the most part.

I would create a normalizing query to "normalize" the data in that first image, and then I would create another query based upon the normalizing query to get your counts.  Just my personal opinion, but this would also let you display the results in a continuous form or data sheet rather than creating controls and having to stuff the values from the array into those controls.

Cannot really help much on the syntax of the normalizing query because I'm not certain that first image contains all of the key fields you would need to use.
jurictaAuthor Commented:
Specialized meant that I cannot show the actual data because it is proprietary data.  The first table showed all the current fields.  The company may add fields later.
The relationship between task and main is many-to-many.  That means you need a junction table to store this data.  So rather than having multiple columns for task assigned and dates, you would have one task per row in the junction table.


Once you have properly normalized the data, you won't have any trouble counting it with a simple query rather than nested VBA loops.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jurictaAuthor Commented:
Maybe I am wrong in my definition but I disagree with PatHartman.  I have a One to Many (1 Task to Many "users".  There are NO duplicate Tasks.  Task1 has only one definition, Task2 has only one definition, etc  Task1 may be assigned to Many Users
Dale FyeOwner, Dev-Soln LLCCommented:

Trust me, Pat has it right here.  Especially with respect to your comment "The company may add fields later.".

Whenever you have a table which has repeating columns, with a number or a date, or a month stored in the column header, it is a very good indicator of poor table design.  You have many people, and have many tasks,  to assign tasks to people, you need the Junction table that Pat describes above.  

When people have difficulty grasping this, it is usually because the data currently resides in Excel or some other form of spreadsheet, which does not neatly depict this many to many relationship except with repeating column headers.  Because of some of the functionality of Excel, you can overcome this and perform some of the counting you are discussing using Excel formulas, or by writing VBA code which will loop through rows (people) and columns (tasks).  But if you configure your data correctly in Access, by creating this junction table, it will make counting, and any other form of aggregate (sum, Min, Max) much simpler.
jurictaAuthor Commented:
Pat should be awarded the whole points by virtue of the junction table idea.  Thanks for all of the input from everybody though
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.