use a column of one query as criteria in a second query

[Guest]

I have asked this question before but I hope that this time it will be worded
better and I actualy have three queries. [Floor Location Query] which takes
three fields and concatenates them into one, for the entire table; [HEAT LOAD
BY DATE QUERY] which retrieves the records that were changed between two
given dates; [Heat Load Report Query] which should take the records in the
concatenated field "Floor Location" of [HEAT LOAD BY DATE QUERY] as criteria
and poll for any matches in [Floor Location Query] and return the Sum of the
rest of the following fields ( "Amps", "Watts", & "CFM"). I hope that this
is a good description of what needs to be acomplished. The problem is that I
do not know how to get the fields for "Floor Location" into the third query
as criteria. Here are the three SQL views of the queries.

Floor Location Query:

SELECT [CHSR TABLE].MODULE, [CHSR TABLE].[BUS PLUG ROW #], Trim([MODULE] & "
" & [EQUIPMENT LOCATION ROW LETTER] & " " & [EQUIPMENT LOCATION ROW #]) AS
[FLOOR LOCATION], [CHSR TABLE].[DATE ENTERED], [CHSR TABLE].SECTION, [CHSR
TABLE].CELL, [CHSR TABLE].[BUS PLUG LETTER DESIGNATOR], [CHSR TABLE].UPS,
[CHSR TABLE].[MOC RC], [CHSR TABLE].[CHSR #], [CHSR TABLE].[PANEL NAME],
[CHSR TABLE].CIRCUIT, [CHSR TABLE].[NUMBER OF POLES], [CHSR TABLE].VOLTAGE,
[CHSR TABLE].PHASES, [CHSR TABLE].[DEVICE TYPE], [CHSR TABLE].[EQUIPMENT
LOCATION ROW LETTER], [CHSR TABLE].[EQUIPMENT LOCATION ROW #], [CHSR
TABLE].[PHASE "A" BREAKER AMPS], [CHSR TABLE].[PHASE "B" BREAKER AMPS], [CHSR
TABLE].[PHASE "C" BREAKER AMPS], [CHSR TABLE].[PHASE "A" PROFILED AMPS],
[CHSR TABLE].[PHASE "B" PROFILED AMPS], [CHSR TABLE].[PHASE "C" PROFILED
AMPS], [CHSR TABLE].NOTES, (([PHASE "A" PROFILED AMPS]+[PHASE "B" PROFILED
AMPS]+[PHASE "C" PROFILED AMPS])/[NUMBER OF POLES]) AS AMPS, IIf([NUMBER OF
POLES]=3,[AMPS]*[VOLTAGE]*1.732,[VOLTAGE]*[AMPS]) AS WATTS, [WATTS]*0.1226 AS
CFM
FROM [CHSR TABLE]
ORDER BY [CHSR TABLE].MODULE, [CHSR TABLE].[BUS PLUG ROW #], Trim([MODULE] &
" " & [EQUIPMENT LOCATION ROW LETTER] & " " & [EQUIPMENT LOCATION ROW #]),
[CHSR TABLE].SECTION, [CHSR TABLE].CELL, [CHSR TABLE].[BUS PLUG LETTER
DESIGNATOR];


HEAT LOAD BY DATE QUERY:

SELECT [Floor Location Query].[FLOOR LOCATION], [Floor Location Query].[DATE
ENTERED], Sum([Floor Location Query].AMPS) AS SumOfAMPS, Sum([Floor Location
Query].WATTS) AS SumOfWATTS, Sum([Floor Location Query].CFM) AS SumOfCFM
FROM [Floor Location Query]
GROUP BY [Floor Location Query].[FLOOR LOCATION], [Floor Location
Query].[DATE ENTERED], [Floor Location Query].CELL
HAVING ((([Floor Location Query].[DATE ENTERED]) Between [Enter the start
date] And [Enter the end date]) AND (([Floor Location Query].CELL)<>"SS"));

Heat Load Report Query:

SELECT DISTINCTROW [Floor Location Query].[FLOOR LOCATION], Sum([Floor
Location Query].AMPS) AS [Sum Of AMPS], Sum([Floor Location Query].WATTS) AS
[Sum Of WATTS], Sum([Floor Location Query].CFM) AS [Sum Of CFM]
FROM [Floor Location Query]
GROUP BY [Floor Location Query].[FLOOR LOCATION];

 

[Pieter Wijnen]

Try using exists (not exists)

ie
SELECT AVALUE FROM A
WHERE NOT EXISTS (SELECT 'X' FROM B WHERE A.ID=B.ID)

as I don't know to much about your data

HTH
--
Pieter Wijnen

My feeble Access pages (good links though)
http://www.thuleeng.com/access
When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca/
http://allenbrowne.com/
http://www.lebans.com/

PS you should get rid of the spaces in your (table) definitions

I have asked this question before but I hope that this time it will be
worded
better and I actualy have three queries. [Floor Location Query] which
takes
three fields and concatenates them into one, for the entire table; [HEAT
LOAD
BY DATE QUERY] which retrieves the records that were changed between two
given dates; [Heat Load Report Query] which should take the records in
the
concatenated field "Floor Location" of [HEAT LOAD BY DATE QUERY] as
criteria
and poll for any matches in [Floor Location Query] and return the Sum of
the
rest of the following fields ( "Amps", "Watts", & "CFM"). I hope that
this
is a good description of what needs to be acomplished. The problem is
that I
do not know how to get the fields for "Floor Location" into the third
query
as criteria. Here are the three SQL views of the queries.

Floor Location Query:

SELECT [CHSR TABLE].MODULE, [CHSR TABLE].[BUS PLUG ROW #], Trim([MODULE] &
"
" & [EQUIPMENT LOCATION ROW LETTER] & " " & [EQUIPMENT LOCATION ROW #]) AS
[FLOOR LOCATION], [CHSR TABLE].[DATE ENTERED], [CHSR TABLE].SECTION, [CHSR
TABLE].CELL, [CHSR TABLE].[BUS PLUG LETTER DESIGNATOR], [CHSR TABLE].UPS,
[CHSR TABLE].[MOC RC], [CHSR TABLE].[CHSR #], [CHSR TABLE].[PANEL NAME],
[CHSR TABLE].CIRCUIT, [CHSR TABLE].[NUMBER OF POLES], [CHSR
TABLE].VOLTAGE,
[CHSR TABLE].PHASES, [CHSR TABLE].[DEVICE TYPE], [CHSR TABLE].[EQUIPMENT
LOCATION ROW LETTER], [CHSR TABLE].[EQUIPMENT LOCATION ROW #], [CHSR
TABLE].[PHASE "A" BREAKER AMPS], [CHSR TABLE].[PHASE "B" BREAKER AMPS],
[CHSR
TABLE].[PHASE "C" BREAKER AMPS], [CHSR TABLE].[PHASE "A" PROFILED AMPS],
[CHSR TABLE].[PHASE "B" PROFILED AMPS], [CHSR TABLE].[PHASE "C" PROFILED
AMPS], [CHSR TABLE].NOTES, (([PHASE "A" PROFILED AMPS]+[PHASE "B" PROFILED
AMPS]+[PHASE "C" PROFILED AMPS])/[NUMBER OF POLES]) AS AMPS, IIf([NUMBER
OF
POLES]=3,[AMPS]*[VOLTAGE]*1.732,[VOLTAGE]*[AMPS]) AS WATTS, [WATTS]*0.1226
AS
CFM
FROM [CHSR TABLE]
ORDER BY [CHSR TABLE].MODULE, [CHSR TABLE].[BUS PLUG ROW #], Trim([MODULE]
&
" " & [EQUIPMENT LOCATION ROW LETTER] & " " & [EQUIPMENT LOCATION ROW #]),
[CHSR TABLE].SECTION, [CHSR TABLE].CELL, [CHSR TABLE].[BUS PLUG LETTER
DESIGNATOR];


HEAT LOAD BY DATE QUERY:

SELECT [Floor Location Query].[FLOOR LOCATION], [Floor Location
Query].[DATE
ENTERED], Sum([Floor Location Query].AMPS) AS SumOfAMPS, Sum([Floor
Location
Query].WATTS) AS SumOfWATTS, Sum([Floor Location Query].CFM) AS SumOfCFM
FROM [Floor Location Query]
GROUP BY [Floor Location Query].[FLOOR LOCATION], [Floor Location
Query].[DATE ENTERED], [Floor Location Query].CELL
HAVING ((([Floor Location Query].[DATE ENTERED]) Between [Enter the start
date] And [Enter the end date]) AND (([Floor Location
Query].CELL)<>"SS"));

Heat Load Report Query:

SELECT DISTINCTROW [Floor Location Query].[FLOOR LOCATION], Sum([Floor
Location Query].AMPS) AS [Sum Of AMPS], Sum([Floor Location Query].WATTS)
AS
[Sum Of WATTS], Sum([Floor Location Query].CFM) AS [Sum Of CFM]
FROM [Floor Location Query]
GROUP BY [Floor Location Query].[FLOOR LOCATION];