If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Query Help
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 |
#2
|
|||
|
|||
Crosstab Query Help
Hi
In the GROUP BY clause, change MetalType by: IIf(Left$(tblMatlSchedule.Stockcode,1)="A","A","B" ) Hoping it may help, Vanderghast, Access MVP "Newbie" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|