If previous record field is same as previous, do n+1

[sparkes84]

If current record field is same as previous, do n+1

Hi all,

I haven't been on here for AGES! Do apologise if I don't use the correct formats, but I'll try!

I should note that I am not good at VBA !

Basically, I need a way in Access of looking up the SumofCount of a previous record where the Selector is the same. Then, I need to take the difference away between them, as the site will have been paid for any previous recruits (SumofCount). If you know a better way of doing this, then I'm all ears but please, I'm not good at VBA at all, so you'd have to spoon feed me!

My logic of what is required is this:
1. Where Selector is the first instance for each particular number, I would like a new column with 0 for those lines.
2. Where Selector is the same as the previous record, I would like it to put a 1 in the first instance like this and then increment, e.g. 2, 3, 4 for the following 3 records. (The order of the query should be correct as it is sorted by 'Order').
3. Then, I would like to somehow (??) change the Previous CountofSum column so that it only looks up the previous SumOfCount where the Selector of the current records is the same as the previous record.

[Please see attachment as I cannot work out how to add a query view from Access]

I hope that makes sense?

Selector is made up of a study ID plus site ID.
Order is the same as Selector but also has year and month (e.g. 1212 or 1301, for 2012 December or 2013 January, respectively)


Many thanks, and oh, Happy New Year!
sparkes84

 

[plog]

This shouldn't require any VBA, it could be done in a query, if your tables are properly structured. Which I don't think they are. Get to that in a moment.

First, what you want to do is predicated on your data having some sort of order (i.e. First, next, previous). But I don't see a field on which you can create order.

Most likely the order is based on that date information you've got encoded into the Order field. The big question is, how did it get there? Is the date portion coming from two unique fields from an underlying table? Or is Order and actual field in your table?

If its an actual field in a table, it shouldn't be, this is not a properly structured table and will prevent you from doing what you want.

Is 'Order' a field in your table?

 

[sparkes84]

Hi Plog,

Thanks for taking the time to reply. 'Order' is made up of three original table fields, then joined via a Make-table query, e.g.

Order: Val([Order1] & [Order2] & [Order3])

where Order1 = StudyID
Order 2 = SiteID
Order 3 = DateStamp

- DateStamp is from: Date_Stamp: Val(Format(Now(),"yymm"))

Is there any hope?

Thanks,
Sally

 

[plog]

then joined via a Make-table query,

Ugh, why? What's wrong with a SELECT query?

Can you post sample data from your table along with field and table names? Also post what you would like the output of the query to be based on that sample data.

 

[sparkes84]

Thanks. I have to log out in a minute. If it is easier, I could attach a copy of the database with info anonymised? I'll attach it tomorrow, if you're agreeable?

Thanks,
sparkes84

 

[plog]

Sure. But beware, I'm anal about proper table structure/layout--I'll have to comment on any errors I see. Its just how I was raised.

 

[sparkes84]

OK, here are two documents within one zip. Re-the db (only 1 table will be imported from Excel, the others have been created in my attempt......!)

Word doc shows example output.

All yours to change things. I'm no whizz, by any means.

Thanks for your help,
sparkes84

 

[plog]

Its not possible to produce the results you sent based on the data you provided: every single date stamp is the same in the data (1301) but you have multiple date stamp values in your expected results (1301, 1302..)

You need to give me an accurate target to aim for.

 

[sparkes84]

Sorry Plog - was in a rush and didn't remember to (force) change the date stamp. I have done so now (in the Recrt_Data_DateStamped and Recrt_Data_DateStamped1). New zip file attached.

Apologies,
sparkes84

 

[plog]

Again, I don't think the data you sent is capable of generating the results you sent. Specifically, the second record of the results. In the table 'Recrt_Data_DateStamped' there is no record that has a Selector value of 6521241 and Order Value of 1302.

It is not possible to generate your results with the data you provided. I'm done trying to help specifically, but I will help you generally:

You basically are trying to do a sort of Running Total which means you need to identify at least 2 fields in your data--one is the field you want to group on (probably Selector, it actually may be more than one field)the other is an order field (most likely the Order3 field). From there you build a query on your data, then you use the DSUM function (http://www.techonthenet.com/access/functions/domain/dsum.php) and in the criteria area you make sure that it looks at only records with the same grouping field(s) and has an order field less than the record you are currently on.

If you need additional help, trying searching this site for 'Running Total query'