Report and sub report

[dixxy]

I have a report that returns materials used for a job, inside that report is a subreport which returns offcuts for those materials.

The problem i am having is, if there is no material, but there is an offcut, then the offcut does not show up, so in other words if there is no parent i get no child.

These 2 reports are connected by a MATID feild.

is there a way to fix this so that i can get offcuts without materials.

Both reports have a different table as data source, but are connected in the reports throught MATID.

Thanks,

Sylvain

 

[Remou]

Can you connect by a JobID rather thant a MatID? It is difficult to tell how to include orphan offcuts on the information you have given. Some notes on the fields of each table would be useful.

 

[dixxy]

Ok,

First of all these tables are from an open source(i think that is the term for this)program that we have, that is 90% Ms Access databases, so the choice of feilds is pretty much what i have to work with.

tblOPTOffcuts has thses feilds: OffcutNo[number], MatID [number], Lenght [number], width [number], qty [number], and offcutID [autonumber], i managed to add the offcut ID feild to the table and change the databse back to a 97 version and give it back to the program and it did not complain about it. I use that feild in the shop to label my offcut for tracking and finding them.

this is the SQL of the main report:

SELECT PanelLayout.MatID, PanelLayout.PatNo, Materials.Name, Materials.Width, Materials.Length, ([materials].[width]*[materials].[length])/144 AS sqFeet, Materials.Offcut, Materials.MatDBID, Job.CNCRunNo, Job.Name AS JobNumber
FROM Job INNER JOIN (PanelLayout INNER JOIN Materials ON PanelLayout.MatID = Materials.ID) ON Job.ID = PanelLayout.JobID
GROUP BY PanelLayout.MatID, PanelLayout.PatNo, Materials.Name, Materials.Width, Materials.Length, ([materials].[width]*[materials].[length])/144, Materials.Offcut, Materials.MatDBID, Job.CNCRunNo, Job.Name
HAVING (((Materials.Offcut)=False));


this is the sql of the sub report:

SELECT PanelLayout.MatID, PanelLayout.PatNo, Materials.Name, Materials.Offcut, ([materials].[width]*[materials].[length])/144 AS sqFeet, [tbl OptOffcuts].OffcutID, Round([materials].[width],6) AS width, Round([materials].[length],6) AS lenght, Materials.MatDBID
FROM (PanelLayout INNER JOIN Materials ON PanelLayout.MatID = Materials.ID) INNER JOIN [tbl OptOffcuts] ON (Materials.MatDBID = [tbl OptOffcuts].MatID) AND (Materials.Width = [tbl OptOffcuts].Width)
GROUP BY PanelLayout.MatID, PanelLayout.PatNo, Materials.Name, Materials.Offcut, ([materials].[width]*[materials].[length])/144, [tbl OptOffcuts].OffcutID, Round([materials].[width],6), Round([materials].[length],6), Materials.MatDBID
HAVING (((Materials.Offcut)=True));


i understand this may not give you that much, but its kinda pretty complicated to explain the entire thing, let me know if you need more....

Thanks,

Sylvain

 

[Remou]

When an offcut does not relate to material, what does it relate to? I notice that MatID in the offcuts table is related to MatDBID in materials, can it relate to MatID in PanelLayout for example?

You seem to have:
[tt]Job -> PanelLayout -> Materials -> Offcuts[/tt]

Which means that an offcut has to have a material in order to appear in the report (unless you wish to include all offcuts regardless of job etc.)

If this can be constructed differently, either:
[tt]Job -> PanelLayout -> Materials
|
-> Offcuts[/tt]
Or
[tt]Job -> PanelLayout -> Materials
|
-> Offcuts[/tt]

Offcuts can appear without a material.

Another possibility is a subreport of all unmatched offcuts.

I am having a little difficulty seeing how an offcut can exist without a material to be cut off ...

 

[dixxy]

ok thanks Remou, i will play with this and report back.

Thanks,

Sylvain