Simple Query

[dwatt]

Hi
How would I write a query to answer the following scenaio

I have a field (A) with consecutive numbers 10,11,12,.. If I were to select 11 in field (A) I want the value 8 from field (C) in the first recordset where A = 10 to be placed in field (B) of the second recordset where A = 11


A B C
10 5 8
11 8 4
12 4 5

ie., IF A = 11 THEN B = C FROM A-1 (WHICH IS 8)

these are values in a subform

Than you for all responses

David

 

[ColinEssex]

Maybe its me, but I'm confused! and I suspect thats why you haven't had a reply yet

If field A contains 10,11,12 then it can't be a number field it has to be a text box.

Reckon we need a bit more info here perhaps

Col

 

[Mile-O]

Maybe its me, but I'm confused!

If field A contains 10,11,12 then it can't be a number field it has to be a text box.
Col

Lol, he said consecutively, so the records are consecutive

The actual question, however, doesn't make much sense at all.

 

[ColinEssex]

its still confusing though!

 

[Mile-O]

Where do the different recordset's come into play?

What is this: "IF A = 11 THEN B = C FROM A-1 (WHICH IS 8)" ???

 

[dwatt]

Okay your right I am confused too…

Field (A) represents a date namely the year…so it’s a number field (its not formatted as a date)

A B C
2002 5 8
2003 8 4
2004 4 5

OR

A B C
2002 B1 C1
2003 B2 C2
2004 B3 C3

When I work with each year I am only interested in the previous year to bring forward a value from C1 to B2 for 2003 to update a calculation for example.

Hope I have made myself clearer....

Regards

 

[neileg]

Hope I have made myself clearer....

Nope, at least three people confused now.

 

[Mile-O]

Are you sure its not an Excel spreadsheet you are using? Or should be using?

 

[Mile-O]

You mean a RUNNING SUM!

 

[dwatt]

No this is only a small part of my database. I thought this was a simple problem that could be easily resolved...aaaHHH

I thought I was looking for a query along the lines:

If A = year(Date()) Then Bn = Cn-1 Where A = year (Date())-1

Please somebody help. I have little of no query experience..

A B C
2002 B1 C1
2003 B2 C2
2004 B3 C3

 

[Mile-O]

We'll help but you'll have to stop being so vague about your process. I understand what you are wanting to do but it's not as simple as you first thought.

Detail the table.

• What fields are in the table?
• What data type are these fields?
• Are any of them realted to other tables?
• What is the purpose of all this?
• Are there any restrictions involved?

 

[Pat Hartman]

In a relational table, records have no inherent order so it makes no sense to have a value from one row be dependent on a value from another row. I'm not even going to go into the potential problems. This process is much better satisfied by Excel since that is a flat file where records have a fixed order.

Storing this data violates Third normal form. Calculating the data in a query is EXTREMELY expensive if you have a large table and it also requires that each row have a unique ID and that unique ID must be able to be used to order the records into the sequence you want. There is a very lengthy post by someone who was being helped by Jon K. Jon developed a very good running sum query. I realize that this is not what you are looking for, but the concept is the same and you should be able to adapt Jon's database to suit your purpose. Or, maybe Jon will even do it for you. Look for "running and sum" and posts by Jon K

If you only need to see a printed copy of this balance forward, you can easily do it in a report.