Best way to link two tables with unusual layouts? 2

[tjc240e]

I have a member table with a donation envelope number in it and I have the donation spreadsheet with the envelope number in it. The problem is the donation spreadsheet, has a line with the env number and contact info, then under it are the donations:

1 Dr. Joe Jang
03/28/2004 $50.00 2030
05/02/2004 $0.00
06/13/2004 $0.00
4 Mr. and Mrs. John L. Doe
01/04/2004 $170.00
02/22/2004 $25.00
5 Mr. and Mrs. David Smith
01/04/2004 $50.00
01/11/2004 $50.00
01/18/2004 $50.00
01/25/2004 $25.00 2090

Does anyone have a slick way of being able to match the first line of contact info (env number) and then tell Foxpro to cycle through until it hits the next env number?

Does this make sense?

All help is very much appreciated. Thanks in advance.

Tom Cusick

 

[Stella740pl]

If you have VFP versions 7 and up, you can find all the documentation on-line, on MSDN site, try

http://msdn.microsoft.com/library/default.asp

If youn are allowed to use Tek-Tips, sure you would be allowed to use Microsoft?

By the way, what did you mean by
cfund=alltrim(acct_num) ?

Did you want, as you said, to return the field's value or to replace it with something else?
The code above wouldn't work for neither, but to replace, you need REPLACE or other means, not SEEK/LOCATE.

To use SEEK, the table should be actually INDEXed, not just physically sorted/ordered. By INDEXed I mean that CDX file with appropriate TAG, or IDX file should exist.

So, it could be something like that (tweak it if you need):

SELECT MyTable && better than SELECT 11
INDEX ON cFund TAG cFund

cAcct=ALLTRIM(acct_type)
SEEK cAcct ORDER TAG cFund ASCENDING IN MyTable
IF FOUND()
?acct_num
ENDIF

*If you need to find all the following matches, you can

IF FOUND()
DO WHILE cFund=cAcct
....
ENDDO
ENDIF

For SEEK() the function doesn't require FOUND() function, it returns .t. or .f. itself.

If, for some reason, you don't index your table on that key, you can use slower LOCATE.

SELECT MyTable
cAcct=ALLTRIM(acct_type)
LOCATE FOR cFund=cAcct
DO WHILE FOUND( )
?acct_num
CONTINUE
ENDDO

These are just the simplest examples. Look it up in the on-line Help, if you cannot have yours.

 

[tjc240e]

Well that helps a bit but it seems to be the same problem that I've been having is that if I don't do a loop it just pulls the first acct_num from the record. But if I do a loop it seems to just run forever (or at least until the EOF). I just copied exactly what you have there just making label changes... any ideas?

 

[Stella740pl]

tjc240e,

You are not giving enough information to get help.

* Which of the fragments did you copy?
* Did it find anything at all or not? Does it seem to take the correct record or not?
* What do you mean, it pulls the first acct_num from the record? How many of them are there? How many did you actually code to pull?
* Do you know for sure that the value you are looking for definitely exists in the table?
* What kinds of values is that?
* What's the structure of the table (or, at least, affected fields) and what does the value you are trying to find look like?
* Did you try to debug it or not (say, by inserting some intermediate print/message/etc.)?

And most importantly, what exactly you are trying to do? If you need to find all records satisfying the conditions, there are multiple ways to do it, including SELECT-SQL statement. The preferable way to do something may depend on your purposes, size of your table, etc.

Please, clarify.

Stella