help with select to cursor for report 2

[Judi201]

Hi!
I have two tables. table1 has basic info while table2 may have several records matching keyid for table1. I need to select several fieldd from table1 and one field from up to 5 records in table2 to display on report. (there will be five columns on the report, but not all will contain info).
I use cursors to set up my reports but have never had to select from multiple records for one line on the report before. I have played with the query builder but can't get my required results.

I would appreciate any suggestion to point me in the right direction. If there are other threads on this, I am missing them.

Thanks for any help.

Judi

 

[JimWWinter]

Would something like the following work for you?

* First, create an empty cursor with all the fields you will need for the report
CREATE CURSOR OutPut (.....)
USE Table2 ORDER KeyID ALIAS Table2 IN SELECT('Table2')
USE Table1 ORDER KeyID ALIAS Table1 IN SELECT('Table1')
SELECT Table1
SCAN
INSERT INTO Output(Field Names from Table1) VALUES (Values from Table1)
SELECT Table2
FOR i = 1 TO 5
IF KeyID = Table1.KeyID
REPLACE Output.Fld + TRANSFORM(i) WITH Table2.FldName
ELSE
EXIT
ENDIF
SKIP
ENDFOR
SELECT Table1
ENDSCAN


Regards,
Jikm

 

[bborissov]

Judi, can you post some data to see what you want. It is hard to me to understand

Jikm, this code will rise error.

  REPLACE Output.Fld + TRANSFORM(i) WITH Table2.FldName

** must be
   REPLACE (Output.Fld + TRANSFORM(i)) WITH Table2.FldName

Also there is no SEEK in Table 2, you can't depend that Table2 records are always with the same order as Table1 and they are maximum 5.

Borislav Borissov

 

[Stella740pl]

Judi,

It looks like you might use cross-tab. Check cross-tab wizard in VFP Help, and also search this forum and General Coding Issues forum on key words cross-tab or _GENXTAB. You will find plenty. As a sample, take a look at thread184-673445.

Also, depending on your structure, you might come up with other solutions, not involving cross-tab. Here is one oof the alternative solutions: thread184-648475.

 

[Judi201]

Borislav, Jim:
Thanks for the quick response. I know my question was not clear. I will try again.
TABLE ONE : JOBID, ...........
TABLE TWO : JOBID, FRAMEID, PARTINSTOCK
aabbc, xx1, 35
aabbc, xx2, 20
aabbc, xx3, 0
There may be as many as 5 like this but cannot be more.

My report must show:
JOBID ..... 35, 20, 0, , for each JOBID

I don't know if this is any clearer.

I'm sorry but I can't quite follow Jim's code

* First, create an empty cursor with all the fields you will need for the report
CREATE CURSOR OutPut (.....)

This is what I just can't get my mind around.

I know that I have to pick up one field from each related record and stick it in the report cursor, but I just don't get how.

Hope this clarifies some.

Judi

 

[Stella740pl]

Judi,
this is much clearer, but this is the way I understood you the first time.

As I said above, cross-tab query would suit your needs in general, for this type of report.
But, for your particular case, you might go with the following:

SELECT	a.JobId,	a.OtherFields, ;
   SUM(IIF(FrameId="xx1", PartsInStock, 0000)) AS Frame_xx1, ;
   SUM(IIF(FrameId="xx2", PartsInStock, 0000)) AS Frame_xx2, ;
   SUM(IIF(FrameId="xx3", PartsInStock, 0000)) AS Frame_xx3, ;
   SUM(IIF(FrameId="xx4", PartsInStock, 0000)) AS Frame_xx4, ;
   SUM(IIF(FrameId="xx5", PartsInStock, 0000)) AS Frame_xx5  ;
	FROM TableOne a LEFT JOIN TableTwo b ;
	ON b.JobId==a.JobId ;
	GROUP BY 1 ORDER BY 1 ;
	INTO CURSOR MyReport

 

[Judi201]

Thanks Stella!

I will check them out.

Judi

 

[Judi201]

Stella,
Forgive me for being so dense, but are you giving me the SUM of all records relating to JobId? I have got to show the amount in each of the five (possibly) records -- not the total. There is a qty instock for each of the types of frames used with that JobId. (Or maybe none, depending)

I'm embarrased to post this, but I won't get help without doing so.

Thanks for all help.

Judi

 

[Stella740pl]

There is a qty instock for each of the types of frames used with that JobId. (Or maybe none, depending)

So I sum them up for each frame type separately! Did you notice the IIF() within the SUMs - five separate SUMs, not one? If there are none, then you would get 0 in the column for that particular type of frame. Or you have more than five types of the frames and don't know which ones you would see for each of the IDs?

In any case, did you run the SELECT and checked if it gives you what you are looking for?

And did you check cross-tab and a sample of it in the first tread I linked? Does it look like what you need?

 

[Judi201]

Stella,
I have to be away for a few minutes. Let you know as soon as I can finish testing. Thanks so much for your time.
Judi

 

[Judi201]

Stella,
This is the modified code I am trying. Get a unrecognized command error. I am sure I have something wrong because I was not reading it correctly.

SELECT  jobCores.jobsnumb, jobCores.partnumb, jobCores.custno,  ;
				jobs.partstoc, jobs.partproc, ;
			   	SUM(IIF(coreno =1, corestk, 0000)) AS corestk1 , ;
			   	SUM(IIF(coreno =2, corestk, 0000)) AS corestk2 , ;
			   	SUM(IIF(coreno =3, corestk, 0000)) AS corestk3 , ;
			   	SUM(IIF(coreno =4, corestk, 0000)) AS corestk4 , ;
			   	SUM(IIF(coreno =5, corestk, 0000)) AS corestk5 , ;
			   	FROM jobs LEFT JOIN jobcores ;
	   		   	ON jobs.jobsnumb == jobcores.jobsnumb ;
	   		   	GROUP BY 1 ;
	   		   	ORDER BY 1 ;
	   			INTO CURSOR InvRept

If you see my error or can suggest something to try, I will appreciate it. I would like to do it this way and not try the Cross-tabs. (save that for when I am more experienced!)

Thanks so much.

Judi

 

[JimWWinter]

Judi,

At least one problem is the comma after the last field in the SELECT statement. I don't see any others.

Regards,
Jim

 

[Judi201]

WOW,
Yes, that did it. The last comma got there by cut-n-pasting.
I see data in all fields, will have to check accuracy but I noticed -2 in one column. Any idea how that could come about?

Stars to Stella and for Jim's quick eye.

Thanks all.
Judi

 

[Stella740pl]

I see data in all fields, will have to check accuracy but I noticed -2 in one column. Any idea how that could come about?

I can't think of anything except of a negative number in one of the original records, for whatever the reason.

(Corrupted table? Automatical subtraction of more parts sold(or whatever) than you have had to start with?)

 

[Judi201]

Stella,
You are right in that several negative numbers have crept into my sample data. Everything checks out and I have marked off a big ?? on my to do list. Thanks so much.

Jim: Thanks for following and catching my carelessness. Looking at your code made me realize something that I had not known I could do so it will help down the line. Many thanks.

Borislav: Thanks for following. You have helped me many time.

Judi