Step-wise task management database query (Indexed data pull) (1 Viewer)

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

That just reads like an exam question to me! Meaning that the real question is a question hidden within the question. I think you need to ferret out the "real question" yourself, because I can only make a guess at it. A better approach might be to publish here the layouts of your tables. It's far easier to sort out the structure and also the problems, which I'm guessing your structure has. I did a YouTube video a while back showing some of the different options you can use to display the structures within your database. See Here:--- Show Your Table on a Forum---

Web pages seem to lacking the ability to create subforms so you are asking how to "flatten" the two tables into one row with multiple occurrences? The answer is - there is no built in method since this would violate first normal form. RDBMS are not spreadsheets and so they do not provide "across" functions. The two options that come to mind:
1. A dLookup for every field of every row in table 2 which you are correct would be very inefficient.
2. Write a function that takes the ID of table1 and returns a recordset of matching rows from table2. The function loops through all the returned records and creates a string formatted as you want. This is also not particularly efficient but is far better than the domain function method.

PS - make sure you check your data to ensure that there really are never more than 5 occurrences and that there are unique indexes that prevent the steps from being duplicated for a given table1 ID.

Ah! yes, I think Pat's hit the nail on the head again!

If she has, and you need to "flatten", then you might find my Blog here:- http://www.niftyaccess.com/display-field-data-horizontally/ on it useful.