Linking multiple tables and Trying to insert records into Detail

[Former Member]

Hello,

I have been struggling with this one for years...

Work Order, Employee Labor, and Materials.  I then create a group headers using the Location field from the Work Order table.  I then create another Group, suppress the group name, and insert several fields from the Work Order table (work order number, wo description, status and completion date into Group 2 section.  Multiple rows of info are displayed.  I then enter some Employee Labor fields from the Labor table into the Details section (employee name, labor hours, pay rate, etc.).  I get several lines of employee labor transaction information grouped below each row of work order info from the Group 2 section.  So far, so good.

Now I attempt to bring a field into the Details section from the 3rd table (Materials).  The moment I introduce the record, the rows of employee labor are duplicated over and over.  In fact, there are multiple duplicate labor transaction rows (over and over).

What am I doing wrong???  I've tried every combination of Linking Order that I can think of...  I need help.

Please reply.

ps.  Let me know if I need to attach a screen shot.

Thank you

Robert

[JWiseman · Accepted solution]

hi Robert,

this is a common issue when you add several details tables to a report. once you add the fields, then the sql generated will include those parent tables and you get a multiplier of records.

my recommendation to you would be to use a subreport for the materials table instead.

go to the Insert menu, choose Subreport and create a new subreport using the Wizard then add your current connection but this time only add the materials table. in the Linking tab, choose your Work order and then add the subreport to the Work order group header. now in the subreport add some fields from your materials table onto the subreport canvas.

what this is essentially doing is a subquery to the database and returning all associated materials records based on a filter for the work order.

the main report should not include any materials records and you can also remove this table from the database expert for the main report.

i hope this helps,

jamie

[Former Member · Accepted solution]

Hi Jamie,

Thank you very much!  I will try it tomorrow and let you know how I make out.

Robert