Need to create a crosstab query

[George W. Jackson]

I need a crosstab query that will have

1. EMPLOYEE as the ROW TITLE
2. FRIDAY as the COLUMN HEADINGS
3. For the VALUE: a function that will display the PROJECT field in the
crosscells. A form will pull this query up. On the form will be two date
fields - ending & starting - & these fields should be the time frame for
the query. Also, on the form, there will be a PROJECT field - which is what
this crossquery should be based on, as well.

I really need this so if anyone is willing to help I would be greatful.

Thanks in advance,

George W. Jackson

 

[[MVP] S. Clark]

A crosstab is typically used to show a summation between the row and column
headers. I don't think that is what you are asking. If you wanted the
count of Projects, or the sum of Project hours, that would make sense, but
just the project field itself seems incorrect.

Maybe more explanation is needed.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[George W. Jackson]

OK, I have been gathering that idea about crosstabs lately, wasn't for sure.
Here is what I am aiming for to begin with.

I need a report that will take all the Fridays of each week that is
specified on the form (starting & ending) and put each of these Fridays as
the Column Headers. Next I need the rows to be all the Employees that
working in those dates. And for the cross cells I need to project that that
employee(row) worked on for that week(column). The only issue here is
sometimes an employee can work on more than one project in one week, making
it so that there will be more than one result in the same cross cell.

Please let me know if you need more info.

Thanks in advance,

George Jackson

 

[George W. Jackson]

I have a cross query that does not work because of type mismatch errors.

My crossquery has this for the SQL:

TRANSFORM Last(FillFryday([fryday],[employeeID])) AS function
SELECT projectfrydays.employeeID
FROM projectfrydays
GROUP BY projectfrydays.employeeID
PIVOT projectfrydays.project;


The "FillFryday" function is this and the line that gets highlighted each
time the mismatch error is marked:

Public Function FillFryday(selFryday As Date, selEmp As Integer) As String
Dim dbs As Database, rst As Recordset
Dim recnum, i, j As Integer
Dim SQLSTRING As String, DATESTRING, fillstring As String


If IsNull(selFryday) Then GoTo EndF:
DATESTRING = Month(selFryday) & "/" & Day(selFryday) & "/" &
Year(selFryday)
SQLSTRING = "SELECT DISTINCT projectfrydays.project FROM projectfrydays
WHERE ((fryday = #" & DATESTRING & "# AND employeeID = " & selEmp & " ));"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLSTRING) *****************THIS IS THE
ERROR LINE
rst.MoveLast
rst.MoveFirst
recnum = rst.RecordCount
'MsgBox recnum
fillstring = rst.Fields(0)
If recnum = 1 Then GoTo Cont01
For i = 1 To recnum - 1
rst.MoveNext
fillstring = fillstring + Chr$(10) + rst.Fields(0)
Next i

Cont01:
FillFryday = fillstring
rst.Close
Set dbs = Nothing

EndF:
End Function





This function is supposed to include more than one entry in a cell. I hope
you or someone can help.

Sincerely,

George Jackson