First Non Zero of Value under a Field

[Wil2003]

Hi I have started using Access for 2 weeks and appreciate if someone can offer helps on my problem below.

Say, I have a table as below:

1. All the records are in numerics
2. The first three columns (Date, Time, Results) are given


3. The fourth column (New) is the column that I want to create
4. The fourth column should look at the value in "Results" and find the first non-zero value (ie, 1 or -1) and the fill the records below with this non-zero value found for a particular day.
5. For a new date, repeat the process, ie, find the first non-zero value for that date, and fill the records with that value for the rest of that date.

Thanks in advance for any help.

Date	Time	Results	New
20101001	1000
20101001	1100
20101001	1200	1	1
20101001	1300		1
20101001	1400	-1	1
20101001	1500	-1	1
20101002	1000
20101002	1100
20101002	1200
20101002	1300	-1	-1
20101002	1400	1	-1
20101002	1500		-1
20101002	1600	1	-1

[pbaldy]

You could use a recordset, opened on SQL that ordered by date and time. Within a loop of that recordset, use a variable to track when the date changes and another to test the results field. Use the .Edit method of the recordset to update records when appropriate.

[June7]

Set ORDER BY property in table design to: [Date], [Time].

Then try:

SELECT Table1.Date, Table1.Time, Table1.Results, Query2.MinOfTime, IIf([Time]>=[MinOfTime],[FirstOfResults],Null) AS New
FROM
(SELECT Query1.Date, Min(Query1.Time) AS MinOfTime, First(Query1.Results) AS FirstOfResults
FROM (SELECT Table1.Date, Table1.Time, Table1.Results FROM Table1 WHERE Not Table1.Results Is Null) AS Query1
GROUP BY Query1.Date) AS Query2 INNER JOIN Table1 ON Query2.Date = Table1.Date;


Date is a reserved word and should not use reserved words as names for anything.

[Wil2003]

Thanks very much Paul and June7. I guess I have to first get myself familiar with SQL. Will get back after trying.
Thanks again.

[pbaldy]

No problem and welcome to the site by the way!