Complex Query - Many Changes

[pnpez]

Hello All,

I have a query attached that I wish someone could help me with. Unfortunately, I don't use VBA coding.
The first thing that needs to happen is whenever a NO."1" appears after a "0", then the NO. must change to "1" . This occurs for as long as ALL of the 3 fields (AE, Type, LFType) remain the same.

The 2nd thing is that I need to fill in an RS Amount based on the field " NO.", as follows:

1. If a "0" NO. appears then let's just say the RS Amount will be another field, and I used a "$0" for purposes of this sample.
2. If a "1" NO. appears then I need to sum all prior P Amounts within the 3 field criteria I described above.
3. if another NO."1" appears in the very next record and ongoing records for the same 3 fields, then the running sum of NO. greater than "1" will cause the RS Amount to equal the P Amount for that record.

I'm sorry that you have to open the attachment, but I wasn't able to put the file right in this post. I did the best I could to explain the numbers in the attached file.

Thanks, Paul

 

[Guus2005]

You question doesn't make any sense to me. Because the picture shows fields marked in red which is very confusing. Why is that?
"If a 1 appears after a 0" is not very clear either.

Please clarify.

 

[vbaInet]

I see what you're trying to do. Are the records definitely going to be sorted by ID?

It would be much easier to do in a report but it can also be done in a query.

 

[pnpez]

Sorry about the confusion. ID is the primary key. The records will be sorted by the 3 fields, (AE, Type, LFType).

The cells in red are just indicating that these are the ones changing from what was in the "Current NO." column, nothing more.

 

[vbaInet]

You will need to do this in a report. Are you happy with that?