How to combine queries????

J

Joseph

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).
 
D

Daryl S

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 said:
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).
Click to expand...
 
J

Joseph

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel crashes as soon as I open it? 0
multiple ranking (sorting), based on diff. criteria 0
Help...... 4
Problems opening workspaces to add tables/fields to secured access 0
Entourage having to restart 4
Excel 2008 Mac crashes every time I attempt to make a copy of a graph 0
Outlook connector crashes outlook 0
Outlook crashes with Outlook connector 2002 0

Top