Need help with Query

  • Thread starter Thread starter MArkus
  • Start date Start date
M

MArkus

I have 2 tables TblCode and Tbl Spent Each table has a
column for each hour of the day. I want to run a query
that would total the time spent depending on the value of
the corresponding hour in TblCode.

Example:
TblCode
Rec# 0600 0700 0800 0900
1 E R E D

TBlSpent
Rec# 0600 0700 0800 0900
1 40 60 30 20

I want to run a query that looks for the value E in Tbl
Code and corresponds with the value in Tbl Spent for the
same hour. The result I would want from this example is

Rec# Spent
1 70

The record numbers are bound through a relationship from
another table TblTrac which contains
Rec# Name ID Status

Any help would be greatly appreciated.
 
I have 2 tables TblCode and Tbl Spent Each table has a
column for each hour of the day.
Click to expand...

In that case they are incorrectly normalized. Storing data - a time -
in a fieldname is going to be a real source of trouble. You should
have one RECORD per hour, not one FIELD per hour! I'd suggest having
tblSpent as:

tblSpent
Rec# Long Integer <linked to tblTrac>
SpentTime Date/Time <date and time of expenditure>
Code Text <linked to tblCodes, translting E and R and D>
I want to run a query
that would total the time spent depending on the value of
the corresponding hour in TblCode.

Example:
TblCode
Rec# 0600 0700 0800 0900
1 E R E D

TBlSpent
Rec# 0600 0700 0800 0900
1 40 60 30 20

I want to run a query that looks for the value E in Tbl
Code and corresponds with the value in Tbl Spent for the
same hour. The result I would want from this example is

Rec# Spent
1 70
Click to expand...

Much easier in a properly normalized table. In fact it would be a
monstrously complicated expression to do the conditional sum... well,
let's see:

SELECT [Rec#], (IIF([tblCode].[0600] = [Enter code:],
[tblSpent].[0600], 0) + IIF([tblCode].[0700] = [Enter code:],
[tblSpent].[0700], 0) + IIF <etc for all the times>) AS Spent
FROM tblCode INNER JOIN tblSpent ON tblCode.[Rec#] = tblSpent.[Rec#];

With a normalized table a simple Totals query would do it easily.
 
Back
Top