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.

EXAMPLE:
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
           ELSE:
                  CREATED counter is incremented by 1  
                  STORE variable and counter in ARRAY
           ENDIF
    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!
jurictaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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, Developing Solutions LLCCommented:
What does the actual data in your table look like?
0
NorieAnalyst Assistant Commented:
How exactly are the 2 tables structured?
0
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
Table-Main.PNG
Table-Task.PNG
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions 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.
0
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.
0
PatHartmanCommented:
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.

tblNodeTasks
Node_ID
Task_ID
AssignedDT
StartedDT
CompletedDT

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

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
0
Dale FyeOwner, Developing Solutions LLCCommented:
Juricta,

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.
0
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
0
PatHartmanCommented:
You're welcome.
0
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
arrays

From novice to tech pro — start learning today.