Using a column of fields from one query as criteria in a query.

J

JUSTIN HOBBS

I have a paramater query that pulls the entries between two dates, it pulls
up multiple records. I need to use one column (FLOOR LOCATION) of those
records as criteria in a new query to pull up any other entries no matter
what date they are entered. I know this is possible by typing them in, but
is there an expression that can be typed into the criteria to reference the
column and put them in the criteria as an "Or" statement. If it will help I
here is the SQL of the two queries.

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM, [HEAT LOAD BY DATE QUERY].MODULE
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW
#], [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY
DATE QUERY].SECTION, [HEAT LOAD BY DATE QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];

Here is the second query:

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER], [HEAT LOAD BY DATE QUERY].SECTION, [HEAT LOAD BY DATE
QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];
 
K

Ken Sheridan

Justin:

If I understand you correctly I think you'll need a subquery in the second
query's WHERE clause (which should be used here rather than a HAVING clause
BTW) which references the first query (called Query1 below) so that the outer
query returns rows where the first query returns at least one row with a
matching Floor location value.

SELECT
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION],
Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS,
Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE
[HEAT LOAD BY DATE QUERY].CELL <>"ss"
OR EXISTS
(SELECT *
FROM Query1
WHERE
Query1.[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #]))
GROUP BY
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER],
[HEAT LOAD BY DATE QUERY].SECTION,
[HEAT LOAD BY DATE QUERY].CELL
ORDER BY
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER];

Ken Sheridan
Stafford, England

JUSTIN HOBBS said:
I have a paramater query that pulls the entries between two dates, it pulls
up multiple records. I need to use one column (FLOOR LOCATION) of those
records as criteria in a new query to pull up any other entries no matter
what date they are entered. I know this is possible by typing them in, but
is there an expression that can be typed into the criteria to reference the
column and put them in the criteria as an "Or" statement. If it will help I
here is the SQL of the two queries.

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM, [HEAT LOAD BY DATE QUERY].MODULE
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW
#], [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY
DATE QUERY].SECTION, [HEAT LOAD BY DATE QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];

Here is the second query:

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER], [HEAT LOAD BY DATE QUERY].SECTION, [HEAT LOAD BY DATE
QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];
Click to expand...
 
J

JUSTIN HOBBS

--
Thanks for any assistance that you can provide.

Justin


Ken Sheridan said:
Justin:

If I understand you correctly I think you'll need a subquery in the second
query's WHERE clause (which should be used here rather than a HAVING clause
BTW) which references the first query (called Query1 below) so that the outer
query returns rows where the first query returns at least one row with a
matching Floor location value.

SELECT
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION],
Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS,
Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE
[HEAT LOAD BY DATE QUERY].CELL <>"ss"
OR EXISTS
(SELECT *
FROM Query1
WHERE
Query1.[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #]))
GROUP BY
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER],
[HEAT LOAD BY DATE QUERY].SECTION,
[HEAT LOAD BY DATE QUERY].CELL
ORDER BY
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER];

Ken Sheridan
Stafford, England

JUSTIN HOBBS said:
I have a paramater query that pulls the entries between two dates, it pulls
up multiple records. I need to use one column (FLOOR LOCATION) of those
records as criteria in a new query to pull up any other entries no matter
what date they are entered. I know this is possible by typing them in, but
is there an expression that can be typed into the criteria to reference the
column and put them in the criteria as an "Or" statement. If it will help I
here is the SQL of the two queries.

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM, [HEAT LOAD BY DATE QUERY].MODULE
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW
#], [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY
DATE QUERY].SECTION, [HEAT LOAD BY DATE QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];

Here is the second query:

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER], [HEAT LOAD BY DATE QUERY].SECTION, [HEAT LOAD BY DATE
QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];
Click to expand...
Click to expand...
 
J

JUSTIN HOBBS

Thanks for responding so quickly,
The help you provided did get me a little closer but it keeps asking for me
to enter the floor location letter and the floor location row #. It also
returns a large number of records versus just the floor locations that [HEAT
LOAD BY DATE QUERY TWO] returned. I took out all unecessary fields from each
query to try and condense and it is now asking for the [Module] as well. The
only reason I see for this is that it states floor location like this
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),

instead of as [FLOOR LOCATION] but I am not sure, here are the queries as
they are now.

(Query 1)= [HEAT LOAD BY DATE QUERY TWO]

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].CELL, [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #]
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]);

(QUERY 2) = [HEAT LOAD REPORT QUERY]

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss")) OR (((Exists (SELECT *
FROM [HEAT LOAD BY DATE QUERY TWO]
WHERE
[HEAT LOAD BY DATE QUERY TWO].[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #])))<>False))
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]);

The goal is to look up what changed between dates with the first query and
then check the rest of the data base for every floor location that changed
and get the sums of amps, watts, and cfm; for each of those locations with
the second query.


--
Thanks for any assistance that you can provide.

Justin


Ken Sheridan said:
Justin:

If I understand you correctly I think you'll need a subquery in the second
query's WHERE clause (which should be used here rather than a HAVING clause
BTW) which references the first query (called Query1 below) so that the outer
query returns rows where the first query returns at least one row with a
matching Floor location value.

SELECT
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION],
Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS,
Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE
[HEAT LOAD BY DATE QUERY].CELL <>"ss"
OR EXISTS
(SELECT *
FROM Query1
WHERE
Query1.[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #]))
GROUP BY
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER],
[HEAT LOAD BY DATE QUERY].SECTION,
[HEAT LOAD BY DATE QUERY].CELL
ORDER BY
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER];

Ken Sheridan
Stafford, England

JUSTIN HOBBS said:
I have a paramater query that pulls the entries between two dates, it pulls
up multiple records. I need to use one column (FLOOR LOCATION) of those
records as criteria in a new query to pull up any other entries no matter
what date they are entered. I know this is possible by typing them in, but
is there an expression that can be typed into the criteria to reference the
column and put them in the criteria as an "Or" statement. If it will help I
here is the SQL of the two queries.

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM, [HEAT LOAD BY DATE QUERY].MODULE
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW
#], [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY
DATE QUERY].SECTION, [HEAT LOAD BY DATE QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];

Here is the second query:

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER], [HEAT LOAD BY DATE QUERY].SECTION, [HEAT LOAD BY DATE
QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];
Click to expand...
Click to expand...
 
K

Ken Sheridan

Justin:

I'm not sure that I fully understand what rows you want returned. You
mentioned a Boolean OR in your original post which suggested to me that you
want all rows returned by HEAT LOAD BY DATE QUERY plus any rows where the
FLOOR LOCATION value matches a FLOOR LOCATION value returned in any row by
the first query in your original post. That doesn't seem to tally, however,
with your description of your aims as:

"The goal is to look up what changed between dates with the first query and
then check the rest of the data base for every floor location that changed
and get the sums of amps, watts, and cfm; for each of those locations with
the second query."

That sound to me more like a simple restriction on the second query to
return rows where the floor locations match the floor locations returned by
the first (date restricted) query. That should be possible simply by INNER
JOINing the queries on the FLOOR LOCATION columns and as this will restrict
the rows returned by the second query to those with matches in the first
query:

SELECT Query2.*
FROM Query2 INNER JOIN Query1
ON Query2.[FLOOR LOCATION] = Query1.[ FLOOR LOCATION];

Ken Sheridan
Stafford, England


JUSTIN HOBBS said:
Thanks for responding so quickly,
The help you provided did get me a little closer but it keeps asking for me
to enter the floor location letter and the floor location row #. It also
returns a large number of records versus just the floor locations that [HEAT
LOAD BY DATE QUERY TWO] returned. I took out all unecessary fields from each
query to try and condense and it is now asking for the [Module] as well. The
only reason I see for this is that it states floor location like this
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),

instead of as [FLOOR LOCATION] but I am not sure, here are the queries as
they are now.

(Query 1)= [HEAT LOAD BY DATE QUERY TWO]

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].CELL, [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #]
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]);

(QUERY 2) = [HEAT LOAD REPORT QUERY]

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss")) OR (((Exists (SELECT *
FROM [HEAT LOAD BY DATE QUERY TWO]
WHERE
[HEAT LOAD BY DATE QUERY TWO].[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #])))<>False))
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]);

The goal is to look up what changed between dates with the first query and
then check the rest of the data base for every floor location that changed
and get the sums of amps, watts, and cfm; for each of those locations with
the second query.


--
Thanks for any assistance that you can provide.

Justin


Ken Sheridan said:
Justin:

If I understand you correctly I think you'll need a subquery in the second
query's WHERE clause (which should be used here rather than a HAVING clause
BTW) which references the first query (called Query1 below) so that the outer
query returns rows where the first query returns at least one row with a
matching Floor location value.

SELECT
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION],
Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS,
Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE
[HEAT LOAD BY DATE QUERY].CELL <>"ss"
OR EXISTS
(SELECT *
FROM Query1
WHERE
Query1.[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #]))
GROUP BY
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER],
[HEAT LOAD BY DATE QUERY].SECTION,
[HEAT LOAD BY DATE QUERY].CELL
ORDER BY
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER];

Ken Sheridan
Stafford, England

JUSTIN HOBBS said:
I have a paramater query that pulls the entries between two dates, it pulls
up multiple records. I need to use one column (FLOOR LOCATION) of those
records as criteria in a new query to pull up any other entries no matter
what date they are entered. I know this is possible by typing them in, but
is there an expression that can be typed into the criteria to reference the
column and put them in the criteria as an "Or" statement. If it will help I
here is the SQL of the two queries.

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM, [HEAT LOAD BY DATE QUERY].MODULE
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW
#], [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY
DATE QUERY].SECTION, [HEAT LOAD BY DATE QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];

Here is the second query:

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER], [HEAT LOAD BY DATE QUERY].SECTION, [HEAT LOAD BY DATE
QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];
Click to expand...
Click to expand...
Click to expand...
 
J

JUSTIN HOBBS

Ken,

I tried the the SQL in your last post but it gives an error stating Circular
reference caused by HEAT LOAD REPORT QUERY. Is there something that i missed
in adapting this? I thought that all i needed to do is add the actual query
names. Also your current description of what i need to do seems to be
correct. Sorry about all of the trouble but I am not good with the SQL, I
included it because it seems that most posts include the SQL of there problem
and thought that it would help describe the problem. I appreciate any
information that you can provide. Here is what i did to your last post to
adapt it.

SELECT [HEAT LOAD REPORT QUERY].*
FROM [HEAT LOAD REPORT QUERY] INNER JOIN [HEAT LOAD BY DATE QUERY TWO]
ON [HEAT LOAD REPORT QUERY].[FLOOR LOCATION] = [HEAT LOAD BY DATE QUERY
TWO].[ FLOOR LOCATION];




--
Thanks for any assistance that you can provide.

Justin


Ken Sheridan said:
Justin:

I'm not sure that I fully understand what rows you want returned. You
mentioned a Boolean OR in your original post which suggested to me that you
want all rows returned by HEAT LOAD BY DATE QUERY plus any rows where the
FLOOR LOCATION value matches a FLOOR LOCATION value returned in any row by
the first query in your original post. That doesn't seem to tally, however,
with your description of your aims as:

"The goal is to look up what changed between dates with the first query and
then check the rest of the data base for every floor location that changed
and get the sums of amps, watts, and cfm; for each of those locations with
the second query."

That sound to me more like a simple restriction on the second query to
return rows where the floor locations match the floor locations returned by
the first (date restricted) query. That should be possible simply by INNER
JOINing the queries on the FLOOR LOCATION columns and as this will restrict
the rows returned by the second query to those with matches in the first
query:

SELECT Query2.*
FROM Query2 INNER JOIN Query1
ON Query2.[FLOOR LOCATION] = Query1.[ FLOOR LOCATION];

Ken Sheridan
Stafford, England


JUSTIN HOBBS said:
Thanks for responding so quickly,
The help you provided did get me a little closer but it keeps asking for me
to enter the floor location letter and the floor location row #. It also
returns a large number of records versus just the floor locations that [HEAT
LOAD BY DATE QUERY TWO] returned. I took out all unecessary fields from each
query to try and condense and it is now asking for the [Module] as well. The
only reason I see for this is that it states floor location like this
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),

instead of as [FLOOR LOCATION] but I am not sure, here are the queries as
they are now.

(Query 1)= [HEAT LOAD BY DATE QUERY TWO]

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].CELL, [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #]
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]);

(QUERY 2) = [HEAT LOAD REPORT QUERY]

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss")) OR (((Exists (SELECT *
FROM [HEAT LOAD BY DATE QUERY TWO]
WHERE
[HEAT LOAD BY DATE QUERY TWO].[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #])))<>False))
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]);

The goal is to look up what changed between dates with the first query and
then check the rest of the data base for every floor location that changed
and get the sums of amps, watts, and cfm; for each of those locations with
the second query.


--
Thanks for any assistance that you can provide.

Justin


Ken Sheridan said:
Justin:

If I understand you correctly I think you'll need a subquery in the second
query's WHERE clause (which should be used here rather than a HAVING clause
BTW) which references the first query (called Query1 below) so that the outer
query returns rows where the first query returns at least one row with a
matching Floor location value.

SELECT
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION],
Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS,
Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
WHERE
[HEAT LOAD BY DATE QUERY].CELL <>"ss"
OR EXISTS
(SELECT *
FROM Query1
WHERE
Query1.[FLOOR LOCATION] =
Trim([HEAT LOAD BY DATE QUERY].[MODULE] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER] & " " &
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #]))
GROUP BY
Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]),
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER],
[HEAT LOAD BY DATE QUERY].SECTION,
[HEAT LOAD BY DATE QUERY].CELL
ORDER BY
[HEAT LOAD BY DATE QUERY].MODULE,
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW #],
[HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER];

Ken Sheridan
Stafford, England

:

I have a paramater query that pulls the entries between two dates, it pulls
up multiple records. I need to use one column (FLOOR LOCATION) of those
records as criteria in a new query to pull up any other entries no matter
what date they are entered. I know this is possible by typing them in, but
is there an expression that can be typed into the criteria to reference the
column and put them in the criteria as an "Or" statement. If it will help I
here is the SQL of the two queries.

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], [HEAT LOAD BY DATE
QUERY].[DATE ENTERED], Sum([HEAT LOAD BY DATE QUERY].AMPS) AS SumOfAMPS,
Sum([HEAT LOAD BY DATE QUERY].WATTS) AS SumOfWATTS, Sum([HEAT LOAD BY DATE
QUERY].CFM) AS SumOfCFM, [HEAT LOAD BY DATE QUERY].MODULE
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].[DATE ENTERED], [HEAT
LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW
#], [HEAT LOAD BY DATE QUERY].[EQUIPMENT LOCATION ROW LETTER], [HEAT LOAD BY
DATE QUERY].SECTION, [HEAT LOAD BY DATE QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].[DATE ENTERED]) Between [ENTER THE START
DATE ] And [ENTER THE ENDING DATE]) AND (([HEAT LOAD BY DATE
QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];

Here is the second query:

SELECT Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION], Sum([HEAT LOAD BY DATE
QUERY].AMPS) AS SumOfAMPS, Sum([HEAT LOAD BY DATE QUERY].WATTS) AS
SumOfWATTS, Sum([HEAT LOAD BY DATE QUERY].CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY]
GROUP BY Trim([MODULE] & " " & [EQUIPMENT LOCATION ROW LETTER] & " " &
[EQUIPMENT LOCATION ROW #]), [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY
DATE QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER], [HEAT LOAD BY DATE QUERY].SECTION, [HEAT LOAD BY DATE
QUERY].CELL
HAVING ((([HEAT LOAD BY DATE QUERY].CELL)<>"ss"))
ORDER BY [HEAT LOAD BY DATE QUERY].MODULE, [HEAT LOAD BY DATE
QUERY].[EQUIPMENT LOCATION ROW #], [HEAT LOAD BY DATE QUERY].[EQUIPMENT
LOCATION ROW LETTER];
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Justin:

I think the problem arises from the fact that there are queries based on
queries. My inclination would be to go back to the original tables and write
a query based on those. However, I don't have the necessary information on
your tables to do this so lets go back to the queries in your first post as
these are both based on the HEAT LOAD BY DATE QUERY and see if we can come up
with a single query based on that query. Essentially what we want is to
return all rows, unrestricted by date, with the columns defined by your
original queries, where the floor location's match those returned by the date
restricted query, so if we start with the second query it’s a question of
restricting this to the floor locations which match the first query's
returned floor locations. As the floor location column is a computed column
made up from the concatenation of a number of columns a subquery correlated
on the outer query by each of those columns, and which is restricted by the
date parameters, should enable us to restrict the outer query. So taking
your original second query and incorporating a subquery into it gives us:

PARAMETERS
[ENTER THE START DATE ] DATETIME,
[ENTER THE ENDING DATE ] DATETIME;
SELECT
Trim(Q1.MODULE & " " & Q1.[EQUIPMENT LOCATION ROW LETTER] & " " &
Q1.[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION],
Sum(Q1.AMPS) AS SumOfAMPS,
Sum(Q1.WATTS) AS SumOfWATTS,
Sum(Q1.CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY] AS Q1
WHERE Q1.CELL<>"ss"
AND EXISTS
(SELECT *
FROM [HEAT LOAD BY DATE QUERY] AS Q2
WHERE Q2.MODULE = Q1.MODULE
AND Q2.[EQUIPMENT LOCATION ROW LETTER]
= Q1.[EQUIPMENT LOCATION ROW LETTER]
AND Q2.[EQUIPMENT LOCATION ROW #]
= Q1.[EQUIPMENT LOCATION ROW #]
AND [DATE ENTERED] >= [ENTER THE START DATE ]
AND [DATE ENTERED] < [ENTER THE ENDING DATE]+1)
GROUP BY
Trim(Q1.MODULE & " " & Q1.[EQUIPMENT LOCATION ROW LETTER] & " " &
Q1.[EQUIPMENT LOCATION ROW #]),
Q1.MODULE,
Q1.[EQUIPMENT LOCATION ROW #],
Q1.[EQUIPMENT LOCATION ROW LETTER],
Q1.SECTION,
Q1.CELL
ORDER BY
Q1.MODULE,
Q1.[EQUIPMENT LOCATION ROW #],
Q1.[EQUIPMENT LOCATION ROW LETTER];

Note how the two instances of the HEAT LOAD BY DATE QUERY are distinguished
by the use of the aliases Q1 and Q2 so that the outer query and the subquery
can be correlated. Note also the different method of defining the date
range. This is more reliable than a BETWEEN….AND operation as it takes
account of the possibility that the date/time values in the table might
inadvertently include unseen times of day (this happens sometimes, usually
because of the inappropriate use of the Now() function as a default date
value). Finally note that the parameters are declared. This is prudent with
date/time data as a parameter entered in short date format can otherwise be
interpreted by Access as an arithmetic expression rather than a date, and
give the wrong results.

Incidentally I notice that you have some columns, MODULE, SECTION and CELL
in the GROUP BY clause which are not in the SELECT clause, and that you
include the EQUIPMENT LOCATION ROW LETTER and EQUIPMENT LOCATION ROW #
columns in the GROUP BY clause independently as being part of the expression
which computes the FLOOR LOCATION column. This seems rather unusual to me.
Normally one would include in the GROUP BY clause only those columns which
are not aggregated in the SELECT clause (or an expression which computes such
a column as with FLOOR LOCATION).

Ken Sheridan
Stafford, England

JUSTIN HOBBS said:
Ken,

I tried the the SQL in your last post but it gives an error stating Circular
reference caused by HEAT LOAD REPORT QUERY. Is there something that i missed
in adapting this? I thought that all i needed to do is add the actual query
names. Also your current description of what i need to do seems to be
correct. Sorry about all of the trouble but I am not good with the SQL, I
included it because it seems that most posts include the SQL of there problem
and thought that it would help describe the problem. I appreciate any
information that you can provide. Here is what i did to your last post to
adapt it.

SELECT [HEAT LOAD REPORT QUERY].*
FROM [HEAT LOAD REPORT QUERY] INNER JOIN [HEAT LOAD BY DATE QUERY TWO]
ON [HEAT LOAD REPORT QUERY].[FLOOR LOCATION] = [HEAT LOAD BY DATE QUERY
TWO].[ FLOOR LOCATION];
Click to expand...
 
J

JUSTIN HOBBS

The new SQL when ran ask for the ending date twice. If I enter dates on the
Start date and the fist paramater for end date It pulls up the correct fields
but they do not contain any information. If I enter information in the last
end date paramater it says that:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a nemeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

I am unsure on the aliases that you provided "Q1" and "Q2" are these to be
replaced by the actual Query name or not.

As far as the extra columns that were in the query I would not know if it is
unusual or not. I have done very little with the SQL format of Access, most
everything I have done in design view.


--
Thanks for any assistance that you can provide.

Justin


Ken Sheridan said:
Justin:

I think the problem arises from the fact that there are queries based on
queries. My inclination would be to go back to the original tables and write
a query based on those. However, I don't have the necessary information on
your tables to do this so lets go back to the queries in your first post as
these are both based on the HEAT LOAD BY DATE QUERY and see if we can come up
with a single query based on that query. Essentially what we want is to
return all rows, unrestricted by date, with the columns defined by your
original queries, where the floor location's match those returned by the date
restricted query, so if we start with the second query it’s a question of
restricting this to the floor locations which match the first query's
returned floor locations. As the floor location column is a computed column
made up from the concatenation of a number of columns a subquery correlated
on the outer query by each of those columns, and which is restricted by the
date parameters, should enable us to restrict the outer query. So taking
your original second query and incorporating a subquery into it gives us:

PARAMETERS
[ENTER THE START DATE ] DATETIME,
[ENTER THE ENDING DATE ] DATETIME;
SELECT
Trim(Q1.MODULE & " " & Q1.[EQUIPMENT LOCATION ROW LETTER] & " " &
Q1.[EQUIPMENT LOCATION ROW #]) AS [FLOOR LOCATION],
Sum(Q1.AMPS) AS SumOfAMPS,
Sum(Q1.WATTS) AS SumOfWATTS,
Sum(Q1.CFM) AS SumOfCFM
FROM [HEAT LOAD BY DATE QUERY] AS Q1
WHERE Q1.CELL<>"ss"
AND EXISTS
(SELECT *
FROM [HEAT LOAD BY DATE QUERY] AS Q2
WHERE Q2.MODULE = Q1.MODULE
AND Q2.[EQUIPMENT LOCATION ROW LETTER]
= Q1.[EQUIPMENT LOCATION ROW LETTER]
AND Q2.[EQUIPMENT LOCATION ROW #]
= Q1.[EQUIPMENT LOCATION ROW #]
AND [DATE ENTERED] >= [ENTER THE START DATE ]
AND [DATE ENTERED] < [ENTER THE ENDING DATE]+1)
GROUP BY
Trim(Q1.MODULE & " " & Q1.[EQUIPMENT LOCATION ROW LETTER] & " " &
Q1.[EQUIPMENT LOCATION ROW #]),
Q1.MODULE,
Q1.[EQUIPMENT LOCATION ROW #],
Q1.[EQUIPMENT LOCATION ROW LETTER],
Q1.SECTION,
Q1.CELL
ORDER BY
Q1.MODULE,
Q1.[EQUIPMENT LOCATION ROW #],
Q1.[EQUIPMENT LOCATION ROW LETTER];

Note how the two instances of the HEAT LOAD BY DATE QUERY are distinguished
by the use of the aliases Q1 and Q2 so that the outer query and the subquery
can be correlated. Note also the different method of defining the date
range. This is more reliable than a BETWEEN….AND operation as it takes
account of the possibility that the date/time values in the table might
inadvertently include unseen times of day (this happens sometimes, usually
because of the inappropriate use of the Now() function as a default date
value). Finally note that the parameters are declared. This is prudent with
date/time data as a parameter entered in short date format can otherwise be
interpreted by Access as an arithmetic expression rather than a date, and
give the wrong results.

Incidentally I notice that you have some columns, MODULE, SECTION and CELL
in the GROUP BY clause which are not in the SELECT clause, and that you
include the EQUIPMENT LOCATION ROW LETTER and EQUIPMENT LOCATION ROW #
columns in the GROUP BY clause independently as being part of the expression
which computes the FLOOR LOCATION column. This seems rather unusual to me.
Normally one would include in the GROUP BY clause only those columns which
are not aggregated in the SELECT clause (or an expression which computes such
a column as with FLOOR LOCATION).

Ken Sheridan
Stafford, England

JUSTIN HOBBS said:
Ken,

I tried the the SQL in your last post but it gives an error stating Circular
reference caused by HEAT LOAD REPORT QUERY. Is there something that i missed
in adapting this? I thought that all i needed to do is add the actual query
names. Also your current description of what i need to do seems to be
correct. Sorry about all of the trouble but I am not good with the SQL, I
included it because it seems that most posts include the SQL of there problem
and thought that it would help describe the problem. I appreciate any
information that you can provide. Here is what i did to your last post to
adapt it.

SELECT [HEAT LOAD REPORT QUERY].*
FROM [HEAT LOAD REPORT QUERY] INNER JOIN [HEAT LOAD BY DATE QUERY TWO]
ON [HEAT LOAD REPORT QUERY].[FLOOR LOCATION] = [HEAT LOAD BY DATE QUERY
TWO].[ FLOOR LOCATION];
Click to expand...
Click to expand...
 
K

Ken Sheridan

Justin:

I think you will have to go back to the base tables and write a query based
on them. It sounds like there's too much complexity building up in the
'tree' of queries.

Ken Sheridan
Stafford, England
 

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

use a column of one query as criteria in a second query 1
using selection from calendar control as the criteria for a query 1
Using the "Forms Object" on more than field in a query table 2
Nested Do...Loops and Date Cell Defined as Date in VBA code 4
Values from textbox in criteria of query for report 1
NOT criteria in query not working 5
Perform a query using a field in a form as a criteria 12
optimized way to make a report? 14

Top