Query Help Needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to creat the field "Condition". It needs to return "TRUE" if Code
in that record is the same as Code in record ID-1 (the preceding record),
otherwise return false.

Is this possible with a query ?

ID Duration Code Condition
1 C FALSE
2 0:00:21 A FALSE
3 0:00:04 A TRUE
4 0:00:09 B FALSE
 
Try something like

SELECT TableName.ID, TableName.Code,
IIf(nz(DLookUp("
Code: 
","[TableName]","[ID] = " &
Nz(DMax("[ID]","[TableName]","[ID] < " & [ID]),0)),0)=[Code],"True","False")
AS Condition
FROM TableName
 
Thank you so much Ofer.

Is it possible to add another "condition" (AND LOGIC) into the sql you
provided ?

For example,I would like to add :

if the value in field "Duration" is less than 00:00:05 (5 seconds) return
AND Code in that record is the same as Code in record ID-1 (the preceding
record), return TRUE, otherwise false.

Thanks again.
Ofer Cohen said:
Try something like

SELECT TableName.ID, TableName.Code,
IIf(nz(DLookUp("
Code: 
","[TableName]","[ID] = " &
Nz(DMax("[ID]","[TableName]","[ID] < " & [ID]),0)),0)=[Code],"True","False")
AS Condition
FROM TableName


--
Good Luck
BS"D


[QUOTE="carl"]
I am trying to creat the field "Condition". It needs to return "TRUE" if Code
in that record is the same as Code in record ID-1 (the preceding record),
otherwise return false.

Is this possible with a query ?

ID	Duration	Code	Condition
1		C	FALSE
2	0:00:21	A	FALSE
3	0:00:04	A	TRUE
4	0:00:09	B	FALSE
[/QUOTE][/QUOTE]
Click to expand...
 
What is the Duration field type?

--
Good Luck
BS"D


carl said:
Thank you so much Ofer.

Is it possible to add another "condition" (AND LOGIC) into the sql you
provided ?

For example,I would like to add :

if the value in field "Duration" is less than 00:00:05 (5 seconds) return
AND Code in that record is the same as Code in record ID-1 (the preceding
record), return TRUE, otherwise false.

Thanks again.
Ofer Cohen said:
Try something like

SELECT TableName.ID, TableName.Code,
IIf(nz(DLookUp("
Code: 
","[TableName]","[ID] = " &
Nz(DMax("[ID]","[TableName]","[ID] < " & [ID]),0)),0)=[Code],"True","False")
AS Condition
FROM TableName


--
Good Luck
BS"D


[QUOTE="carl"]
I am trying to creat the field "Condition". It needs to return "TRUE" if Code
in that record is the same as Code in record ID-1 (the preceding record),
otherwise return false.

Is this possible with a query ?

ID	Duration	Code	Condition
1		C	FALSE
2	0:00:21	A	FALSE
3	0:00:04	A	TRUE
4	0:00:09	B	FALSE
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
I think it is a number. It comes in as shown in the Duration Filed Below - I
am trying to find a way to convert it to New Format. Either way, I can live
with any format so long as I can query on 5 seconds. Would it help if I sent
you sample data ?

NewID NewTime Duration NewFormat
1 9:31:16
2 9:31:37 0.00024306 0:00:21
3 9:31:41 4.63E-05 0:00:04
4 9:31:50 0.00010417 0:00:09


Ofer Cohen said:
What is the Duration field type?

--
Good Luck
BS"D


carl said:
Thank you so much Ofer.

Is it possible to add another "condition" (AND LOGIC) into the sql you
provided ?

For example,I would like to add :

if the value in field "Duration" is less than 00:00:05 (5 seconds) return
AND Code in that record is the same as Code in record ID-1 (the preceding
record), return TRUE, otherwise false.

Thanks again.
Ofer Cohen said:
Try something like

SELECT TableName.ID, TableName.Code,
IIf(nz(DLookUp("
Code: 
","[TableName]","[ID] = " &
Nz(DMax("[ID]","[TableName]","[ID] < " & [ID]),0)),0)=[Code],"True","False")
AS Condition
FROM TableName


--
Good Luck
BS"D


:

I am trying to creat the field "Condition". It needs to return "TRUE" if Code
in that record is the same as Code in record ID-1 (the preceding record),
otherwise return false.

Is this possible with a query ?

ID	Duration	Code	Condition
1		C	FALSE
2	0:00:21	A	FALSE
3	0:00:04	A	TRUE
4	0:00:09	B	FALSE
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top