FIFO in one table 1

[EZLI]

Hi,
I have a one table T1:

ID 1
Date 01.07.04
Field1 A
Field2 B

How to assign Field2 value "B" to Field1 for the next record ?
Next records must look so:

ID 2
Date 01.08.04
Field1 B
Field2 C

ID 3
Date 01.09.04
Field1 C
Field2 D
e.t.c
Thank's in advance for help.

Bob

 

[SkipVought]

Bob,

Is this a mass update? How many records will be added? What happens next?
[tt]
ID: 25
Date:
Field1: Y
Field2: Z
[/tt]


Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[EZLI]

Skip,
Thank's for help.

The value from current record Field2 must be assigned (copied) to Field1 for the next record.This operation for each next record.
And we can use only one table.
The amount of records will be grow till 1000.

I'm novice in VBA.
Please help dissolve my question.

Bob

 

[PHV]

What is the proven way to know which is the next (or previous) record ? ID=ID +/- 1 ? Date = Date +/- 1 day ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

What happens next?
[tt]
ID: 25
Date: 7/1/2006
Field1: Y
Field2: Z
[/tt]

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[KenReay]

Hi

Are you trying to build a linked list, where each record in the list points to the preceding record and next record?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[EZLI]

Ken,
Thank's for help.
Please give example for that simple table.

Regards,

Bob

 

[KenReay]

Hi

Is that a yes or a no?

If it is a yes, why do you need values A,B,C etc

I would have thought it would look so:

Id Description Prev Next
1 Record 1 0 3
3 Record 2 1 4
4 Record 3 3 5
5 Record 4 4 0

is that what you are trying to do?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[EZLI]

Ken,
Here is the table:

ID Field1 Field2
1 5 10
2 10 27
3 27 98
4 98 150

Bob

 

[KenReay]

Yes, that is something like I expected, but WHERE do the values 5,10,27,98,150 come from?, if you add a new record how do you know what the values of Field1 and Field2 should be?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Forgot to say, I am in UK, and it is 19:30 here, I am going to watch TV, so I will be back onlline in about 12 hours time, so I have not lost interest, OK?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[EZLI]

Hi,
Value in Field2 I enter manualy.
Date-month.
Field1-Counter messure for last month.
Field2-Counter messure for this month.
Field3-Diff=[Field2]-[Field1].

ID Date Field1 Field2 Diff
1 01.07.04 100 200 100
2 01.08.04 200 350 150

Regards,

Bob

 

[KenReay]

Hi

Ok

Answer is do not do it that way, you are storing the same data twice a definate no-no in relational database design, it will lead to problems of data integrity problems in the even of an amendment to a given months figure.

simplify your table to

ID Date Field1

and use a query to produce the

ID Date Field1 Field2 Diff





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

I would need to think a bit more to get exactly correct SQL, but it would be something like:

SELECT A.Id, A.MEDate,A.MonthlyValue,B.MonthlyValue As LastMonth, A.MonthlyValue-B.MonthlyValue As Diff
FROM tblTest A Inner Join tblTest B ON Month(B.[MEDate]) = Month(A.[MEDate])-1;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

OK, the Mark2 SQL which works for data spanning a calendar year is

SELECT A.Id, A.MEDate, A.MonthlyValue, Nz(B.MonthlyValue,0) AS LastMonth, A.MonthlyValue-Nz(B.MonthlyValue,0) AS Diff
FROM tblTest AS A Left JOIN tblTest AS B ON Month(B.MEDate)=Month(DateAdd("m",-1,A.MEDate)) AND Year(B.MEDate)=Year(DateAdd("m",-1,A.MEDate)) ORDER BY A.MEDate ;

I can send you a sample Access2000 mdb with table and sample data if you want, in which case send your EMail address to me at kenneth dot reay at talk21 dot com

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK