Query join

[fat controller]

I have two queries, separately they work fine, but I would like to join them somehow for a report - yet they have no shared fields.

The SQL for the first:

SELECT UnfitVehicles.ID, UnfitVehicles.[Bonnet Number], UnfitVehicles.DateUnfit, UnfitVehicles.ReasonUnfit, UnfitVehicles.ExpectedReturnDate, UnfitVehicles.VehicleType, UnfitVehicles.ActualReturnDate, UnfitVehicles.CurrentStatus, UnfitVehicles.StatusReason, UnfitVehicles.VehicleType, UnfitVehicles.MadeUnfitBy, UnfitVehicles.DeclaredFitBy, UnfitVehicles.PartsAwaited
FROM UnfitVehicles
WHERE (((UnfitVehicles.ActualReturnDate) Is Null));

And the SQL for the second:

SELECT DCount("TypeRequired","AllocatedVehiclesToday","TypeRequired='" & "Double Deck" & "'") AS Expr1, 

DCount("TypeRequired","AllocatedVehiclesToday","TypeRequired='" & "Single Deck" & "'") AS Expr2, 

DCount("TypeRequired","AllocatedVehiclesToday","TypeRequired") AS Expr3, 

DCount("VehicleType","AllocatedVehiclesToday","VehicleType='" & "Double Deck" & "'") AS Expr4, 

DCount("VehicleType","AllocatedVehiclesToday","VehicleType='" & "Single Deck" & "'") AS Expr5, 

DCount("VehicleType","AllocatedVehiclesToday","VehicleType") AS Expr6, 

DCount("VehicleType","CallOverUnfits","VehicleType='" & "Double Deck" & "'") AS Expr7, DCount("VehicleType","CallOverUnfits","VehicleType='" & "Single Deck" & "'") AS Expr8, 

DCount("VehicleType","Live_Vehicles","VehicleType='" & "Double Deck" & "'") AS Expr9, 

DCount("VehicleType","Live_Vehicles","VehicleType='" & "Single Deck" & "'") AS Expr10;

I have broken the second one up a bit lest I inflict blindness on anyone :biggrin:

Essentially, I am wanting the data from the second query in the page header of a report, and the rest is in the detail of the report.

I have tried opening the query for the report and pulling both queries in, however that throws up an error when I try and run the query "Query input must contain at least one table or query"

Is there a way to bring peace and harmony?

 

[plog]

My suggestion for the issue you posted is to use a sub-report. Build a report using the 2nd query, then bring it in as a subreport into the other which would be based on the 1st query.

Now, for the issue you don't know you have: That second query is horrible inefficient, technically, its not even a query (No FROM clause). Heck, you could use all those DCounts statements in the header of your report instead of a subreport or even referencing that 2nd query.

Are all the datasources of those DCounts tables (AllocatedVehiclesToday, Live_Vehicles, etc.) ? If so, that's an improperly structured database--you shouldn't store information in table or field names. If not and those are queries, then you probably could make a more eficient query to get that data instead of looking at 3 diferent queries with those DCounts.

 

[fat controller]

Thanks

The DCount datasources are queries, but if there is a more efficient way of doing things, I am always open to learn.

I have just tried to create a report based on the second query (to then pop into the main report as a subreport), and it is that second query that is throwing the error up, as it does so even on its own.

All advice gratefully received

 

[plog]

Can you post your database?

 

[fat controller]

Afraid not - one, its pretty massive, and two there is quite a lot of sensitive stuff in there that I would get shot for if it was released into the public domain.

I took your advice though, and just put the DCounts into the text box on the report and it works a treat

Thank you