joining two queries.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DD - 26 Apr 2008 22:48 GMT

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

Reply to this Message

Steve Schapel - 26 Apr 2008 23:49 GMT

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.

Signature

Steve Schapel, Microsoft Access MVP

> one query displays cases produced by item and raw material that should have
> been used. second query displays what raw material was actually used.
[quoted text clipped - 3 lines]
>
> thanks

Reply to this Message

DD - 28 Apr 2008 23:41 GMT

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

> DD,
>
[quoted text clipped - 8 lines]
>>
>> thanks

Reply to this Message

Steve Schapel - 29 Apr 2008 09:50 GMT

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.

Signature

Steve Schapel, Microsoft Access MVP

> this is what i have. any help is appreciated.
> QRyCASES
[quoted text clipped - 63 lines]
>
> LBL0641970 0 01001023000 0 01001039000 2,470

Reply to this Message

Bob Quintal - 27 Apr 2008 12:50 GMT

> one query displays cases produced by item and raw material that
> should have been used. second query displays what raw material
[quoted text clipped - 3 lines]
>
> thanks

Yes, the first query should have an additional field which contains
a "Find Number"/1 for each material to be used. The "as-Built"
records should store the find number as well as the material
actually used.

The structures should contain fields
Item, findNo, Material, Qty, UoM
as well as others

You then Join on Item and FindNo and filter to As-Designed.material
<> As-Built.material

/1 Term taken from MIL-STD-100, Engineering Drawing Practices, an
obsolete document that contains everything you want to know about
the subject.

Signature

Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

Reply to this Message