Availability report

I have a table in Access with fields as follows:

EmpCode
Month_Year
Percentage of Availability (PA)

I have another table called StaffMaster which has the following fields:

EmpCode
EmpName
Dept

In the report, I wish to retrieve and display records as follows:

Month1 Month2 Month3 Month4

EmpCode EmpName PA PA2 PA3 PA4

Can this be accomplished? Please help. Urgent!

ThanksTry this query and use labels for Month1, Month2, etc...

SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.PA, 'PA2' = T2.PA, 'PA3' = T3.PA, 'PA4' = T4.PA
FROM StaffMaster AS SM
LEFT JOIN Table1 AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = 0104
LEFT JOIN Table1 AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = 0204
LEFT JOIN Table1 AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = 0304
LEFT JOIN Table1 AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = 0404

This will return all the records in StaffMaster and the matching records in your other table (which I referred to as Table1), it will return a NULL if no records are found in Table1.|||But I don't have 4 tables. I have only 2 tables, where T1 is the StaffMaster table and T2 is the Availability table.|||The query I posted is only based on 2 tables, it just calls Table1 4 times to get each month's data. SM is the StaffMaster table and T1-T4 are all from the Availability Table. In my example, T1 gets January's data (0104), T2 gets February's data (0204), T3 gets March's data (0304), and T4 gets April's data (0404).

Try running the query (substituting the real table names and the values in the Month_Year column) and see if it produces the results you're looking for.|||I tried running the query, but I get this error.

Synatx error (missing operator) in query expression ".

This is how I changed the query.

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.PA, 'PA2' = T2.PA, 'PA3' = T3.PA, 'PA4' = T4.PA " & _
"FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"

Thanks|||What is the data type of the field Month_Year? Maybe your syntax error is in there. If it's a string, try replacing the # with a single quote(').

If you want to post a few rows of sample data from your table, I might be able to help you further.|||The data type of Month_Year is date. Hence the #.

This is from the StaffMaster Table

EmpCode| EmpName| Designation| Discipline |Status

X059| $Dummy-SE1| Structural Engineer| Structure| A|
X060| $Dummy-SE2| Structural Engineer| Structure| A|
X061| $Dummy-SE3| Structural Engineer| Structure| A|
X062| $Dummy-SD1| Structural Drafter| Structure| A|
X063| $Dummy-SD2| Structural Drafter| Structure| A|
X064| $Dummy-SD3| Structural Drafter| Structure| A|
X065| $Dummy-SE1| Specifications Engineer| Specifications| A|

This is from the Availability Table

Empcode| Discipline| Month_Year| Avail| AvailDate| Id|

X059| Structure| June 2004| 0| 01 July 2004| Admin|
X059| Structure| July 2004| 0| 01 August 2004| Admin|
X059| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| July 2004| 0| 01 August 2004| Admin|
X060| Structure| June 2004| 0| 01 July 2004| Admin|
X060| Structure| July 2004| 48| 17 July 2004| Admin|
X060| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| September 2004| 48| 11 September 2004| Admin|
X060| Structure| October 2004| 0| 01 November 2004| Admin|
X060| Structure| November 2004| 0| 01 December 2004| Admin|
X060| Structure| December 2004| 24| 04 December 2004| Admin|
X060| Structure| January 2005| 46| 01 January 2005| Admin|
X061| Structure| June 2004| 0| 01 July 2004| Admin|
X061| Structure| July 2004| 0| 01 August 2004| Admin|
X061| Structure| August 2004| 0| 01 September 2004| Admin|
X062| Structure| August 2004| 7| 21 August 2004| Admin|

Thanks|||You need to replace the PA's after SELECT with the actual name of the column that you use to represent 'Percentage of Availability' (per your first post).

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.{ColumnName}, 'PA2' = T2.{ColumnName}, 'PA3' = T3.{ColumnName}, 'PA4' = T4.{ColumnName} " & _
"FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"|||I replaced the PA after the Select with the actual name of the column. This is how it is now.

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.{Avail}, 'PA2' = T2.{Avail}, 'PA3' = T3.{Avail}, " & _
"'PA4' = T4.{Avail} FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"

When I run this query, I get this error.

Malformed GUID. in query expression ''PA=T1{Avail}'.|||Thanks malleyo for the reply. I got it to work. The code is below:

strsql = "TRANSFORM Sum(Availability.Avail) AS SumOfAvail " & _
"SELECT StaffMaster.EmpCode, StaffMaster.EmpName " & _
"FROM StaffMaster INNER JOIN Availability ON StaffMaster.EmpCode = Availability.Empcode " & _
"WHERE StaffMaster.Discipline='Architecture' " & _
"AND StaffMaster.Designation<>'Project Manager' " & _
"AND StaffMaster.Designation NOT LIKE 'Head%' " & _
"AND StaffMaster.Designation NOT LIKE '%Manager%' " & _
"AND StaffMaster.Designation<>'Designer' " & _
"AND StaffMaster.EmpCode NOT LIKE 'X%' " & _
"AND Availability.Month_Year BETWEEN #May 2004# AND #October 2004# " & _
"GROUP BY StaffMaster.EmpCode, StaffMaster.EmpName " & _
"ORDER BY StaffMaster.EmpName " & _
"PIVOT Availability.Month_Year"

R0.Open strsql, Cn, adOpenDynamic, adLockReadOnly

Report.DiscardSavedData
Report.Database.Tables.Add "", , R0

Here I have one problem more to be solved. I hope it can be solved. The problem is that , if the database table does not have a record for that month, it displays blank in the report for that month. Instead of displaying blank, I wish to display it as 100. Can this be accomplished? Please help.

Thanks|||Any help please...|||Use a Conditional Suppress on that field.|||You mean this?

crField.ConditionFormula(crEnableSuppressConditionFormulaType) = "If (IsNull(Report.Database.Tables(1).Fields(1).Name)) then 100"

If so, when the code is executed, it gives a message saying

'The ) is missing'

Thanks|||Try creating a Formula instead of using the Conditional Suppress. I think I read your last post too quickly, I don't think the Conditional Suppress will work in this case.

If you're creating you report files with the designer in VB, right-click on the word 'Formula' in the list on the Left and Click 'New'. Give it a Name. Type something similar to this:

If {FieldName} = NULL Then
100
Else
{FieldName}

I don't know the proper syntax for If statements in Crystal Reports, so you'll have to look it up. Once you get the Format correct, drop the Formula field into place where you have your field (delete the field).|||Try this,

crField.ConditionFormula
(crEnableSuppressConditionFormulaType) = "If IsNull(Report.Database.Tables(1).Fields(1).Name) = True then 100"|||Originally posted by harmonycitra
Try this,

crField.ConditionFormula
(crEnableSuppressConditionFormulaType) = "If IsNull(Report.Database.Tables(1).Fields(1).Name) = True then 100"

This gives me an error.

'The ) is missing.'

Thanks|||Originally posted by malleyo
Try creating a Formula instead of using the Conditional Suppress. I think I read your last post too quickly, I don't think the Conditional Suppress will work in this case.

If you're creating you report files with the designer in VB, right-click on the word 'Formula' in the list on the Left and Click 'New'. Give it a Name. Type something similar to this:

If {FieldName} = NULL Then
100
Else
{FieldName}

I don't know the proper syntax for If statements in Crystal Reports, so you'll have to look it up. Once you get the Format correct, drop the Formula field into place where you have your field (delete the field).

But I am creating every object in the crystal report with code. Also, adding the database into the report too with code. Everything is done on the fly.

Thanks