How to combine queries????
in [Queries]
|
From: Joseph on 21 Apr 2010 18:36 I have two related queries/tables. One: TRANSFORM Max(MeritsMain2Total.Total) AS MaxOfTotal SELECT CadetsName.CadetID, MeritsMain2Total.CadetName, CInt(Format([dtgofmerits],"ww")) AS Week, Phase.Phase, Dorms.Dorm FROM MonthlyPeriods, (Phase INNER JOIN (Dorms INNER JOIN CadetsName ON Dorms.DormID = CadetsName.DormID) ON Phase.PhaseID = CadetsName.PhaseID) INNER JOIN MeritsMain2Total ON CadetsName.CadetID = MeritsMain2Total.CadetID GROUP BY CadetsName.CadetID, MeritsMain2Total.CadetName, CInt(Format([dtgofmerits],"ww")), Phase.Phase, Dorms.Dorm PIVOT Format([DTGofMerits],"dddd") In ("Friday","Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday"); Which outputs: "CadetID","CadetName","Week","Phase","Dorm","Friday","Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday" 145,"Test, Test -",14,"Orientation","Alpha",-4.00,0.00,,,,, 145,"Test, Test -",15,"Orientation","Alpha",0.00,0.00,0.00,-3.00,5.00,0.00,0.00 145,"Test, Test -",16,"Orientation","Alpha",,0.00,0.00,0.00,0.00,0.00,0.00 145,"Test, Test -",17,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 145,"Test, Test -",18,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 145,"Test, Test -",19,"Orientation","Alpha",,,0.00,0.00,0.00,, 225,"Test2, Test2 -",14,"Orientation","Alpha",,0.00,,,,, 225,"Test2, Test2 -",15,"Orientation","Alpha",0.00,0.00,-2.00,0.00,0.00,0.00,0.00 225,"Test2, Test2 -",16,"Orientation","Alpha",,0.00,0.00,0.00,0.00,0.00,0.00 225,"Test2, Test2 -",17,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 225,"Test2, Test2 -",18,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 225,"Test2, Test2 -",19,"Orientation","Alpha",,,0.00,0.00,0.00,, 226,"Test3, Test3 -",14,"Orientation","Alpha",0.00,0.00,,,,, 226,"Test3, Test3 -",15,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 226,"Test3, Test3 -",16,"Orientation","Alpha",,0.00,0.00,0.00,0.00,0.00,0.00 226,"Test3, Test3 -",17,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 226,"Test3, Test3 -",18,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 226,"Test3, Test3 -",19,"Orientation","Alpha",,,0.00,0.00,0.00,, and a table: CREATE TABLE `BootCampMerits2`.`MonthlyPeriods` ( `idMonthlyPeriods` int(10) unsigned NOT NULL auto_increment, `DTGCreated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `User` varchar(45) NOT NULL, `Computer` varchar(45) NOT NULL, `Period` int(10) unsigned NOT NULL, `WeekStart` int(10) unsigned NOT NULL, `WeekStop` int(10) unsigned NOT NULL, PRIMARY KEY (`idMonthlyPeriods`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; with: "idMonthlyPeriods","DTGCreated","User","Computer","Period","WeekStart","WeekStop" 1,2010-4-21 16:38:22,"martinezjr","TRAINING",1,1,4 2,2010-4-21 16:39:02,"martinezjr","TRAINING",2,5,8 3,2010-4-21 16:39:22,"martinezjr","TRAINING",3,9,12 4,2010-4-21 16:39:27,"martinezjr","TRAINING",4,13,16 5,2010-4-21 16:39:35,"martinezjr","TRAINING",5,17,20 6,2010-4-21 16:39:40,"martinezjr","TRAINING",6,21,24 7,2010-4-21 16:39:46,"martinezjr","TRAINING",7,25,28 8,2010-4-21 16:39:51,"martinezjr","TRAINING",8,29,32 9,2010-4-21 16:40:02,"martinezjr","TRAINING",9,33,36 10,2010-4-21 16:40:09,"martinezjr","TRAINING",10,37,40 11,2010-4-21 16:40:18,"martinezjr","TRAINING",11,41,44 12,2010-4-21 16:40:24,"martinezjr","TRAINING",12,44,48 13,2010-4-21 16:40:31,"martinezjr","TRAINING",13,49,52 How can I pull data from MonthlyMerits_2 utilizing "Periods" from MonthlyPeriods? I have tried: SELECT MonthlyMerits_2.CadetName FROM MonthlyPeriods, MonthlyMerits_2 WHERE (([monthlymerits_2]![week] Between (select [weekstart] from [monthlyperiods] where [monthlyperiods]![period] = 4) And (select [weekstop] from [monthlyperiods] where [monthlyperiods]![period] = 4))); and SELECT MonthlyMerits_2.CadetName FROM MonthlyPeriods, MonthlyMerits_2 WHERE (((select [weekstart] from [monthlyperiods] where [monthlyperiods]![period] = 5)<[monthlymerits_2].[week] And [monthlymerits_2].[week]<(select [weekstop] from [monthlyperiods] where [monthlyperiods]![period] =5))); I get 117 records instead a max of 4 per CadetID, which right now only should be 12 (see above output from MonthlyMerits).
From: Daryl S on 22 Apr 2010 11:42 Joseph - Is this what you are looking for? SELECT MonthlyMerits_2.CadetName FROM MonthlyPeriods, MonthlyMerits_2 WHERE (([monthlymerits_2]![week] Between [monthlyperiods]![weekstart] AND [monthlyperiods]![weekstop] AND [monthlyperiods]![period] = 4))); -- Daryl S "Joseph" wrote: > I have two related queries/tables. > > One: > TRANSFORM Max(MeritsMain2Total.Total) AS MaxOfTotal > SELECT CadetsName.CadetID, MeritsMain2Total.CadetName, > CInt(Format([dtgofmerits],"ww")) AS Week, Phase.Phase, Dorms.Dorm > FROM MonthlyPeriods, (Phase INNER JOIN (Dorms INNER JOIN CadetsName ON > Dorms.DormID = CadetsName.DormID) ON Phase.PhaseID = CadetsName.PhaseID) > INNER JOIN MeritsMain2Total ON CadetsName.CadetID = MeritsMain2Total.CadetID > GROUP BY CadetsName.CadetID, MeritsMain2Total.CadetName, > CInt(Format([dtgofmerits],"ww")), Phase.Phase, Dorms.Dorm > PIVOT Format([DTGofMerits],"dddd") In > ("Friday","Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday"); > > Which outputs: > "CadetID","CadetName","Week","Phase","Dorm","Friday","Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday" > 145,"Test, Test -",14,"Orientation","Alpha",-4.00,0.00,,,,, > 145,"Test, Test > -",15,"Orientation","Alpha",0.00,0.00,0.00,-3.00,5.00,0.00,0.00 > 145,"Test, Test -",16,"Orientation","Alpha",,0.00,0.00,0.00,0.00,0.00,0.00 > 145,"Test, Test -",17,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 > 145,"Test, Test -",18,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 > 145,"Test, Test -",19,"Orientation","Alpha",,,0.00,0.00,0.00,, > 225,"Test2, Test2 -",14,"Orientation","Alpha",,0.00,,,,, > 225,"Test2, Test2 > -",15,"Orientation","Alpha",0.00,0.00,-2.00,0.00,0.00,0.00,0.00 > 225,"Test2, Test2 -",16,"Orientation","Alpha",,0.00,0.00,0.00,0.00,0.00,0.00 > 225,"Test2, Test2 > -",17,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 > 225,"Test2, Test2 > -",18,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 > 225,"Test2, Test2 -",19,"Orientation","Alpha",,,0.00,0.00,0.00,, > 226,"Test3, Test3 -",14,"Orientation","Alpha",0.00,0.00,,,,, > 226,"Test3, Test3 > -",15,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 > 226,"Test3, Test3 -",16,"Orientation","Alpha",,0.00,0.00,0.00,0.00,0.00,0.00 > 226,"Test3, Test3 > -",17,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 > 226,"Test3, Test3 > -",18,"Orientation","Alpha",0.00,0.00,0.00,0.00,0.00,0.00,0.00 > 226,"Test3, Test3 -",19,"Orientation","Alpha",,,0.00,0.00,0.00,, > > > and a table: > CREATE TABLE `BootCampMerits2`.`MonthlyPeriods` ( > `idMonthlyPeriods` int(10) unsigned NOT NULL auto_increment, > `DTGCreated` timestamp NOT NULL default CURRENT_TIMESTAMP on update > CURRENT_TIMESTAMP, > `User` varchar(45) NOT NULL, > `Computer` varchar(45) NOT NULL, > `Period` int(10) unsigned NOT NULL, > `WeekStart` int(10) unsigned NOT NULL, > `WeekStop` int(10) unsigned NOT NULL, > PRIMARY KEY (`idMonthlyPeriods`) > ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; > > with: > "idMonthlyPeriods","DTGCreated","User","Computer","Period","WeekStart","WeekStop" > 1,2010-4-21 16:38:22,"martinezjr","TRAINING",1,1,4 > 2,2010-4-21 16:39:02,"martinezjr","TRAINING",2,5,8 > 3,2010-4-21 16:39:22,"martinezjr","TRAINING",3,9,12 > 4,2010-4-21 16:39:27,"martinezjr","TRAINING",4,13,16 > 5,2010-4-21 16:39:35,"martinezjr","TRAINING",5,17,20 > 6,2010-4-21 16:39:40,"martinezjr","TRAINING",6,21,24 > 7,2010-4-21 16:39:46,"martinezjr","TRAINING",7,25,28 > 8,2010-4-21 16:39:51,"martinezjr","TRAINING",8,29,32 > 9,2010-4-21 16:40:02,"martinezjr","TRAINING",9,33,36 > 10,2010-4-21 16:40:09,"martinezjr","TRAINING",10,37,40 > 11,2010-4-21 16:40:18,"martinezjr","TRAINING",11,41,44 > 12,2010-4-21 16:40:24,"martinezjr","TRAINING",12,44,48 > 13,2010-4-21 16:40:31,"martinezjr","TRAINING",13,49,52 > > How can I pull data from MonthlyMerits_2 utilizing "Periods" from > MonthlyPeriods? > > I have tried: > SELECT MonthlyMerits_2.CadetName > FROM MonthlyPeriods, MonthlyMerits_2 > WHERE (([monthlymerits_2]![week] Between (select [weekstart] from > [monthlyperiods] where [monthlyperiods]![period] = 4) And (select [weekstop] > from [monthlyperiods] where [monthlyperiods]![period] = 4))); > > and > > SELECT MonthlyMerits_2.CadetName > FROM MonthlyPeriods, MonthlyMerits_2 > WHERE (((select [weekstart] from [monthlyperiods] where > [monthlyperiods]![period] = 5)<[monthlymerits_2].[week] And > [monthlymerits_2].[week]<(select [weekstop] from [monthlyperiods] where > [monthlyperiods]![period] =5))); > > I get 117 records instead a max of 4 per CadetID, which right now only > should be 12 (see above output from MonthlyMerits). > >
From: Joseph on 24 Apr 2010 12:05 That is what I was trying to do in the first query, but I did not think that it would do it because there is no relationship between the table/query. "Daryl S" wrote: > Joseph - > > Is this what you are looking for? > > SELECT MonthlyMerits_2.CadetName > FROM MonthlyPeriods, MonthlyMerits_2 > WHERE (([monthlymerits_2]![week] Between [monthlyperiods]![weekstart] > AND [monthlyperiods]![weekstop] > AND [monthlyperiods]![period] = 4))); > > -- > Daryl S
|
Pages: 1 Prev: combine multiple recs into one with repeating fields Next: Multitable query problem |