collecting varible data per person in table

[gfrlaser]

I have a table of donors, the name and address is repeated up to 10 times with the date of the gift and the dollar amount of the gift different in each record.

John Adams 123 anystreet 9/10/08 $100
John Adams 123 anystreet 6/06/08 $50
John Adams 123 anystreet 3/15/08 $100
John Adams 123 anystreet 5/12/08 $50

what I want is a table that has

John Adams = a) 9/10/08 100 b) 6/06/08 50 c) 3/15/08 100

In date order preferably.

Anyone know a simple way to go about this. We are going to eventually print letters in Word showing the name and address + the amounts they donated with the dates listed in the body. The most I found is one person making 10 donations.


Thanks.

 

[TamarGranor]

First, do NOT change your data structure to store data the way you suggest. That's bad design.

For your reporting needs, rather than trying to create one row per person, use VFP the way it's meant to be used. Build a cursor that has the data you need in the order you need it and report from that.

The one issue is that a Word mailmerge can't handle multiple records per "unit." So for that, you'll have to do one of two things.

1) Use Word automation that builds the letter based on your data instead of mailmerge. This is probably the more flexible solution, but also more work.

2) Combine all the donations into a single field formatted the way you want them before doing the mailmerge. The problem with this solution is that most of the ways you export data from VFP don't touch memo fields, and you'd need to use a memo field for the list of donations.

However, Word appears to be willing to use Dbase file, which is probably Dbase III+ format. So the answer is to save your data into a table in that format before doing the mailmerge.

IOW, there are three steps you need to do to prepare the data for mailmerge:

a) Run a query to collect all the data into a cursor. Something along these lines:

SELECT cName, cAddress, dDonation, nAmount ;
FROM YourOriginalTable ;
WHERE <whatever conditions you want for this list of letters> ;
ORDER BY cName, dDonation ;
INTO CURSOR DonationsToReport


b) Run some code that consolidates the dDonation and nAmount fields into a single memo field. Something like this. I'm guessing on field sizes for your existing data.

CREATE CURSOR DonationsConsolidated ;
(cName C(30), cAddress C(50), mDonations)

SELECT DonationsToReport
cCurName = ""
cCurAddress = ""
cCurDonations = ""
SCAN
IF NOT (cName == m.cCurName)
* New donor, so save current data and move on
IF NOT EMPTY(m.cCurName) && so you don't put an empty record at the top
INSERT INTO DonationsConsolidated ;
VALUES (m.cCurName, m.cCurAddress, m.cCurDonations)
ENDIF

cCurName = DonationsToReport.cName
cCurAddress = DonationsToReport.cAddress
cCurDonations = ""
ENDIF

* Add this donation to the string.
* You'll probably want to do more formatting here than I am.
cCurDonations = m.cCurDonations + CHR(13) + CHR(10) + ;
TRANSFORM(DonationsToReport.dDonation) + " " +;
TRANSFORM(DonationsToREport.nAmount, "@$")

ENDSCAN

* Save the last record
INSERT INTO DonationsConsolidated ;
VALUES (m.cCurName, m.cCurAddress, m.cCurDonations)


c) Save this cursor as a dBase III+ table.

SELECT DonationsConsolidated
COPY TO <wherever you want to store the table> TYPE FOX2X

Tamar

 

[gfrlaser]

Ok. Makes sense. Someone else told me I could not use SQL to make this work. I knew I would have to considate the data somehow though. It's a one time job and I just need to get the data together and send it to the laser printing department for print. I will try to pull together what you suggest and see if I can make it work. Thanks for the response.

 

[gfrlaser]

Hmm.. first time I have ever hung up my entire computer...

I created the code you suggested as follows, Windows reported that the .tmp file was too large at the line;
Insert values into donationsconsoldated;
Values(m.Cname,m.Cad1) Etc.

here is the code... any ideas?

****2/9/09
****code to create donor table for merge to letter

Select Name,a1,a2,city,st,zip,country,Date,Code,amount;
from members.Dbf;
order By Date;
into Cursor donationstoreport

Create Cursor donationsconsolidated;
( Name c(70),a1 c(32), a2 c(22), city c(21), st c(2), zip c(10), country c(10), Date d(8), Code c(3), amount N(9))

Select donationstoreport
CName=' '
Ca1=' '
Ca2=' '
Ccity=' '
Cst=' '
Czip=' '
Ccountry=' '
Cdate=' '
Ccode=' '
Camount=' '

Scan
If Not (Name==m.CName)
If Not Empty(m.CName)
Go Top
Insert Into donationsconsolidated;
VALUES (M.CName, M.Ca1, M.Ca2, M.Ccity, M.Cst, M.Czip, M.Ccountry, M.Cdate, m.Ccode, M.Camount)
Endif

CName=donationstoreport.Name
Ca1=donationstoreport.a1
Ca2=donationstoreport.a2
Ccity=donationstoreport.city
Czip=donationstoreport.zip
Ccountry=donationstoreport.country
Cdate=donationstoreport.Date
Ccode=donationstoreport.Code
Camount=donationstoreport.amount
Endif

Endscan

&&& hangs up the computer somewhere here (.tmp too large)&&

Insert Into donationsconsolidated;
Values (m.CName, m.Ca1, m.Ca2, m.Ccity, m.Czip, m.Ccountry, m.Cdate, m.Ccode, m.Camount)

Select donationsconsolidated
Copy To 74822Member.Dbf Type Fox2

 

[Imaginecorp]

Hmm.. first time I have ever hung up my entire computer...

ROFLMAO {sorry}

 

[Imaginecorp]

Sorry, I did not mean to laugh at you, it was just too funny, the killer was the "Hmm...".
I Have a very simple program that will do exactly what you are looking for, but its too late at night and I am tired, I will find it in the morning and post it...

 

[Imaginecorp]

Here is some sample code; rather then search for the program I had, I wrote this on the fly. Its crude but I think it will work. Sorry nothing elegant comes to mind.

create a table, call it table1, put in 5 fields, cname, city, state, pdate <<date field>> ,amount <<Numeric>>.

Create records: as many as you like. then run the following code: Again remember this is to give you an idea, you will have to fine tune...Good luck

Clear All
Use table1

Select Distinct cName,city, state From table1 Order By 1,2,3 Into Cursor discursor

i = 0
h = 3
Select discursor
Dimension atem[1,30]
Store "" To atem
Scan
	i = i + 1
	sname = discursor.cName+discursor.city+discursor.state
	Select * From table1 Where table1.cName+table1.city+table1.state = sname ;
		Order By cName,pdate Into Cursor temp
	atem[i,1] = temp.cName
	atem[i,2] = temp.city
	atem[i,3] = temp.state
	h  = 3
	Select  temp
	Scan
		h = h + 1
		atem[i,h] = temp.pdate
		h = h + 1
		atem[i,h] = temp.amount
		Select  temp
	Endscan
	Dimension atem[ALEN(atem,1)+1,30]
	Select discursor
Endscan
Dimension atem[ALEN(atem,1)-1,30]

Create Cursor donation (cName c(35), city c(35), state c(2) ,donations M )
d = 0
N = Alen(atem,1)
For x = 1 To Alen(atem,1)
	Insert Into donation ;
		(cName,city,state) Values (atem[x,1],atem[x,2],atem[x,3])
	cstring = ""
	For i = 4 To 30
		If !Empty(atem[x,i])
			If Vartype(atem[x,i]) = "D"
				cstring = cstring +" Date: "+Transform(atem[x,i])
			Else
				cstring = cstring +"  Amount: "+Transform(atem[x,i],"@R$  ###,###.##") +Chr(13)+Chr(10)
			Endif
		Endif
	Endfor
	Replace donations With cstring
Endfor
Select donations
Browse

 

[Imaginecorp]

One more thing; rather than fool around with word etc, just create a simple VFP report, as this is a one time deal...

 

[gfrlaser]

Glad I gave you a chuckle <grin>. At least you didnt break my puter. LOL

If I had my way I would be using a report but I am required to give the data to another department who will carry the ball from there however they want.

I will be working on this code this morning. I really appreciate your help. I have a couple more jobs like this all of sudden and as usual, not much time to complete it in.

 

[gfrlaser]

I received an error 'script outside defined range'. Any idea how to get past this?

it happened at this point:

Scan
h = h + 1
atem[i,h] = temp.pdate
h = h + 1
atem[i,h] = temp.amount
Select temp
Endscan



************************

Clear All
Use table1

Select Distinct cName,city, state From table1 Order By 1,2,3 Into Cursor discursor

i = 0
h = 3
Select discursor
Dimension atem[1,30]
Store "" To atem
Scan
i = i + 1
sname = discursor.cName+discursor.city+discursor.state
Select * From table1 Where table1.cName+table1.city+table1.state = sname ;
Order By cName,pdate Into Cursor temp
atem[i,1] = temp.cName
atem[i,2] = temp.city
atem[i,3] = temp.state
h = 3
Select temp
Scan
h = h + 1
atem[i,h] = temp.pdate
h = h + 1
atem[i,h] = temp.amount
Select temp
Endscan
Dimension atem[ALEN(atem,1)+1,30]
Select discursor
Endscan
Dimension atem[ALEN(atem,1)-1,30]

Create Cursor donation (cName c(35), city c(35), state c(2) ,date d(8),donations M )
d = 0
N = Alen(atem,1)
For x = 1 To Alen(atem,1)
Insert Into donation ;
(cName,city,state) Values (atem[x,1],atem[x,2],atem[x,3])
cstring = ""
For i = 4 To 30
If !Empty(atem[x,i])
If Vartype(atem[x,i]) = "D"
cstring = cstring +" Date: "+Transform(atem[x,i])
Else
cstring = cstring +" Amount: "+Transform(atem[x,i],"@R$ ###,###.##") +Chr(13)+Chr(10)
Endif
Endif
Endfor
Replace donations With cstring
Endfor
Select donations
Browse

 

[Imaginecorp]

Like I said earlier this code is crude and has not been tested. I just ran it without an error Except:
its not
Select donations
Browse
BUT
Select donation &&& No s
Browse
Maybe you have more records than I did... Increase the Array dimensions to say: Dimension[1,100] i.e. Change all the 30's to 100 then try it first.

table1 structure:
cname c(35), city c(35), state c(2) pdate d, amount N(10,2)

The table contains the following info: If increasing the Array does not help Try the code with this data to see if you still get the error.

The error normally means we are sending more data than what the array can hold...

John Smith                         Albany                             NY21090101     10.00
John Smith                         Albany                             NY21090109     30.00
John Smith                         Albany                             NY21090120    100.00
John Smith                         Albany                             NY21090102      1.00
John Smith                         Albany                             NY21090104      6.00
Joe Smith                          Albany                             NY21090101     10.00
Joe Smith                          Albany                             NY21090109     30.00
John Blow                          Albany                             NY21090120    100.00
John blow                          Albany                             NY21090102      1.00
John blow                          Albany                             NY21090104      6.00
hooty                              Albany                             NY21090101     10.00
hooty                              Albany                             NY21090102     30.00
hooty                              Albany                             NY21090120    100.00
hooty                              Albany                             NY21090103      1.00
hooty                              Albany                             NY21090104      6.00
hooty                              Albany                             NY21090111     10.00
hooty                              Albany                             NY21090109     30.00
hooty                              Albany                             NY21090121    100.00
hooty                              Albany                             NY21090106      1.00
hooty                              Albany                             NY21090107      6.00

 

[Imaginecorp]

21090107 etc are dates...

 

[Olaf Doschke]

How many records do you have?

Create Cursor donationsconsolidated;
( Name c(70),a1 c(32), a2 c(22), city c(21), st c(2), zip c(10), country c(10), Date d(8), Code c(3), amount N(9))

This makes 70+32+22+21+2+10+10+8+3+9+1(deleted flag) = 188 Bytes per Record. Neglecting header this gives space for about 11 million consolidated records at maximum.

You do

Select Name,a1,a2,city,st,zip,country,Date,Code,amount;
from members.Dbf;
order By Date;
into Cursor donationstoreport

That is you order by DATE, but you consolidate by CNAME, that would explain why you don't get consolidated records, also you don't sum on anything, neither concatening lines to some memo field, nor summing amounts.

Bye, Olaf.

 

[TamarGranor]

The problem with your version of my suggestion is the GO TOP. You keep going back to the first record. You don't need to GO TOP before issuing INSERT.

Tamar

 

[Imaginecorp]

I received an error 'script outside defined range'. Any idea how to get past this?

I just noticed your table fields are not in the order mine were, thats the reason the ARRAY threw an error. You have something called code c(3) between date and amount. If you can leave the date and amount field last. move code c(3) before date. change the value of h to the number of fields before date... Thats it, I think

 

[gfrlaser]

I have 6220 records. This is one of things that just drive me nuts. I have too many jobs to work on I guess. No wonder I am burning the midnight oil.

 

[gfrlaser]

I feel like I might be getting somewhere.

here are the revisions. However at this point.....

***

Insert Into donationsconsolidated;
Values (m.CName, m.Ca1, m.Ca2, m.Ccity, m.Czip, m.Ccountry, m.Cdate, m.Ccode, m.Camount)

***

I recieve a data type mismatch error... I can see no errors in the type of data created with donationsconsolidate.

the entire code:
**************************
Select Name,a1,a2,city,st,zip,country,Date,Code,amount;
from members.Dbf;
order By Date;
into Cursor donationstoreport



&& put date and amount at the end per ImageCorp
Create Cursor donationsconsolidated;
( Name c(70),a1 c(32), a2 c(22), city c(21), st c(2), zip c(10), country c(10), code c(3), Date d(8), amount N(9))

Select donationstoreport
CName=' '
Ca1=' '
Ca2=' '
Ccity=' '
Cst=' '
Czip=' '
Ccountry=' '
Cdate=' '
Ccode=' '
Camount=' '

Scan
If Not (Name==m.CName)
If Not Empty(m.CName)
**Go Top && Tamar's suggestion
Insert Into donationsconsolidated;
VALUES (M.CName, M.Ca1, M.Ca2, M.Ccity, M.Cst, M.Czip, M.Ccountry, M.Cdate, M.Ccode, M.Camount)
Endif

CName=donationstoreport.Name
Ca1=donationstoreport.a1
Ca2=donationstoreport.a2
Ccity=donationstoreport.city
Czip=donationstoreport.zip
Ccountry=donationstoreport.country
Cdate=donationstoreport.Date
Ccode=donationstoreport.Code
Camount=donationstoreport.amount
Endif

Endscan

Insert Into donationsconsolidated;
Values (m.CName, m.Ca1, m.Ca2, m.Ccity, m.Czip, m.Ccountry, m.Cdate, m.Ccode, m.Camount)

Select donationsconsolidated
Copy To 74822Member.Dbf Type Fox2

 

[Imaginecorp]

Create Cursor donationsconsolidated;
    ( Name c(70),a1 c(32), a2 c(22), city c(21), st c(2), zip c(10), country c(10), code c(3), [blue]Date d(8)[/blue], amount N(9))

          Insert Into donationsconsolidated;
                VALUES (M.CName, M.Ca1, M.Ca2, M.Ccity, M.Cst, M.Czip, M.Ccountry, M.Cdate, [blue]M.Ccode[/blue], M.Camount)

You are inserting a Character into a date field. I asked you to change the positions of the fields Only if you were using the code I wrote.

Though I am kinda lost as what you are doing, I thought you wanted to arrange the dates and amounts and put them in a memo field and send them for printing...

 

[gfrlaser]

ok. I hope you excuse me if I mis understood. I did change the order, I put the date and amount last, however the order of the dates really dont matter at this stage.

I am too bad at doing this foxpro programming stuf, but not anywhere near as good as you guys. I had to get this thing done and out to the person that will print it. The goal was to have one line with all the information so that this other person could merge it into a letter with the program of thier choice.

So, contrary to popular belief, I wrote a small program to do just that. The code I will post in a minute.

LOL... now I have one that is very similar except the data base is 6000+ records, the name may repeat up to 125 times!! and as in the last one, I have to populate each record with the repeating value(customer to agent) for each record. Using what I already wrote (I will post below), I would have to create a table with an additional 125 fields X2 (the name of the customer and the policy number).

Can I do this? Yes. Do I have time.... LOL

I am at a point I am considering to, on the new database, to just have the person that has to print the letters cut and past each one into a letter template.
For instance, David Henry may appear up to 125 times....!!!

agent Policy holder
David C Henry James R Downs
David C Henry Joyce E Morris
David C Henry Joyce E Morris
David C Henry Patrick J Farrell
David C Henry Wayne D Morris
David C Henry Wayne D Morris
David C Henry Wayne D Morris
David C Henry Lloyd Ryan
David C Henry Rita M Ryan
David C Henry Rita M Ryan
David C Henry Alan W Grace
David C Henry Alan W Grace
David C Henry Alan W Grace
David C Henry Alan W Grace
David C Henry Alan W Grace
David C Henry Almamzor J Messier Jr
David C Henry Almanzor J Messier Jr
David C Henry Almanzor J Messier Jr

&&&&&& next letter

William B Milham Keith W Jordan
William B Milham Anna M Jordan
William B Milham Scott P Stoddard
William B Milham William R Borden


The letter has to have in the body;
the original agent name of David C Henry one time, then

Alan W Grace + the name of holder + policy number
Alan W Grace + the name of holder + policy number
Alan W Grace + the name of holder + policy number

etc....

as many times as it appears. Boiled down from the database it will only be 70 letters out of the 6000 database. Make sense? The policy holders are duplicates because they hold more than one policy.


OK.. the code I used to fix the first problem... Please don't laugh, I had to do something QUICK.

*****************************
set safety off

use members
copy to table1

set dele on
select 1
use table1
copy to table1bak

close all


select 1
use table1
select 2
use table2


*do while not deleted()
do while not eof()
select 1
go top
scatter to sc
*dele
pack
sele 2
append blank
gather from sc
store name to mf


** 1


sele 1

locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da1 with md
replace co1 with cd
replace am1 with ad

store name to mf
endif

**2

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da2 with md
replace co2 with cd
replace am2 with ad

store name to mf
endif
**3

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da3 with md
replace co3 with cd
replace am3 with ad

store name to mf
endif
**4

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da4 with md
replace co4 with cd
replace am4 with ad

store name to mf
endif
**5

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da5 with md
replace co5 with cd
replace am5 with ad

store name to mf
endif
**6

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da6 with md
replace co6 with cd
replace am6 with ad

store name to mf
endif
**7

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da7 with md
replace co7 with cd
replace am7 with ad

store name to mf
endif
**8

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da8 with md
replace co8 with cd
replace am8 with ad

store name to mf
endif
**9

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da9 with md
replace co9 with cd
replace am9 with ad

store name to mf
endif
**10

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da10 with md
replace co10 with cd
replace am10 with ad

store name to mf
endif

**11

sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da11 with md
replace co11 with cd
replace am11 with ad

store name to mf
endif

**12
sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da12 with md
replace co12 with cd
replace am12 with ad

store name to mf
endif

**13
sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da13 with md
replace co13 with cd
replace am13 with ad

store name to mf
endif

**14
sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da14 with md
replace co14 with cd
replace am14 with ad

store name to mf
endif

**15
sele 1
locate for name=mf
if not found()
enddo
endif

if found()
store date to md
store amount to ad
store code to cd
dele

sele 2
replace da15 with md
replace co15 with cd
replace am15 with ad

store name to mf
endif

select 1
go top
enddo

*************************

Pretty barbaric, I know.. but it worked.
You guys are great.. thank for all your help.

Will