Append Query

[amfroehlich]

I have a query that I want to append to anthor table. But this query only has doesn't have all of the vaules that the table does. Can someone give me a step by step process or an example of an append Query?

 

[Ian Mac]

Hi,

Can you give a little more information please,

What is the setup of each?
Which values are not included in the query, and are they required?

Regards,

 

[amfroehlich]

The query i have contains program names, and costs assoctaed with the programs per quarter. I need to append that information to a table with the following values.

Manufactuer ID (Required) - the query is only by one manufacturer
Year Planned(Required) - the query is only for the current year but i
would like to be able to track history.
Program ID(Required) - this is the autonumber associated with the
program name in my query.
QuarterPlanned(Required) - I should pull this from my quarter one
column,etc...
Program Cost - Part of my Query


Thanks

 

[Ian Mac]

Sorry,

I should have asked where the orginal Query's data is coming from?

To run an Append Query you can open a new Query select the tables where the information is coming from.
Change the Query to a Append Query.
Select the Table to Append to.
Select the fields you need from the Table(s) you prevoiusly selected, the from tables.
Then select the field to Append To from the list.

Edit:

Note: the values for each field must be the same, i.e. Date and Date etc.

Regards,

 

[mdmilner]

The basic syntax of an Append query is:

INSERT INTO table_name ( fld1, fld2, fld3, fld4 )
SELECT field1, field2, field3, field4 FROM source_table_name

The names from the source table do not need to match the names in the destination table, but the first source field does always go into the first destination field listed and, the field types must be compatible. You can't paste a length 10 Text field into a length 5 Text field.

Now, here's the trick. You don't have to insert into all the fields in a table. And, because you're using the names, they can be in any order. fld1 above might actually be the 5th field in the given destination table.

Now, another trick. Lets say you want to paste in a constant at the same time. Four of the destination fields are pulled from a source table, but the fifth field is actually some constant (or to get complex, it could be something based on the source_table_name)

The way that appears is like this:

INSERT INTO table_name ( fld1, fld2, fld3, fld4, fld5 )
SELECT field1, field2, field3, field4, 'value' FROM source_table_name

Here, the text "value" will go into fld5 of table_name.

Now, here's how you can do this easily.

Open up QBE (query by example) and create a generic 'select' query based on your source table with all the fields you'd like to append to the 2nd table. Then change the query type to append. With the query open in design mode, you can use the 'Query' menu or the toolbar icon. After selecting Append, it wants to know the destination table name. At this point the appearance of design mode changes, adding in a row for the 'destination' field name.

Access will attempt to 'guess' what field goes where. If your field names are precisely identical, you should find those values filled in. On the rest, you will have to use the pull-down menus and select it yourself. From here save it. If you execute it, it should run.

That should be a 'basic' append query. All the familiar rules for things like parameters (only update the 2nd table if the value of a field in the 1st table is equal to xxxxx) work.

Now, here's where I digress onto a different topic which may have nothing to do with your current problem.
Are you certain you need an append query?

Sometimes, new users make the assumption that in order to work with data stored in tables by having them in tables. And if you only want a small portion of that data, you have to create and append it to another table. This isn't true.

Going backwards, lets go to the select query (with a parameter) that you were wanting to append to another table. When you run that Select query -- is that all the data you wish to work with elsewhere? Perhaps in a form? Or in a Report? Or perhaps to export it to Excel?

If any of the above is yes, the answer is STOP RIGHT THERE. You can directly export Queries or use them as Recordsources for Forms & Reports exactly the same way you might use a table. There is no need to create another table that clutters your database. Use the query instead.

Mike