Skip to content
Advertisement

How to combine multiple query types in MS Access 2010?

I have a query that is created in VBA and selects column information from two other tables and creates a new table with that information.

Existing Query:

Select Prem.longitude, Prem.latitude,
DataByColl.[MIU ID], DataByColl.[Avg
RSSI], Prem.prem_addr1
Into [FifthAveMeshInput]
From [Prem]
Left
Join DataByColl
ON (Prem.meter_miu_id
= DataByColl.[MIU ID] AND DataByColl.Collector = (“Fifth Ave.”))
ORDER BY DataByColl.[Avg RSSI] desc

I would like to add to this two fields. One called Index that is auto incremented starting at a value of 2 as well as one called MeterType that is a number with all values set to 0. Is it possible to have all of these in one query or would I have to use separate queries to accomplish this? And if I would need to use separate queries which ones would I need and in what order?

Advertisement

Answer

Has to be two steps:

  1. Create the table (you can use SQL
    DDL CREATE TABLE... syntax for
    this).
  2. Populate the table using INSERT
    INTO..SELECT
    .

One issue with the above is that you have to first discover the data types of the columns in your query and use them in the table creation step.

Alternatively:

  1. Create the table using
    SELECT..INTO..FROM.
  2. Alter the table to add the new
    columns.

One issue with the above is that you’d need to populate the auto-increment values yourself (if I am correct in thinking one can’t add the auto-increment property to an existing column), which may actually defeat the object of having an auto-increment column in the first place!

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement