Crosstab Query Help

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi,

I have the following query

PARAMETERS [forms]!frmMetalRequirements![dtpDateTo] DateTime;
TRANSFORM Sum(tblMatlSchedule.OSQtyReqd) AS SumOfOSQtyReqd
SELECT tblMatlSchedule.JobNo, tblMatlSchedule.Stockcode,
tblMatlSchedule.StartDate,
IIf(Left$(tblMatlSchedule.Stockcode,1)="A","A","B") AS MetalType
FROM tblMatlSchedule
GROUP BY tblMatlSchedule.JobNo, tblMatlSchedule.Stockcode,
tblMatlSchedule.StartDate, MetalType
PIVOT "Wk" &
IIf(DateDiff("ww",[StartDate],Forms!frmMetalRequirements!dtpDateTo)>11,"11",
DateDiff("ww",[StartDate],Forms!frmMetalRequirements!dtpDateTo)) In
("Wk0","Wk1","Wk2","Wk3","Wk4","Wk5","Wk6","Wk7","Wk8","Wk9","Wk10","Wk11");

However, when it runs it asks for a parameter MetalType
How can I stop it asking for this?

Thanks
 
Hi


In the GROUP BY clause, change MetalType by:
IIf(Left$(tblMatlSchedule.Stockcode,1)="A","A","B")


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top