Count number of records in SQL query

[IanHem]

Can anybody tell me how to count the number of records produced by this query?

SELECT Tbl_Vehicles.VehicleID, Tbl_Makes.Make, Tbl_Models.Model,  Tbl_Operators.Operator, Tbl_Customer.CustomerName, Tbl_Vehicles.Registration  INTO Temp_TblVehicleSelect FROM Tbl_Models INNER JOIN (Tbl_Makes INNER JOIN (Tbl_Operators AS Tbl_Operators_1  RIGHT JOIN ((Tbl_Vehicles INNER JOIN Tbl_Operators ON Tbl_Vehicles.OperatorId = Tbl_Operators.OperatorID) LEFT JOIN Tbl_Customer ON Tbl_Vehicles.OwnerId = Tbl_Customer.CustomerId) ON Tbl_Operators_1.OperatorID = Tbl_Vehicles.OperatorId) ON Tbl_Makes.MakeId = Tbl_Vehicles.MakeId) ON Tbl_Models.ModelId = Tbl_Vehicles.ModelId WHERE Tbl_Vehicles.FunctionId = 0 AND Tbl_Makes.MakeId = 104 AND Tbl_Models.ModelId = 17 AND Tbl_Vehicles.OperatorId = 1;

Its quite a long query I know but its basically a make table query
If there are no records in the select query I don't want to make a table.

Thanks

 

[plog]

Its quite a long query I know but its basically a make table query

Action queries (MAKE TABLE, UPDATE, DELETE, etc.) are rookie hacks. I question the reason you are making a table and thus this step you want to add to the process. Also, that's a very unkosher query--you've used every type of JOIN available in Access (LEFT, RIGHT and INNER). I really think there's a much better way to reach your end goal than this process you are trying to put together.

Explain the big picture. Why are you moving data around your database? What are you doing with the resulting table?

 

[IanHem]

The query is used in a filter on a form with combos creating the query on the fly.
Then it creates a temporaray table with a check box for the user to select which items need his attention.

 

[MarkK]

UPDATE and DELETE queries aren't rookie hacks.

 

[pbaldy]

Plus one on:

UPDATE and DELETE queries aren't rookie hacks.

Action queries are a valuable tool that might be misused by a rookie.

 

[plog]

UPDATE and DELETE queries aren't rookie hacks.

Search this forum for posts about issues with those types of queries. It's the #2 mark of a poorly structured database. #1 is numerated field names (Item1, Item2, Item3). His reply backs up my intuition as well.

Ian, what you are trying to do sounds like a poor way of doing it. What type of attention does the user do to these records? And how does that get back to your original table?

 

[IanHem]

The user is trying to select a group of vehicles based on there involvement in several other tables on a form, which has a continuous subform.

The user will not be trying to locate specific vehicles, but vehicles that meet specific needs. Of course only the user at runtime knows what these needs are.

When the user has whittled it down to a list, they then choose further from that.

A subform on the above form is bound to the temporary table created by the query, the table has a Y/N field.

Am I choosing a complicated route to achieve this?

 

[plog]

Am I choosing a complicated route to achieve this?

Probably. Instead of using the user supplied criteria in the WHERE clause of a MAKE TABLE query, you could use it as a filter on the form. That way the form would only display the records meeting the users criteria. The temporary table is most likely a needless middleman.

The real key though is what does the user do with that data? Are they editing those records somehow and changing data in the database? Are they simply exporting that data to a spreadsheet to work with outside the system?

 

[IanHem]

The temporary table is just there so that I can have check boxes on the continuous form.

The user selects each vehicle and in the future the database handles them differently to others.

e.g. all red Toyota's owned by Bob Smith will be washed on Tuesdays.


The alteration is made to the Tuesday washing list.

 

[plog]

The user selects each vehicle and in the future the database handles them differently to others.

You haven't explained how the database knows how to do that. So far you've made a temp table and set it off to the side. In your explanation so far, no external data has come into the system, the data has remain unchanged so I fail to see how the database can handle them differently--its ignorant of what the user wants.

Perhaps you can post your database, or at least a screenshot fo the relationaship tool.