|
Home > Archive > dBASE Programming > January 2007 > Another Loop Question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Another Loop Question
|
|
|
| Hi,
First I want to thank Ivar and Ken for the answers to my earlier question "Building a Table from a Table." With their information I was able to build a small program (basically copied it) and now, each time I update my DelStats I save about 2 hours of han
d work -- and it's completely accurate, unlike the old way of doing it. Thanks!
Now I've got one more question (probably one of many, but I'll try not to be too much of a pain here).
The USPS DelStat (Delivery Statistics) database exists just to give the the routes in each zip code and the number of deliveries in each route for -- updated monthly. We only use it for the rural routes (mostly in our own area). Currently we aren't requir
ed to separate PO Box Sections in rural routes -- as we must do in Carrier Routes (bigger cities, which is handled by another USPS product, CDS). So my processed database, the one I referred to in my earlier question, has listings like the following:
ZIP CRRT TOTAL
83354 B001 17
83354 B002 34
83354 B003 22
83354 B004 45
etc.
For the purpose of making a new table from these numbers, this is not a problem. I just replace all the B001, B002, etc., routes with "B000." Since the table is sorted by Zip and CRRT it just appends the next "route" to the one above it -- it doesn't care
how many "B000"s it sees.
But now I would like to save some more hand work and build a table that adds all the B000 routes Totals for each Box Section in each Zip Code. (I need to be able to give total numbers to customers so they can decide where they want to mail their ads.)
So, instead of the above table, there would be one listing for PO Box sections for each Zip Code:
ZIP CRRT TOTAL
83354 B000 118
I think I've got the basic concept, a loop in a loop that totals all the "B000s" for each zip code into a memory variable, then writes to the table when the next unique route (for example "R001") is scanned in.
I'm guessing you read the information into memory variables until the CRRT from Table 1 changes, then write what's in the variables to the new TABLE, then append a blank and read the new information into the memory variables. So there would be an "if" con
dition. If TABLE1->CRRT <> mCRRT then replace Zip with mZip, CRRT with mCCRT, Total with mTotal, then Append blank, then let mZip=TABLE1->Zip, mCRRT=TABLE1->CRRT, mTotal=TABLE1->Total. Then Scan the next record.
Doing this in dBASE (and making sure everything stays in sync) is still tricky for me -- and from past experience in asking dBASE programming questions, I know there is always a more efficient way of doing things then what I come up with (providing I come
up with anything at all).
At any rate, thanks for any suggestions. And, again, sorry for the long winded message. I'm trying to as specific as possible, probably too much so.
Sincerely,
RonB
| |
| Ivar B. Jessen 2007-01-09, 1:30 am |
| On Wed, 27 Dec 2006 05:21:15 -0500, in dbase.programming,
Subject: Another Loop Question,
Message-ID: <IzlY9CaKHHA.912@news-server>,
RonB <rblizz@myway.com> wrote:
>The USPS DelStat (Delivery Statistics) database exists just to give the the routes in each zip code and the number of deliveries in each route for -- updated monthly. We only use it for the rural routes (mostly in our own area). Currently we aren't requi
red to separate PO Box Sections in rural routes -- as we must do in Carrier Routes (bigger cities, which is handled by another USPS product, CDS). So my processed database, the one I referred to in my earlier question, has listings like the following:
>
>ZIP CRRT TOTAL
>83354 B001 17
>83354 B002 34
>83354 B003 22
>83354 B004 45
> etc.
>
>For the purpose of making a new table from these numbers, this is not a problem. I just replace all the B001, B002, etc., routes with "B000." Since the table is sorted by Zip and CRRT it just appends the next "route" to the one above it -- it doesn't car
e how many "B000"s it sees.
>
>But now I would like to save some more hand work and build a table that adds all the B000 routes Totals for each Box Section in each Zip Code. (I need to be able to give total numbers to customers so they can decide where they want to mail their ads.)
>
>So, instead of the above table, there would be one listing for PO Box sections for each Zip Code:
>
>ZIP CRRT TOTAL
>83354 B000 118
Try the code below my signature. When running it I get the following ourput:
Record# ZIP CRRT TOTAL
1 83301 B000 65
2 83354 B000 118
Ivar B. Jessen
//-----
clear
close all
clear memory
if file("rural1.dbf")
drop table rural1
endif
if file("rural2.dbf")
drop table rural2
endif
create table rural1(ZIP char(5), CRRT char(4), TOTAL integer)
insert into rural1 values ("83301","B000", 10)
insert into rural1 values ("83301","B000", 5)
insert into rural1 values ("83301","B000", 20)
insert into rural1 values ("83301","B000", 30)
insert into rural1 values ("83354","B000", 17)
insert into rural1 values ("83354","B000", 34)
insert into rural1 values ("83354","B000", 22)
insert into rural1 values ("83354","B000", 45)
use rural1 excl
index on ZIP tag ZIP
set fields to ZIP, CRRT, TOTAL
total on ZIP to rural2
use rural2
list
use
//-----
| |
| Roland Wingerter 2007-01-09, 1:30 am |
| Ivar B. Jessen wrote >
> Try the code below my signature. When running it I get the following
> ourput:
>
> Record# ZIP CRRT TOTAL
> 1 83301 B000 65
> 2 83354 B000 118
-------
Ivar, I think Ron uses 5.x, so I tried your code in VdB 5.7. I get an error
on the line "insert into", it looks like this SQL statement is not supported
in VdB 5.x.
Roland
| |
| Ivar B. Jessen 2007-01-09, 1:30 am |
| On Wed, 27 Dec 2006 14:00:09 +0100, in dbase.programming,
Subject: Re: Another Loop Question,
Message-ID: <A4ppuibKHHA.1216@news-server>,
"Roland Wingerter" <ich@hier.de> wrote:
>Ivar B. Jessen wrote >
>-------
>Ivar, I think Ron uses 5.x, so I tried your code in VdB 5.7. I get an error
>on the line "insert into", it looks like this SQL statement is not supported
>in VdB 5.x.
>
Roland,
Thanks for pointing this out. I had used similar code in a previous example and Ron did not mention
that he had a problem with that code.
The workaround is to use APPEND BLANK and REPLACE .. WITH..
like in the following,
for i = 1 to 8
append blank; replace ZIP with ""83301", CRRT with "B000", TOTAL with 10
append blank; replace ZIP with ""83301", CRRT with "B000", TOTAL with 5
......
......
next i
Ivar B. Jessen
| |
| Roland Wingerter 2007-01-09, 1:30 am |
| Ivar B. Jessen wrote
> "Roland W." wrote:
>
> Thanks for pointing this out.
-----
You're welcome.
> The workaround is to use APPEND BLANK and REPLACE .. WITH..
-----
OK, thanks. I knew it could be done but was too lazy to figure it out... ;-)
FWIW, the following SQL statement sums up the field total too:
select distinct zip, crrt, sum(total) from rural1 group by zip, crrt
The above is an alternative if one doesn't mind that the format of total
will be numeric(20,4).
Roland
| |
| Ivar B. Jessen 2007-01-09, 1:30 am |
| On Wed, 27 Dec 2006 20:39:52 +0100, in dbase.programming,
Subject: Re: Another Loop Question,
Message-ID: <7CDYECfKHHA.1068@news-server>,
"Roland Wingerter" <ich@hier.de> wrote:
>FWIW, the following SQL statement sums up the field total too:
>
>select distinct zip, crrt, sum(total) from rural1 group by zip, crrt
>
>The above is an alternative if one doesn't mind that the format of total
>will be numeric(20,4).
In a previous message (in a different thread) Ron used work areas, not rowsets, and terms like
rural->ZIP, so I assumed that SQL statements would be too advanced :-)
Ivar B. Jessen
| |
| Roland Wingerter 2007-01-09, 1:30 am |
| Ivar B. Jessen wrote
> "Roland W." wrote:
>
> In a previous message (in a different thread) Ron used work areas, not
> rowsets, and terms like
> rural->ZIP, so I assumed that SQL statements would be too advanced :-)
-------
I see, that's very thoughtful of you.
Roland
| |
|
| Ivar B. Jessen Wrote:
> Thanks for pointing this out. I had used similar code in a previous example and Ron did not mention
> that he had a problem with that code.
>
> The workaround is to use APPEND BLANK and REPLACE .. WITH..
> like in the following,
>
> for i = 1 to 8
> append blank; replace ZIP with ""83301", CRRT with "B000", TOTAL with 10
> append blank; replace ZIP with ""83301", CRRT with "B000", TOTAL with 5
> .....
> .....
>
> next i
Hi Ivar,
Sorry it's taken so long getting back to you -- been fighting a cold/flu/something. The last time I had Ken's code first and just modified and used that -- assuming that yours would also work. Sorry I didn't make that clear.
I haven't had a chance to try this code yet, but by appending blank and filling it in with the next B000 Total -- won't we still have a whole list of B000s?
83350 B000 10
83350 B000 5
etc.
Or am I missing something here?
Thanks again,
Ron
| |
|
| Ivar B. Jessen Wrote:
> In a previous message (in a different thread) Ron used work areas, not rowsets, and terms like
> rural->ZIP, so I assumed that SQL statements would be too advanced :-)
Huh?
You're right. Although, if I can use something like that in VdB 5.7 I wouldn't mind learining.
Thanks,
RonB
| |
| Ivar B. Jessen 2007-01-09, 1:30 am |
| On Fri, 29 Dec 2006 12:09:41 -0500, in dbase.programming,
Subject: Re: Another Loop Question,
Message-ID: <QoY5gw2KHHA.1764@news-server>,
RonB <rblizz@myway.com> wrote:
>Sorry it's taken so long getting back to you -- been fighting a cold/flu/something. The last time I had Ken's code first and just modified and used that -- assuming that yours would also work. Sorry I didn't make that clear.
>
>I haven't had a chance to try this code yet,
Let me hear the result when you have tried the code.
> but by appending blank and filling it in with the next B000 Total -- won't we still have a whole list of B000s?
>
>83350 B000 10
>83350 B000 5
> etc.
>
>Or am I missing something here?
I don't relly know what you are talking about. From a previous posting I got the impression the you
for some unspecified reason replaced all the B001, B002 ... values with B000 and for the sake of the
demo I created and used a table where the CRRT column was filled with BOOO. As that value does not
enter into the calculation of the value of the groups in the ZIP column or the sums in the TOTAL
column I do not see how the CRRT column is relevant to the problem you posted.
>Thanks again,
You are welcome.
Ivar B. Jessen
| |
|
| Ivar B. Jessen Wrote:
> I don't relly know what you are talking about. From a previous posting I got the impression the you
> for some unspecified reason replaced all the B001, B002 ... values with B000 and for the sake of the
> demo I created and used a table where the CRRT column was filled with BOOO. As that value does not
> enter into the calculation of the value of the groups in the ZIP column or the sums in the TOTAL
> column I do not see how the CRRT column is relevant to the problem you posted.
Hi Ivar,
I was unclear again. I apologize. I think I was confusing two different functions.
What I've got now is a table that looks something like this:
Zip CRRT Total
83301 C001 420
83301 C002 468
83301 C003 677
83301 B000 34
83301 B000 21
83301 B000 45
83001 B000 17
83301 H045 321
What I want to have when finished is a table like this:
Zip CRRT Total
83301 C001 420
83301 C002 468
83301 C003 677
83301 B000 117
83301 H045 321
In other words every Zip+CRRT pair will be unique and their total field will hold the sum of all the "formerly" redundant Zip+CRRT totals. Since all PO Box sections will be designated "B000" that means that every zip should only have one entry for CRRT "B
000."
BTW, the reason I change B001, B002, etc., to B000 is that, for the purpose of Rural Route mailing, we only need one PO Box section per zip code. Even though PO Box sections are separated in our DELSTAT database, they are treated as one route for mailing
purposes. The reason they are separated into sections at all is because, in about two years (once the Post Office is done compiling the Rural Route address database), rural routes will be treated exactly like City Routes and City PO Boxes which *must* be
separated in walk sequence sections. When that time comes the DELSTAT database will become obsolete (for our purposes anyhow) and we'll have to use the CDS product for Rural as well as City addressing.
Thanks for your patience.
RonB
| |
| Ivar B. Jessen 2007-01-09, 1:30 am |
| On Fri, 29 Dec 2006 18:26:38 -0500, in dbase.programming,
Subject: Re: Another Loop Question,
Message-ID: <inw$JD6KHHA.1064@news-server>,
RonB <rblizz@myway.com> wrote:
>I was unclear again. I apologize. I think I was confusing two different functions.
>
>What I've got now is a table that looks something like this:
>
>Zip CRRT Total
>83301 C001 420
>83301 C002 468
>83301 C003 677
>83301 B000 34
>83301 B000 21
>83301 B000 45
>83001 B000 17
>83301 H045 321
>
>What I want to have when finished is a table like this:
>
>Zip CRRT Total
>83301 C001 420
>83301 C002 468
>83301 C003 677
>83301 B000 117
>83301 H045 321
>
>In other words every Zip+CRRT pair will be unique and their total field will hold the sum of all the "formerly" redundant Zip+CRRT totals. Since all PO Box sections will be designated "B000" that means that every zip should only have one entry for CRRT "
B000."
Run the code below my signature, it should result in the following:
Record# ZIP CRRT TOTAL
1 83301 B000 117
2 83301 C001 420
3 83301 C002 468
4 83301 C003 677
5 83301 H045 321
Read also the on-line help on the command TOTAL in your version of dbase. The example in the help
file was used with minor modifications in the present demo.
Ivar B. Jessen
//-----
clear
close all
clear memory
if file("rural1.dbf")
drop table rural1
endif
if file("rural2.dbf")
drop table rural2
endif
create table rural1(ZIP char(5), CRRT char(4), TOTAL integer)
use rural1
append blank; replace ZIP with "83301", CRRT with "C001", TOTAL with 420
append blank; replace ZIP with "83301", CRRT with "C002", TOTAL with 468
append blank; replace ZIP with "83301", CRRT with "C003", TOTAL with 677
append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 34
append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 21
append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 45
append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 17
append blank; replace ZIP with "83301", CRRT with "H045", TOTAL with 321
use rural1 excl
index on ZIP + CRRT tag ZIP_CRRT
set fields to ZIP_CRRT = ZIP + CRRT
set fields to ZIP, CRRT, TOTAL
total on ZIP_CRRT to rural2
use rural2
list ZIP, CRRT, TOTAL
use
//-----
| |
|
| Ivar B. Jessen Wrote:
> Read also the on-line help on the command TOTAL in your version of dbase. The example in the help
> file was used with minor modifications in the present demo.
>
>
> Ivar B. Jessen
>
> //-----
> clear
> close all
> clear memory
>
> if file("rural1.dbf")
> drop table rural1
> endif
>
> if file("rural2.dbf")
> drop table rural2
> endif
>
> create table rural1(ZIP char(5), CRRT char(4), TOTAL integer)
> use rural1
> append blank; replace ZIP with "83301", CRRT with "C001", TOTAL with 420
> append blank; replace ZIP with "83301", CRRT with "C002", TOTAL with 468
> append blank; replace ZIP with "83301", CRRT with "C003", TOTAL with 677
> append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 34
> append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 21
> append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 45
> append blank; replace ZIP with "83301", CRRT with "B000", TOTAL with 17
> append blank; replace ZIP with "83301", CRRT with "H045", TOTAL with 321
>
> use rural1 excl
> index on ZIP + CRRT tag ZIP_CRRT
>
> set fields to ZIP_CRRT = ZIP + CRRT
> set fields to ZIP, CRRT, TOTAL
> total on ZIP_CRRT to rural2
>
> use rural2
> list ZIP, CRRT, TOTAL
> use
> //-----
Thanks. I've copied the program to file and will work on it when I go back to work on Tuesday -- or maybe sooner.
And I *will* read the help file on Total. Thanks.
RonB
| |
|
| Ivar B. Jessen Wrote:
> use rural1 excl
> index on ZIP + CRRT tag ZIP_CRRT
>
> set fields to ZIP_CRRT = ZIP + CRRT
> set fields to ZIP, CRRT, TOTAL
> total on ZIP_CRRT to rural2
Tested this in dBASE DOS 5, it's exactly what I needed. Thank you. And no loops or memory variables needed at all.
RonB
| |
| Ivar B. Jessen 2007-01-09, 1:30 am |
| On Sun, 31 Dec 2006 01:44:19 -0500, in dbase.programming,
Subject: Re: Another Loop Question,
Message-ID: <xRA0YcKLHHA.796@news-server>,
RonB <rblizz@myway.com> wrote:
>
>Tested this in dBASE DOS 5, it's exactly what I needed. Thank you. And no loops or memory variables needed at all.
I am glad it worked for you. Next time read the help file before you start reinventing the wheel :-)
Ivar B. Jessen
| |
|
| Ivar B. Jessen Wrote:
> On Sun, 31 Dec 2006 01:44:19 -0500, in dbase.programming,
> Subject: Re: Another Loop Question,
> Message-ID: <xRA0YcKLHHA.796@news-server>,
> RonB <rblizz@myway.com> wrote:
>
>
> I am glad it worked for you. Next time read the help file before you start reinventing the wheel :-)
Only problem is I would have had no idea where to start. I never realized there was such a command as Total. But I am going to get out my Reference Manual and read a few pages each day.
Thanks.
RonB
|
|
|
|
|