Hello on the forum.
First of all, I hope I am in the right subforum - this question may touch database design as well.
I am working in a company where we apply a database to store data used to manage tasks that are supposed to proceeded through steps (1, then 2, then 3, etc.).
In our current setup, we have a large database with a task per row, with columns showing core info on the task as well as the following columns per step (5 steps total):
- User
- Deadline
- Completed status
- Comments
The above setup is based on a local Access Database with a front-end program. In the process of making the database online with a website front-end, we have split the data into the following:
Table 1 = core info
Table 2 = steps (with index 1-5 and foreign key related to Table 1)
In querying data, I would like to make a query that does the following:
- Select * from Table1 where Table1.Name = "A"
- Select Deadline, Completed status from Table2 Where Table2.Step = 1 And Table2.Fkey = ID-of-Table1; this data should then be displayed as new columns in the query, next to core info data
- Select Deadline, Completed status from Table2 Where Table2.Step = 2 And Table2.Fkey = ID-of-Table 1; this data should then be displayed as new columns in the query, next to step 1 data
...repeat for 5 steps total
See the attached .pdf for an example using only 2 steps.
The amount of steps depend on the ID of Table1 and thus varies. They are created using strict forms, so each ID in Table1 will only have one Step1 in Table2.
I know the above can be done through columns in Table1 using the Dlookup function, which essentially are queries themselves. However that would mean >20 Dlookups in a table with thousands of entries. I was wondering if it can be optimized through a query?
Regards,
new user
First of all, I hope I am in the right subforum - this question may touch database design as well.
I am working in a company where we apply a database to store data used to manage tasks that are supposed to proceeded through steps (1, then 2, then 3, etc.).
In our current setup, we have a large database with a task per row, with columns showing core info on the task as well as the following columns per step (5 steps total):
- User
- Deadline
- Completed status
- Comments
The above setup is based on a local Access Database with a front-end program. In the process of making the database online with a website front-end, we have split the data into the following:
Table 1 = core info
Table 2 = steps (with index 1-5 and foreign key related to Table 1)
In querying data, I would like to make a query that does the following:
- Select * from Table1 where Table1.Name = "A"
- Select Deadline, Completed status from Table2 Where Table2.Step = 1 And Table2.Fkey = ID-of-Table1; this data should then be displayed as new columns in the query, next to core info data
- Select Deadline, Completed status from Table2 Where Table2.Step = 2 And Table2.Fkey = ID-of-Table 1; this data should then be displayed as new columns in the query, next to step 1 data
...repeat for 5 steps total
See the attached .pdf for an example using only 2 steps.
The amount of steps depend on the ID of Table1 and thus varies. They are created using strict forms, so each ID in Table1 will only have one Step1 in Table2.
I know the above can be done through columns in Table1 using the Dlookup function, which essentially are queries themselves. However that would mean >20 Dlookups in a table with thousands of entries. I was wondering if it can be optimized through a query?
Regards,
new user
