Help in query calculated field

  • Thread starter Thread starter Carlos
  • Start date Start date
C

Carlos

Hello

i have 3 tables

T1
DATA (datetime PK) all days of the year

T2
Emp (text PK)
Cost_1 (number) all days except holydays
Cost_2 (number) holiday days

T3
Date_holidays (datetime ) holiday dates

the calculate field it makes, if date T1 not equal holiday T3 return
T2.Cost_1 if equal T2.Cost_2.
But if have 10 dates in holydays table the query return back 10 times same
DATA
all with same result in calculated field, what i missing i want only 1 DATA

i try this query,

SELECT T1.DATA, IIf(T1.DATA<>T3.Date_holidays,T2.Cost_1,T2.Cost_2) AS
Payment, T2.Emp
FROM T1, T2, T3
WHERE Emp='Joe'

Thank's
 
i want only 1 DATA
For one thing you are not specifying what day.
Click to expand...

Thank's Karl for your replay,
what i mean is unique DATA days, the query return 10 DATA days equals

Thank's
 
Carlos,

Your tables are not joined. That is why you are getting the same row repeated 10 times. You need to join your tables together in
the query.

Now it looks like you can join T1 to T3 using T1.DATA and T3.Date_holidays, but it doesn't look like you can join T2 to either of
the other 2 using any of the fields in T2.

Let me ask you this: Does both T1 & T3 have only 1 field/column in each of them of data type "datetime"?

I don't think you can accomplish what you are trying to do with the way you have your tables/DB structured right now.

Please give us more information. Describe each table to us:

- All fields in each table and their data types
- What kind of information are you storing in each of these tables.

I hope this helps,

Conan Kelly
 
Thank's Conan
Let me ask you this: Does both T1 & T3 have only 1 field/column in each
of them of data type "datetime"?
Click to expand...
yes you right T1 & T3 have only 1 field, i solve by modifie T1 ,add 1 field
(yes/no) holiday_day and don't need T3
the calculate field stay:

SELECT T1.DATA, IIf(T1.holidays=0,T2.Cost_1,T2.Cost_2) AS Payment, T2.Emp
FROM T1, T2
WHERE Emp='Joe'

thank's one more time for show me way
 
Back
Top