Create crosstab query without a intermediate table

[tmartin]

The enclosed code is a prototype that first creates an intermediate table called temp and then used by the crosstab query. I have enclosed the SQL. Is there anyway
to use a UNION and eliminate the need for two separate queries with a temporary table?

Thanks
TOm

[CJ_London]

If you convert your make table query to a select query. you could just use this as the source for the crosstab which eliminates the need for a temp table, or you could just convert the second query into a crosstab query.

TRANSFORM Sum([Cost]+[Service Charge]) AS SumOfTotal
SELECT Employees.FirstName
FROM Employees INNER JOIN [Transaction] ON Employees.EmployeeID = Transaction.EmployeeID
WHERE (((Transaction.Date) Between [Forms]![PreReport]![startDate] And [Forms]![PreReport]![endDate]))
GROUP BY FirstName
PIVOT [Type];

Note that type is a reserved word, using it can have unexpected consequences, better to change the name to something else

[tmartin]

I manually tried inserting your SQL in SQL view and did get errors. I put together this prototype that you run from the Form
If you specify a date range it will generate a report but has the intermediate steps. If this helps let me know

Tom

[CJ_London]

TRANSFORM Sum([Cost]+[Service Charge]) AS Total
SELECT Employees.FirstName
FROM Employees INNER JOIN [Transaction] ON Employees.EmployeeID = Transaction.EmployeeID
WHERE (((Transaction.Date) Between [Forms]![PreReport]![startDate] And [Forms]![PreReport]![endDate]))
GROUP BY Employees.FirstName
PIVOT Transaction.Type;

this works except that it cannot find startdate and enddate in your prereport form - you have called them something else