Re: Update fields in previous row from fields in current row

Hello Graham.

I'd like to try to explain what i want to do using the data at the end of
this post.

The data is in a csv file and imported into a recordset. I don't have any
need to update the data source or sort any data. It is simply read into the
recordset. There is no query in the sense that i'm extracting a set of
records from the source. I'm using all the records in the ascii, text, csv
file.

If you look at the data , you can see that the first row has "00050-00-0" as
the first field. The fourth row has "00050-00-0" as the first field. This
constitutes a group of rows in the recordset, four rows.

What i want to do to the recordset is take the "000048815.40" from row four
and put it into the first row after the first comma after the "37.00". So,
after that copy/move, the first row would look like this:

"00050-00-0",,"27202475",037.00,000048815.40,,,Y,Y,N,Y,N

There are other fields i want to move/copy also, but that's not important
here, since the idea here is to get the logic down.

Once the move/copy is done, i want to delete the fourth row then go onto to
the row that has "00050-81-7" and do the same thing. As you can see, that
group consists of only two rows.

I hope this helps you understand what i want to accomplish with the
recordset.

I'm sorry, but the comments you are making just don't seem to apply to what
i need. I hope this does not offend you in any way. I'm just having a hard
time trying to figure out how your comments apply to my problem.

Thanks for all of your help so far.
Tony

"00050-00-0",,"27202475",037.00,,,,Y,Y,N,Y,N
,,"27202490",037.00,,,,Y,Y,N,Y,N
,,"27206490",035.00,,,,Y,Y,N,Y,N
"00050-00-0","FORMALDEHYDE",,,000048815.40 ,000002599 ,00027,,,,,
"00050-81-7",,"12550055",100.00,,,,N,Y,N,N,N
"00050-81-7","L-ASCORBIC ACID",,,000000275.65 ,000000016 ,00027,,,,,
"00056-81-5",,"29950551",100.00,,,,N,Y,N,N,N
,,"56567421",100.00,,,,N,Y,N,N,N
"00056-81-5","GLYCERIN",,,000007163.00 ,000000256 ,00027,,,,,

"argusy" <argusy@xxxxxxxxxxxxxxx> wrote in message
news:13q22cej9nr4cad@xxxxxxxxxxxxxxxxxxxxx
inline, Tony

Tony Girgenti wrote:
Hello Graham.

I'm having a hard time trying to figure out what your code is doing. I
didn't try it in my program, but i do have some observations about your
comments.

LOL. seeing stepping back a record, updating, then moving forward again is
an
experience thing I can see in my mind

Tony, what you need to do is pick on just one record in the group to
HAVE a
total. That way you don't need to add records, and only works if all
records in a group have the total cleared each time your program is
run.<<

I'm not trying to add records. If anything, i want to delete the
existing totals records after i move their data fields to the first item
in the group.

I just re-read a few of your responses

I'm assuming you've got data like

item group cost qty grptotal

camera optical 150 20 ' data record
lens optical 100 0 ' data record
binoculars optical 100 4 ' data record
optical 4000 ' totals record (sum of
cost*qty?)
PC computer 1000 5 ' data
computer 5000 ' totals

You want to get rid of the 'totals' records, and put the total for the
group into the first (or last) group record.


In some other area of your program, I guess you're after totals on the
data.
Just set a query to pick records that have the total greater than zero.
Already grouped, too, and you don't need the DISTINCT query modifier,
either.<<

I'm not after totals. I already have the totals in the last record of
each group. I'm just trying to put those totals on the first row of the
group and then delete the totals row altogether. It's not needed.

At this point I'd like to point out a small problem. Modern DBMS's
couldn't
give a fig about where a record is in the database, it's how it's sorted
for
display that is important.
Also, the above may show exactly the same, until the database is changed.
example, add an "optical" record, and the above info might start with the
'Lens'
record instead. How it's sorted can change a record's position when
displayed.

This affects your problem - What is the 'first' record you want to put
the total into?.
You may have to add a flag field to indicate the record having the 'total'
data.


The above will set ONLY one record with a total, so you don't have to
worry
about which one has it, or adding records to save your totals.<<

I'm not looking to set totals. All of the data is in the rows, i just
want to change the fields that the totals are in.

Now that I'm not following - don't you mean
i just want to change the records that the totals are in.


After reading your comments, i'm not sure if you understand what it is
i'm trying to do. There is nothing wrong with my recordset, except that
for each group there is a totals row at the end of each group. I want to
take those total fields and put them into the first row of that group,
then delete that totals row.

again, I think you mean the data in the fields, not the fields

I didn't say there's anything wrong with your recordset. I just missed
your point that you have a record which contains totals for a group.

I don't need to do any calculations or find any totals by adding up
fields or anything like that.

I didn't say you have to do any maths, either.

change your program to

'WARNING - AIR CODE
dim mcCASno as single ' use to trap 'previous' info

with casInputFileRecordset 'makes for easier reading.
.movefirst
do until .eof
mCASno = .fields("CASNumber") ' get what will be 'previous'
.movenext ' now move to the 'current' record
If .fields("CASnumber").value <> "" then
' put the previous record's data into current record
.fields("CASnumber").value = mCASno
.update
endif
loop
end with

That should get your current problem out of the way.

I have a funny feeling we're missing something, like when to delete those
'extra' records, and which record is the 'first' (or 'last')
Depends on your query - Ascending or Descending?


Thanks,
Tony
<snip>


.

Relevant Pages

  • RE: An odd problem with SUM(A:B) and AutoSum
    ... This year's Totals ... first external workbook for this year, which appears on the first row of this ... Instead I noticed that when I clicked on Autosum the first row, ... with data, un-highlighted, and the SUM expression at the bottom of the column ...
    (microsoft.public.excel.misc)
  • Re: Water use meter
    ... if your setting is in A1 and your first row of data is in B2:H2 you can ... between these two numbers or is the tank reset while the other tank is ... > I have a water softener intsalled for boiler use. ... > I can get my totals to look at A1 and disregard 650 and give me a correct ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Update fields in previous row from fields in current row
    ... grouped, too, and you don't need the DISTINCT query modifier, either.<< ... I'm not after totals. ... I'm just trying to put those totals on the first row of the group and ... I THINK I can see how to use .bookmark, and it would require bookmarking twice. ...
    (microsoft.public.vb.database.ado)
  • RE: Crosstab Query to produce a report with row field with multipl
    ... Date part figured out but not the 3 totals and title fields that go to each ... "Duane Hookom" wrote: ... query that has a project number in the first row, SumBudgeted Rev, ... monthly totals of revenues per project number with a calculated column at the ...
    (microsoft.public.access.queries)
  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
Loading