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(vTaskAssign
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!
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(vTaskAssign
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!
Last Comment
Dale Fye
What does the actual data in your table look like?
How exactly are the 2 tables structured?
ASKER
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
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
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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.
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.
ASKER
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.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
