report layout

[shersu]

I have 2 tables in Access. I use VB to view a report in Crystal Report.

Table1: StaffMaster

EmpCode
EmpName

Table2: StaffHrs

EmpCode
Rfs
Project
Month_Year
ATH

I wish to display a report in Crystal Report as follows:

EmpCode | EmpName | Rfs | Prject | Month_Year | ATH

How do I accomplish this? Can this be done using crosstab query?

The criteria is for Month_Year. I tried using Crosstab query, but I have problems with the date format. The date should be displayed on the report as mmmm yyyy format. Can it be done using the following query?

"SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project, StaffHrs.Month_Year, " & _
"StaffHrs.ATH FROM StaffMaster LEFT OUTER JOIN StaffHrs ON StaffMaster.EmpCode=StaffHrs.EmpCode " & _
"WHERE StaffMaster.EmpCode=StaffHrs.EmpCode AND StaffMaster.Discipline='Architecture' AND " & _
"StaffHrs.Month_Year BETWEEN #July 2004# AND #September 2004#"

If so, how will I display the fields in the appropriate places?

Thanks

[shersu]

I landed up in the crosstab query, without the date criteria, which I think will give me the result I want. I ignored the date criteria, so that I can retrieve all the records in the StaffMaster table, which is what I want. But one problem is that, the recordset contains all the dates that are there in the StaffHrs table. I would like to find out, if the Month_Year field of the recordset is the same as that I want, and then display it into a field. The Month_Year field is not fixed, as it depends on the user's selection. So it cannot be specified in the code. I know this can be done with formula fields. But how?

This is the crosstab query:

strsql = "TRANSFORM Sum(StaffHrs.ATH) AS SumOfATH " & _
"SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project " & _
"FROM StaffMaster LEFT JOIN StaffHrs ON StaffMaster.EmpCode = StaffHrs.EmpCode " & _
"Where (([StaffMaster]![Discipline] = 'Architecture')) " & _
"GROUP BY StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project " & _
"ORDER BY StaffMaster.EmpName " & _
"PIVOT StaffHrs.Month_Year"

The Field Count is 18. But this may also vary depending on the Discipline that the user selects.

If, for example, the Discipline is Architecture, then the field count is 18. I did a looping just to see what the fields are. This is the code:

For I = 1 To Report.Database.Tables(1).Fields.Count
K = Report.Database.Tables(1).Fields(I).DatabaseFieldDisplayName
MsgBox K
Next I

The result I got is as follows:

<>
01/01/05
02/01/05
03/01/05
04/01/05
05/01/05
05/01/04
06/01/04
07/01/04
08/01/04
09/01/04
10/01/04
11/01/04
12/01/04
EmpCode
EmpName
Project
RFS

If the result is this, how will I find out the appropriate data for the corresponding date and display it in the corresponding fields? The user needs to view the data for 6 months from the month he selects.

Thanks