How to populate an unbound Report from multiple tables?

[Jeroen3131]

I've recently completed my Work Instructions database and now I want to create a Report which shows each Instruction. Below is a simplified version of my database and problem.

I want to populate the Report with data from multiple tables. All the tables are related to eachother. These are my tables:

[U][B]tblSteps[/B][/U]
StepID     Description     Picture
1            AAA           C://xxxx
2            BBB           C://xxxx
3            CCC           C://xxxx
Etc.

[B][U]tblStepParts[/U][/B]
StepPartID   StepID   PartID    Qty
1               3        8       3
2               3        6       3
3               3        3       4
4               3        1       4
5               2        2       1
6               2        3       10
7               1        3       6
8               1        5       4
9               1        2       1
Etc.

[U][B]tblParts[/B][/U]
PartID     Name
1         WASHER
2         RING
3         NUT M8
4         COVER
5         CYLINDER
6         HOSE
7         PIN
8         BOLT M8
Etc.

I have a mainform (mainForm) with a header and a footer. On this mainform I have a Subform (subForm1). This subform contains another subform (subsubForm1). The height of subsubform1 is 3", the height of subForm1 is 6". This ensures that on each Report page there are two subsubForm1's visible.

Subsubform1 contains 4 textboxes to show Part names, 4 textboxes for their quantities, 1 textbox for the Step description and 1 image control to display a picture.

Normally you bound a control to the form and the form to a table. But my problem is that each StepID contains multiple PartID's which are stored in another table.

Can someone point me in the right direction?

[twinnyfo]

jeroen,

First, I need to clarify that you are building a Report and not another Form.

If you are trying to print out the instructions, then I would highly encourage you to create a Report (same principles as a Form, but better able to deal with some of your "subform" issues).

If I were building this report, I would create the query first, such that your results would look like this:

[B][U]StepID[/U]  [U]Description[/U]  [U]Picture[/U]   [U]StepPartID[/U]  [U]StepID[/U]  [U]PartID[/U]  [U]PartName[/U]  [U]Qty[/U][/B]
 1         AAA       C://xxxx      7          1       3    NUT M8     6
 1         AAA       C://xxxx      8          1       5    CYLINDER   4
 1         AAA       C://xxxx      9          1       2    RING       1
 2         BBB       C://xxxx      5          2       2    RING       1
 2         BBB       C://xxxx      6          2       3    NUT M8    10
 3         CCC       C://xxxx      1          3       8    BOLT M8    3
 3         CCC       C://xxxx      2          3       6    HOSE       3
 3         CCC       C://xxxx      3          3       3    NUT M8     4
 3         CCC       C://xxxx      4          3       1    WASHER     4

This can be done by simple joins, which I think you are capable of doing (based on what I've seen so far in your other threads).

This will allow you to build a report very easily, grouping on the various parts of the report (StepID, PartID, etc.).

There are other ways to do this, but can be more involved. If you wanted to use Sub-Reports, you would typically create several queries that addressed the specific information of each Report: The Main Report would only have information such as StepID, Description and Picture. The First Sub-Report would list all the Step Parts, but include the StepID, so you could establish a Master-Child relationship on the Sub-Report. Another Sub-Report would have a query that lists only the Parts and their Quantities (again, with StepID). Then these Sub-Reports would be arranged on the Main Report to your liking.

If the Sub-Reports have the CanGrow Property set to True, you could also set these Sub-Reports to be "invisible" when there are no records by setting the height to 0.

Lots of different options for this one, but what you are asking for is neither strange or difficult. But, when one has not done it before, it can be confusing.

Hope this hepps!

[Jeroen3131]

Thnx! Got it working with the Master-Child relations.