Query error help

G

Guest

Hello, I have a query that I want to use for a graph in a report but I cant
get the query right. I want to Sum FOOTAGE and / by TotalUT and then * by 8
hours. What am I doing wrong? Here is the SQL... Thanks!

SELECT tblMain.[EMPLOYEE NAME], [EMPLOYEE TIME]-[TotalDT] AS TotalUT,
Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME], [DT Reason 1]+[DT
MAINTENANCE]+[DT Reason 2] AS TotalDT, Sum(tblMain.[DT Reason 1]) AS [SumOfDT
Reason 1], Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2], Sum(tblMain.[DT
MAINTENANCE]) AS [SumOfDT MAINTENANCE], Sum([FOOTAGE])/(Sum([TotalUT])*8) AS
Average
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) Between [Start Date: (mm/dd/yy)] And [Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];
 
G

Guest

You are creating aliases ( [TotalDT] and [TotalUT] ) and then using them in
the same query.
You can use them in follow-on queries but not in the same query. You must
use the full calculation.
 
G

Guest

Not sure I understand what you mean? I only have the show boxes checked on
employee name and Average but when I run the query its asking me for total UT
then the date range. Im new to access so can you explain on how I can fix
this? Thanks a million!!!

KARL DEWEY said:
You are creating aliases ( [TotalDT] and [TotalUT] ) and then using them in
the same query.
You can use them in follow-on queries but not in the same query. You must
use the full calculation.
--
KARL DEWEY
Build a little - Test a little


Chad said:
Hello, I have a query that I want to use for a graph in a report but I cant
get the query right. I want to Sum FOOTAGE and / by TotalUT and then * by 8
hours. What am I doing wrong? Here is the SQL... Thanks!

SELECT tblMain.[EMPLOYEE NAME], [EMPLOYEE TIME]-[TotalDT] AS TotalUT,
Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME], [DT Reason 1]+[DT
MAINTENANCE]+[DT Reason 2] AS TotalDT, Sum(tblMain.[DT Reason 1]) AS [SumOfDT
Reason 1], Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2], Sum(tblMain.[DT
MAINTENANCE]) AS [SumOfDT MAINTENANCE], Sum([FOOTAGE])/(Sum([TotalUT])*8) AS
Average
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) Between [Start Date: (mm/dd/yy)] And [Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];
Click to expand...
Click to expand...
 
G

Guest

when I run the query its asking me for total UT then the date range.
As I said you did a calculation to get [TotalUT] and then used that alias in
the same query. You must use the calculation and not the alias.

You told the query to prommt for [Start Date: (mm/dd/yy)] and [Stop
Date: (mm/dd/yy)] in your query.

--
KARL DEWEY
Build a little - Test a little


Chad said:
Not sure I understand what you mean? I only have the show boxes checked on
employee name and Average but when I run the query its asking me for total UT
then the date range. Im new to access so can you explain on how I can fix
this? Thanks a million!!!

KARL DEWEY said:
You are creating aliases ( [TotalDT] and [TotalUT] ) and then using them in
the same query.
You can use them in follow-on queries but not in the same query. You must
use the full calculation.
--
KARL DEWEY
Build a little - Test a little


Chad said:
Hello, I have a query that I want to use for a graph in a report but I cant
get the query right. I want to Sum FOOTAGE and / by TotalUT and then * by 8
hours. What am I doing wrong? Here is the SQL... Thanks!

SELECT tblMain.[EMPLOYEE NAME], [EMPLOYEE TIME]-[TotalDT] AS TotalUT,
Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME], [DT Reason 1]+[DT
MAINTENANCE]+[DT Reason 2] AS TotalDT, Sum(tblMain.[DT Reason 1]) AS [SumOfDT
Reason 1], Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2], Sum(tblMain.[DT
MAINTENANCE]) AS [SumOfDT MAINTENANCE], Sum([FOOTAGE])/(Sum([TotalUT])*8) AS
Average
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) Between [Start Date: (mm/dd/yy)] And [Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

WOW Chris thanks for the explamations and such!! I got it to work though I
was going about it wrong.. Here is the answer to my question if interested.

SELECT Sum([FOOTAGE])/Sum([EMPLOYEE TIME]-([DT Reason 1]+[DT Reason 2]+[DT
MAINTENANCE]))*8 AS Test, tblMain.[EMPLOYEE NAME]
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) Between [Enter Week Beginning: (mm/dd/yy)] And
[Enter Week Ending: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];


Chris2 said:
Chad said:
Hello, I have a query that I want to use for a graph in a report but I cant
get the query right. I want to Sum FOOTAGE and / by TotalUT and then * by 8
hours. What am I doing wrong? Here is the SQL... Thanks!

SELECT tblMain.[EMPLOYEE NAME], [EMPLOYEE TIME]-[TotalDT] AS TotalUT,
Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME], [DT Reason 1]+[DT
MAINTENANCE]+[DT Reason 2] AS TotalDT, Sum(tblMain.[DT Reason 1]) AS [SumOfDT
Reason 1], Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2], Sum(tblMain.[DT
MAINTENANCE]) AS [SumOfDT MAINTENANCE],
Click to expand...
Sum([FOOTAGE])/(Sum([TotalUT])*8) AS
Average
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) Between [Start Date: (mm/dd/yy)] And [Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];
Click to expand...

Chad,

You have said your can't get the query right, but you haven't said
what is actually going wrong.

You have not shared your database schema.

The following is strictly my best guess and may be totally wrong.

Here's your query straightened up.

SELECT tblMain.[EMPLOYEE NAME]
,[EMPLOYEE TIME]-[TotalDT] AS TotalUT
,Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME]
,[DT Reason 1]+[DT MAINTENANCE]+[DT Reason 2] AS TotalDT
,Sum(tblMain.[DT Reason 1]) AS [SumOfDT Reason 1]
,Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2]
,Sum(tblMain.[DT MAINTENANCE]) AS [SumOfDT MAINTENANCE]
,Sum([FOOTAGE])/(Sum([TotalUT])*8) AS Average
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) BETWEEN
[Start Date: (mm/dd/yy)] And [Stop Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];


Let's break it down:

SELECT tblMain.[EMPLOYEE NAME]
,[EMPLOYEE TIME]-[TotalDT] AS TotalUT

Right here, you subtract [TotalDT] from [EMPLOYEE TIME].

TotalDT gets created further below as a column alias. You can't refer
to TotalDT here.

You're also declaring that the result of this expression will be a
column alias named TotalUT.


,Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME]
,[DT Reason 1]+[DT MAINTENANCE]+[DT Reason 2] AS TotalDT

Right here, you're declaring that the result of this express will be a
column alias named TotalDT.


,Sum(tblMain.[DT Reason 1]) AS [SumOfDT Reason 1]
,Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2]
,Sum(tblMain.[DT MAINTENANCE]) AS [SumOfDT MAINTENANCE]
,Sum([FOOTAGE])/(Sum([TotalUT])*8) AS Average

TotalUT gets created further above as a column alias. You can't refer
to TotalUT here.

FROM tblMain
WHERE (((tblMain.[DAYS DATE]) BETWEEN
[Start Date: (mm/dd/yy)] And [Stop Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];



Other Comments.

If you have control of your database schema, consider removing all
spaces (and any special characters) from every table, query, form,
report, macro, and/or module name. To start with, it will get rid of
all those hideous [] characters.

If you have control of your database schema, consider table names that
describe the entity in question. "tblMain" describes nothing in
particular.

If you have control of your own query design, consider using table
aliases in all queries. They will clarify all of your queries. Many
queries are not possible without using table aliases (all types of
correlated subqueries are impossible without table aliases).


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

Chad said:
Hello, I have a query that I want to use for a graph in a report but I cant
get the query right. I want to Sum FOOTAGE and / by TotalUT and then * by 8
hours. What am I doing wrong? Here is the SQL... Thanks!

SELECT tblMain.[EMPLOYEE NAME], [EMPLOYEE TIME]-[TotalDT] AS TotalUT,
Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME], [DT Reason 1]+[DT
MAINTENANCE]+[DT Reason 2] AS TotalDT, Sum(tblMain.[DT Reason 1]) AS [SumOfDT
Reason 1], Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2], Sum(tblMain.[DT
MAINTENANCE]) AS [SumOfDT MAINTENANCE],
Click to expand...
Sum([FOOTAGE])/(Sum([TotalUT])*8) AS
Average
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) Between [Start Date: (mm/dd/yy)] And [Stop
Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];
Click to expand...

Chad,

You have said your can't get the query right, but you haven't said
what is actually going wrong.

You have not shared your database schema.

The following is strictly my best guess and may be totally wrong.

Here's your query straightened up.

SELECT tblMain.[EMPLOYEE NAME]
,[EMPLOYEE TIME]-[TotalDT] AS TotalUT
,Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME]
,[DT Reason 1]+[DT MAINTENANCE]+[DT Reason 2] AS TotalDT
,Sum(tblMain.[DT Reason 1]) AS [SumOfDT Reason 1]
,Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2]
,Sum(tblMain.[DT MAINTENANCE]) AS [SumOfDT MAINTENANCE]
,Sum([FOOTAGE])/(Sum([TotalUT])*8) AS Average
FROM tblMain
WHERE (((tblMain.[DAYS DATE]) BETWEEN
[Start Date: (mm/dd/yy)] And [Stop Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];


Let's break it down:

SELECT tblMain.[EMPLOYEE NAME]
,[EMPLOYEE TIME]-[TotalDT] AS TotalUT

Right here, you subtract [TotalDT] from [EMPLOYEE TIME].

TotalDT gets created further below as a column alias. You can't refer
to TotalDT here.

You're also declaring that the result of this expression will be a
column alias named TotalUT.


,Sum(tblMain.[EMPLOYEE TIME]) AS [SumOfEMPLOYEE TIME]
,[DT Reason 1]+[DT MAINTENANCE]+[DT Reason 2] AS TotalDT

Right here, you're declaring that the result of this express will be a
column alias named TotalDT.


,Sum(tblMain.[DT Reason 1]) AS [SumOfDT Reason 1]
,Sum(tblMain.[DT Reason 2]) AS [SumOfDT Reason 2]
,Sum(tblMain.[DT MAINTENANCE]) AS [SumOfDT MAINTENANCE]
,Sum([FOOTAGE])/(Sum([TotalUT])*8) AS Average

TotalUT gets created further above as a column alias. You can't refer
to TotalUT here.

FROM tblMain
WHERE (((tblMain.[DAYS DATE]) BETWEEN
[Start Date: (mm/dd/yy)] And [Stop Date: (mm/dd/yy)]))
GROUP BY tblMain.[EMPLOYEE NAME];



Other Comments.

If you have control of your database schema, consider removing all
spaces (and any special characters) from every table, query, form,
report, macro, and/or module name. To start with, it will get rid of
all those hideous [] characters.

If you have control of your database schema, consider table names that
describe the entity in question. "tblMain" describes nothing in
particular.

If you have control of your own query design, consider using table
aliases in all queries. They will clarify all of your queries. Many
queries are not possible without using table aliases (all types of
correlated subqueries are impossible without table aliases).


Sincerely,

Chris O.
 
C

Chris2

Chad said:
WOW Chris thanks for the explamations and such!!
Click to expand...

You're welcome! :D

I got it to work though I
was going about it wrong.. Here is the answer to my question if
Click to expand...
interested.

I am happy to hear that you resolved your question.


Sincerely,

Chris O.
 

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

Percent help 3
Update query help 3
Query expression help 11
Query and text box help on formula 6
Need help with Report formula 6
Formula in text box on RPT help... 9
Combo Box on Parameter Form 4
building a query with variables 3

Top