Changing an Access Table to a Cobol friendly format to load to amainframe

  • Thread starter Thread starter Linda Ribbach
  • Start date Start date
L

Linda Ribbach

Hi,
I think I have everything under control, except I need to create a
routine that will insert line numbers into a table e.g.
01,02,03,04,05,06 until a number in another field changes.

Then, when that other number changes again, I want the routine to start
the count over at 01 . I want this to continue until the end of the
table.

Example:
Line No RefNo
01 767CB100303
02 767CB100303
03 767CB100303
01 767CB100304
02 767CB100304
03 767CB100304
04 767CB100304
01 767CB100305
01 767CB100306

Thanks In advance
Linda
 
Dear Linda:

Do you have another column or set of columns by which you can uniquely
sort these rows, so that they are in an unambiguous order? Otherwise,
what is it that makes any one of them come before another one?

I you can creat a query that shows everything you want to "load to a
mainframe" and which is uniquely sorted, I can then add the LineNo you
asked for. Just post the SQL of that query here.

Hi,
I think I have everything under control, except I need to create a
routine that will insert line numbers into a table e.g.
01,02,03,04,05,06 until a number in another field changes.

Then, when that other number changes again, I want the routine to start
the count over at 01 . I want this to continue until the end of the
table.

Example:
Line No RefNo
01 767CB100303
02 767CB100303
03 767CB100303
01 767CB100304
02 767CB100304
03 767CB100304
04 767CB100304
01 767CB100305
01 767CB100306

Thanks In advance
Linda
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

To answer your question, yes, there is another number which makes each row
unique. It's called the TransNo. The Sql is very simple.

UPDATE tblLineRecord SET tblLineRecord.LineNo = "My Routine Placed Here";
This query would call a routine that is in a module.

I don't how to write a loop to increment the LineNo until a certain
condition occurs and then to continue to do this until there are no more
records. This is what I want: "Increment the LineNo as long as the RefNo is
the same. When the RefNo changes start at 01 again until end of file."


LineNo RefNo
TransNo
01 767CB100303 12512154445
02 767CB100303 25041121455
03 767CB100303 26044545641
01 767CB100304 31254545778
02 767CB100304 42245645745
03 767CB100304 42345464781
04 767CB100304 51245788998
01 767CB100305 75454678785
01 767CB100306 87878954751

Linda
 
Dear Linda:

SELECT RIGHT("00" & CStr(SELECT COUNT(*) + 1 FROM YourTable T2
WHERE T2.RefNo = T1.RefNo AND T2.TransNo < T1.TransNo)), 2)
AS LineNo,
RefNo
FROM YourTable T1
ORDER BY RefNo, TransNo

Try this please. You have to replace "YourTable" with the actual
table or query name.

There is no "loop to increment the LineNo." When writing queries it
is best to avoid thinking in terms like "looping". The idea that we
are sequentially processing the data is entirely foreign to writing
queries. Instead, we are writing "rules" that perform the same tasks.
This is truly a different way of thinking. It's a "fourth generation
program language" way of thinking. I know this can be tough (I had to
make the transition myself!)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom,

This is how I interpreted your message using my fields and table name:

SELECT RIGHT("00" & CStr(SELECT COUNT(*) + 1
FROM tblLineRecord T2
WHERE T2.IVNo = T1.IVNo AND T2.TransNo < T1.TransNo), 2)
AS LineNo,
IVNo
FROM tblLineRecord T1
ORDER BY IVNo, TransNo

The SQL above is using my table name and field names. I had to change RefNo to IV
number. I'm not getting any error messages when I attempt to see it design view
or datasheet view. However, it doesn't run. It looks like it's created a temp
table called T1 when I look at it n the design view.

Then there are three fields: |LineNo:"00" | IVNo | TransNo|

Thanks for your help. I'm trying to put incremental numbers starting at 01 in the
TransNo field until the IVNo changes and then I want to start at 01 again. I can
almost understand your SQL. How can I update the TransNo field? Should I use an
update query and put this into it?
 
Tom,

I have another problem now. When I try to close a table in this database,
regardless if it referenced in the sql you gave me, I get an error message that
says "the action will reset the current code in break mode". I cannot close any
table without going to design view first and then closing it. What happened? I
think I'll recreate the db and start from scratch.

Linda
 
Dear Linda:

I was looking to see why this might be causing you a problem. I think
I see it. Try this small change:

SELECT RIGHT("00" & CStr((SELECT COUNT(*) + 1
FROM tblLineRecord T2
WHERE T2.IVNo = T1.IVNo AND T2.TransNo < T1.TransNo)), 2)
AS LineNo,
IVNo
FROM tblLineRecord T1
ORDER BY IVNo, TransNo

When you have subquery as an argument of a function you must put
parens around the subquery, even if it means doubling up parens
already there. This is a difficult requirement for me to remember at
times. Please see if this helps, OK?

I was thinking you could use an Append Query made from this. Create a
table to receive the results, empty it out of previous results, then
append these rows. Then you'd have something to export.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

That was so slick. It worked perfectly. I added a few more fields to the
append so that I could link other data later by unique field content. Oh
that was sooooo slick.

I'm creating a file based on format specs that will enable the data to be
loaded to our Financial System. The document line limit is 99. I don't
remember if I mentioned the line limit, but this query works perfectly for
my purposes. There will never be more than 99 lines per IVNo.

This query will only work for under 100 lines, right? But, I can see plenty
of other applications for this.

Thank you Linda
 
Dear Linda:

The query will "work" for more than 99 lines. But the LineNo will go
to 00, then 01, etc after reaching 99. If you want a 3 digit counter
change to:

SELECT RIGHT("000" & CStr((SELECT COUNT(*) + 1
FROM tblLineRecord T2
WHERE T2.IVNo = T1.IVNo AND T2.TransNo < T1.TransNo)), 3)
AS LineNo,
IVNo
FROM tblLineRecord T1
ORDER BY IVNo, TransNo

There are many, many applications for ranking rows, some of them other
than just to display a line number.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top