joining two queries.

[DD]

one query displays cases produced by item and raw material that should have
been used. second query displays what raw material was actually used.
sometimes not the same as what was called for. when i join these i get
duplicate cases produced when the raw material items do not match. is there
a way to prevent this.

thanks

 

[Steve Schapel]

DD,

In design view of the query, please select SQL from the VIew menu, and
then copy/paste the SQL view of the query into your reply. Thanks.

 

[DD]

this is what i have. any help is appreciated.
QRyCASES



SELECT Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Sum(Repack.PJTQTY) AS SumOfPJTQTY, Repack.[PJORD#], Repack21.ABRLINE, [Bom
File].FFRAWC, [Bom File].MNDESC, [Bom File].FFSOLD, [Bom File].FFSOUM, [Bom
File].FFILSF, [SumOfPJTQTY]*[ffsold]*[ffilsf] AS [Std Qty]

FROM ((Repack INNER JOIN Repack21 ON Repack.[PJORD#] = Repack21.[ABTCK#])
LEFT JOIN [QryItem#] ON Repack.PJPROD = [QryItem#].[MNMFG#]) LEFT JOIN [Bom
File] ON Repack.PJPROD = [Bom File].FFFOID

WHERE (((Repack.PJTUM)="CASE"))

GROUP BY Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Repack.[PJORD#], Repack21.ABRLINE, [Bom File].FFRAWC, [Bom File].MNDESC,
[Bom File].FFSOLD, [Bom File].FFSOUM, [Bom File].FFILSF

HAVING (((Repack21.ABRLINE)<>"REW" And (Repack21.ABRLINE)<>"PRE" And
(Repack21.ABRLINE)<>"BKUP"))

ORDER BY Repack.PJTDAT;



QRYLBS

SELECT Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Sum(Repack.PJTQTY) AS SumOfPJTQTY, Repack.[PJORD#], Repack21.ABRLINE

FROM (Repack INNER JOIN Repack21 ON Repack.[PJORD#] = Repack21.[ABTCK#])
LEFT JOIN [QryItem#] ON Repack.PJPROD = [QryItem#].[MNMFG#]

WHERE (((Repack.PJTUM)="LB"))

GROUP BY Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Repack.[PJORD#], Repack21.ABRLINE

HAVING (((Repack21.ABRLINE)<>"REW" And (Repack21.ABRLINE)<>"PRE" And
(Repack21.ABRLINE)<>"BKUP"));





QRYREC



SELECT QryCases.[PJORD#], QryCases.[QryItem#].MNDESC, QryCases.PJPROD,
QryCases.SumOfPJTQTY, QryCases.ABRLINE, QryCases.FFRAWC, QryCases.[Std Qty],
QryLbs.PJPROD, QryLbs.MNDESC, QryLbs.SumOfPJTQTY

FROM QryCases LEFT JOIN QryLbs ON QryCases.[PJORD#] = QryLbs.[PJORD#]

WHERE (((QryCases.FFRAWC) Like "010*"));



Here are the results.



PJORD# QryCasesSumof FFRAWC STDQTY QryLBSPJPROD QRYLBSSUmOF

LBL0641970 844 01001023000 11,647 01001023000 13,000

LBL0641970 844 01001023000 11,647 01001039000 2,470



I left out some columns so it would be easier to read. The 844 and 11647
are doubled because and extra item was used to produced this order.





What I would like is





PJORD# QryCasesSumof FFRAWC STDQTY QryLBSPJPROD QRYLBSSUmOF

LBL0641970 844 01001023000 11,647 01001023000 13,000

LBL0641970 0 01001023000 0 01001039000 2,470

 

[Steve Schapel]

DD,

Thanks for the additional information.

I can't see any way to get the result you are asking for. Not directly
anyway.

It seems to me that in effect, there is a one-to-many relationship
between the data in the two base queries.

What is the purpose of this QRYREC query? If it's for a Report, then
you can use the report's Sorting & Grouping to get a group header on the
"cases" side, so that is only shown once, and then the "LBS" side can if
necessary show more than one item for the PJORD#.

Or if you will be outputting to a form, then possibly you need to
consider dropping the QRYREC query, and using a form/subform based on
the QRyCASES and QRYLBS queries respectively.

--
Steve Schapel, Microsoft Access MVP

this is what i have. any help is appreciated.
QRyCASES



SELECT Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Sum(Repack.PJTQTY) AS SumOfPJTQTY, Repack.[PJORD#], Repack21.ABRLINE, [Bom
File].FFRAWC, [Bom File].MNDESC, [Bom File].FFSOLD, [Bom File].FFSOUM, [Bom
File].FFILSF, [SumOfPJTQTY]*[ffsold]*[ffilsf] AS [Std Qty]

FROM ((Repack INNER JOIN Repack21 ON Repack.[PJORD#] = Repack21.[ABTCK#])
LEFT JOIN [QryItem#] ON Repack.PJPROD = [QryItem#].[MNMFG#]) LEFT JOIN [Bom
File] ON Repack.PJPROD = [Bom File].FFFOID

WHERE (((Repack.PJTUM)="CASE"))

GROUP BY Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Repack.[PJORD#], Repack21.ABRLINE, [Bom File].FFRAWC, [Bom File].MNDESC,
[Bom File].FFSOLD, [Bom File].FFSOUM, [Bom File].FFILSF

HAVING (((Repack21.ABRLINE)<>"REW" And (Repack21.ABRLINE)<>"PRE" And
(Repack21.ABRLINE)<>"BKUP"))

ORDER BY Repack.PJTDAT;



QRYLBS

SELECT Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Sum(Repack.PJTQTY) AS SumOfPJTQTY, Repack.[PJORD#], Repack21.ABRLINE

FROM (Repack INNER JOIN Repack21 ON Repack.[PJORD#] = Repack21.[ABTCK#])
LEFT JOIN [QryItem#] ON Repack.PJPROD = [QryItem#].[MNMFG#]

WHERE (((Repack.PJTUM)="LB"))

GROUP BY Repack.PJTDAT, [QryItem#].MNDESC, Repack.PJTCOD, Repack.PJPROD,
Repack.[PJORD#], Repack21.ABRLINE

HAVING (((Repack21.ABRLINE)<>"REW" And (Repack21.ABRLINE)<>"PRE" And
(Repack21.ABRLINE)<>"BKUP"));





QRYREC



SELECT QryCases.[PJORD#], QryCases.[QryItem#].MNDESC, QryCases.PJPROD,
QryCases.SumOfPJTQTY, QryCases.ABRLINE, QryCases.FFRAWC, QryCases.[Std Qty],
QryLbs.PJPROD, QryLbs.MNDESC, QryLbs.SumOfPJTQTY

FROM QryCases LEFT JOIN QryLbs ON QryCases.[PJORD#] = QryLbs.[PJORD#]

WHERE (((QryCases.FFRAWC) Like "010*"));



Here are the results.



PJORD# QryCasesSumof FFRAWC STDQTY QryLBSPJPROD QRYLBSSUmOF

LBL0641970 844 01001023000 11,647 01001023000 13,000

LBL0641970 844 01001023000 11,647 01001039000 2,470



I left out some columns so it would be easier to read. The 844 and 11647
are doubled because and extra item was used to produced this order.





What I would like is





PJORD# QryCasesSumof FFRAWC STDQTY QryLBSPJPROD QRYLBSSUmOF

LBL0641970 844 01001023000 11,647 01001023000 13,000

LBL0641970 0 01001023000 0 01001039000 2,470