Repeat data (bottom up) when criteria from another field changes (1 Viewer)

Hello, I an new to this forum. I need help with the following.


$ 1 1
$ 2 2
$ 3 3
0 0 4
0 0 5
0 0 6
0 0 7
$ 8 8
$ 9 9
$ 10 10
$ 11 11
$ 12 12
$ 13 13
$ 14 14
$ 15 15
$ 16 16
$ 17 17
0 0 18
0 0 19
0 0 20
$ 21 21




I need the 21 to repeat bottom up (in another column) if the $ sign is blank. It's the same for the 11 and the 8. This is easy enough in Excel but we want to use Access. (The information here is the result of several back-to back queries.) Had some ideas using Dmax and Dcount but have to limit the range. Anyone have any ideas?


This information is from a table. I tried copying it into this post, but it didn't format right. It's supposed to be 3 columns, and the zeroes should be blanks.

Welcome aboard
I'm pretty good with patterns but I just don't see this one. What is unique about the 8 and 11 that they need special handling?

I'll make a generalization though. If you are having to duplicate data by copying it, your schema is incorrect. Access tables may look like spreadsheets but a relational database is quite different from a flat file such as Excel. I'm going to guess that one or more of the columns need to move to a different table where their value can occur only once and then dependent records in a separate table "complete" the pattern.

If you post your schema and tell us a little about it, we'll be happy to help.

Pat, thanks for your input. The information in this table will be the basis for summing other fields not shown. This table is the product of several back-to-back Access queries and sorts. If we can figure out a function for the issue we have, it will sum correctly. (I've already tested it with a paste.)

The 3rd column is an autonumber field. The 2nd column references the 3rd column whenever it sees a $ in the first column otherwise it creates a blank. (The zero is there because it didn't copy correctly.) In another column we need a function that will copy the following (not preceding) record (8,11,21) when it sees a blank. Once we get that, then we can sum these records like we want.

We know that Access is not a spreadsheet program, and that this works with Excel, but we want to use Access for this program. Can you or someone help us with this?


Thanks

I neglected to mention that I've tried Dcount,Dsum,Dmax,Dlookup, and Count and combinations of these with limited success. I feel the answer is very close, but still needs some work.

is this information coming from a query, or a table?

access is different to excel. you are able to manipulate data in special ways in excel to get stuff to work. you can't do this directly in access. in access what you are really donig is establishing a set of rules that lead to the data you want being extracted from your information

to get this to work in access, you probably need to rethink your data structure

ie -as far as this exercise goes, the $ sign is confusing. is this indicating some rows are $ and others are a different currency? that is the sort of way to go.

OK, column 3 is an autonumber. Column 2 is calculated based on column 1 and column 3. Where does column 1 come from? And, unless it is a deep, dark, secret what exactly are we trying to accomplish? Your description is so focused on what you want to code that your explaination told us nothing helpful.

In another column we need a function that will copy the following (not preceding) record (8,11,21) when it sees a blank.

Click to expand...

8 and 11 look the "same". 21 is clearly different. Why is it in the same set? Or, if you look at the preceeding records, 8 and 21 look the "same" and 11 is odd man out.

In any event, SQL DML (data manipulation language) is not a procedural language and isn't capable of doing sequential processing. If you want to process a recordset and do something based on the preceeding or following record, you'll need to code it in VBA. You'll need to open a RecordSet based on a query that sorts by the autonumber to maintain the given sequance.

Pat, this standard labor incentive table. The $ signs which are embedded in the task indicate an end point of the operation and it is a key to add the previous records. The easiest way (I thought) to do this was bottom up totaling. I didn't really want to show these in this example (because of confidentiality), but I'll make up some data to demonstrate.

Oper Descr Oper # Time End Data Ind End Data# Auto# Add Ind
Get widget 128545444 .010 4 8
Grind widget 128555444 .015 5 8
Paint widget 128565444 .017 6 8
Inspect widget 128575444 .012 7 8
Pack widget 128585444$ .014 $ 8 8 8



Once we get it to this point, it's easy to run a query and pick the Pack widget operation. It's the end point, and the 8's will allow us to total the entire time of .069, the total standard for the entire operation.

Also, I didn't notice but 21 should be listed in the 2nd column also instead of 0. It's an end point.

I looked at running total options, but you still have to limit the range of data that you're looking at, so we're still in the same boat.

I hope this is helpful.



Thanks

If the 8's are a group identifier, why do you care about the $? A query would sum each group without convoluted logic.