Grid Horizontaly

[Jomercat]

Hi all,

Hope somebody can help.

I have a grid on a form which is displaying the records from a query.

The query retrieves a one to many relation. In other words, I have a table which has the number of departments, which will always have the same number of records and another table which stores multiple records per department.

The query is as follows:

Select table1.Dept, table2.field1
from table1
inner join
table2 on table1.id=table2.dept_id

What I want to do is populate the grid horizontaly so that there will only be a row per department.

Department Week1 Week2
Dept1 field1 field1
Dept2 field1 field1
Dept3 field1 field1

Any help would be appreciated.

Jose.

 

[bborissov]

SELECT Dept,;
       SUM(IIF(Week1,Table2.Field1,00000000.00)) AS Field1,;
       SUM(IIF(Week2,Table2.Field1,00000000.00)) AS Field2;
FROM Table1;
INNER JOIN Table2 ON Table1.Id == Table2.Id;
GROUP BY Table1.Dept;
INTO CURSOR crsGrid

Borislav Borissov

 

[TamarGranor]

I assume the number of columns you need to create varies with the data. In that case, what you need is a cross-tab. First, run a query that selects exactly three columns, one containing the data for the first column (in your case, departments), the second containing the data that determines the other columns (in your case, the weeks) and the third containing the actual data that should go in the "cells."

Then, call VFPXTAB, which is in the home directory.

Tamar

 

[Jomercat]

bborissov and TamarGranor,

Thanks both of you for the reply!!

I'll try your suggestions.

Thanks.

Jose.