• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 42
  • Last Modified:

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!
  • 4
  • 3
  • 2
  • +1
1 Solution
Dale FyeCommented:
What does the actual data in your table look like?
NorieVBA ExpertCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Dale FyeCommented:
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.
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 FyeCommented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now