Join two dynamic crosstab queries 1

[evr72]

Hello,

Is there a way to join two dynamic cross tab queries?

If I look at my first crosstab
these are the fields I see
Item Op1 OP2 OP3 OP4
50 0 15 3

The table looks like this
Item qty sum value op group column heading

the second query is the same but instead of qty I have hours
but when I try to join them I see this

Item
op1
op2
op3

the issue is that in some cases there could be more than one op or less than one op

not sure how to go about this one

any help is appreciated.

Thanks!!

 

[dhookom]

It would help if you provided the SQL view of your queries, some sample records, and how you want them to appear/display in your final output.

Duane
Hook'D on Access
MS Access MVP

 

[evr72]

Sorry for the delay

Here is the sql statement that I have for the hours

TRANSFORM Sum(MOMastandMORout.[Qty Complete Cur Oper]) AS [SumOfQty Complete Cur Oper]
SELECT MOMastandMORout.OSTAT, MOMastandMORout.Dept, MOMastandMORout.[MO Number], MOMastandMORout.Item, MOMastandMORout.[Act Start Date], MOMastandMORout.[Last Activity Date], MOMastandMORout.[MO Qty], MOMastandMORout.[Rcv To Stock Qty], MOMastandMORout.[Deviation Qty], MOMastandMORout.Scrap, MOMastandMORout.[Remaining Qty]
FROM MOMastandMORout
WHERE (((MOMastandMORout.Dept)='43'))
GROUP BY MOMastandMORout.OSTAT, MOMastandMORout.Dept, MOMastandMORout.[MO Number], MOMastandMORout.Item, MOMastandMORout.[Act Start Date], MOMastandMORout.[Last Activity Date], MOMastandMORout.[MO Qty], MOMastandMORout.[Rcv To Stock Qty], MOMastandMORout.[Deviation Qty], MOMastandMORout.Scrap, MOMastandMORout.[Remaining Qty]
ORDER BY MOMastandMORout.[MO Number], MOMastandMORout.Item
PIVOT MOMastandMORout.[Oper #];

 

[dhookom]

I'm not understanding if you want to display a union query of the two crosstabs or join the Row Heading fields in a standard query. For example do you want to see a quantity and hours column for each Op?

Duane
Hook'D on Access
MS Access MVP

 

[evr72]

dhhookom,
Yes, I would like to see quantity and hours column for each op, possibly add an extra column in the future for further calculations

 

[PHV]

Have a look here:
faq701-4524

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886