Combining crosstab queries

toast · Jul 11, 2014

I've got 2 crosstab queries that both work, but I'd like to combine them together.

The first shows the total time that various vehicles were used for at each base:

Code:

TRANSFORM Sum(Cdbl(Nz(M.dtTripTime, 0))) AS SheetTime

SELECT M.lngVehicleID
    ,Sum(Cdbl(Nz(M.dtTripTime, 0))) AS Total
FROM tblSheets M
INNER JOIN Bases B ON M.lngBaseID = B.BaseID
GROUP BY M.lngVehicleID
PIVOT B.BaseName IN (
        'ALPHA'
        ,'BRAVO'
        ,'CHARLIE'
        );

The second shows the number of trips that each vehicle did at various bases:

Code:

TRANSFORM Count(M.lngSheetID) AS SheetCount

SELECT M.lngVehicleID
    ,Sum(Cdbl(Nz(M.dtTripTime, 0))) AS Total
FROM tblSheets M
INNER JOIN Bases B ON M.lngBaseID = B.BaseID
GROUP BY M.lngVehicleID
PIVOT B.BaseName IN (
        'ALPHA'
        ,'BRAVO'
        ,'CHARLIE'
        );

What I'd ideally like is to generate one crosstab that shows both the combined time and the number of trips for each vehicle at the various bases. Is this possible? The above queries maxed out my very limited abilities on SQL!

I'd like to end up with something like this:

CJ_London · Jul 11, 2014

Fairly straight forward to get the columns.

Simply create a new query to use the existing two queries and joined on lngVehicleID e.g.

Code:

SELECT * 
FROM Query1 INNER JOIN Query2 ON Query1.lngVehicleID=Query2.lngVehicleID 
ORDER BY Query1.lngVehicleID

Then just order the columns as required.

What you won't be able to do is have one heading across two columns in the query, you'll need a form or report to do that - in which case the form recordsource can be the above query.

The form or report will need to be in continuous view. For each paired column, simply delete the label for one of the columns and reposition the other to straddle both columns and centre the text.

You will probably need to edit the label to remove the query name

toast · Jul 12, 2014

Thank you very much - that seems to be working well!

Combining crosstab queries

toast

Registered User.

Attachments

CJ_London

Super Moderator

toast

Registered User.

Similar threads

Users who are viewing this thread