How to use Append Queries to add new records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone, thanks for all the help you've given me so far. I now have a
new problem I haven't been able to wrap my head around.

I have a table of updates with a record for each update for each computer.
So rigth now I have five records for each computer in my Updates table. In
my previous question I was advised to use apped queries to add many records
all at once (one record per computer whenever a new update is to be
installed). However, after sifting through other threads here and the Access
help on creating append queries, I still have no idea how to do this.

Perhaps my problem is that I do not understand HOW append queries work. Can
someone help explain them here in my context? The Access help says "Create a
query that contains the table whose records you want to append to another
table." This is hard to wrap my head around since I have a table of
computers, but how am I going to be able to set the update name and date and
set the default completion check to No?

All help and advice is greatly appreciated,
-Aaron
 
Hi Aaron,

If designing the query in design view, you will add the table of computers
to the new query, then change the query type to append and then specify the
destination query. The query design grid will then change such that you
designate the field source values on the top row, and you specify the
destination field a few rows below. Double Click the field in the computers
table with the table ID's that you want to add, and then specify the
destination field name that you want to append those values to. This will
cause the append query to generate one record for each computer.

For the other fields, the values will be fixed rather than coming from a
table. Look up ID for the update that you want to append (you will need to
enter the update in the lookup table recording available updates first of
course). Manually type the UpdateID in the top row of a blank field. Access
will then enter Expr1: before the value and take the value to be an
expression. Then, specify the destination field name in the appropriate row
below.

As far as setting the default status to false, this will automatically be
the case unless you have set the default property of that field to True in
the destination table design. So, you don't have to include anything for
that field in the append query.

The final result will be a query that will append a record for each
computer, enter each computer ID, enter a constant for the Update ID (same
value for each record), and by default the field recording the update status
will be false.

It is always a good idea to make a backup of your database prior to testing
out new action queries.

Hopefully that will help. Post back if you run into any problems or have
further questions.

-Ted Allen
 
p.s., regarding the question of how append queries work, they basically take
what would have been the result of a select query, and append each of the
fields from that result to the fields that you have mapped them to in the
destination table.

In your case, what you wanted to append was a record for each Computer ID,
as well as a constant for the Update ID. So, the select statement to provide
this recordset would be to select the Computer ID field from your table of
computers, and an expression that is equal to the Update ID that you want to
include for each record. Changing that to an append query just gives you the
ability to set the destination table and fields.

By the way, one other thing that I forgot to mention. When you are in
design view of an action query (such as an append query), you can preview
what the results will be without actually executing the action by clicking
the normal query view button. To actually run the query action, you have to
click the button with the exclamation point, or just close the query and then
double-click it in the query window.

One other thing that you could consider in the future, would be to replace
the manual entry value for the Update ID with a DMax() function or a subquery
to automatically add the highest Update ID from your lookup table (since the
highest value would be the latest entry if using an increment type ID).

Or, you could also consider adding both the computer table and the update
table to the append query - with no joins. The output of this would be a
cartesian product which would append every combination of Computer ID's and
Update ID's to the join table. Then, there are two ways that you could
prevent adding duplicate data to the join table. If the join table is
properly indexed, it should be keyed, or at least have a unique index, on the
combination of ComputerID and UpdateID. In that case, any duplicates would
automatically be rejected and only those not already in the table would be
added. The other way to limit the appends to the appropriate records
(although you would still want to have the table index) would be to set
criteria on the UpdateID field to something like:

Not In (SELECT YourUpdateIDField FROM YourJoinTable)

This would result in only adding the combinations of any UpdateID's that do
not have any entries in the join table. The advantage or disadvantage of
this method vs just using the index to reject duplicates, is that if you add
a new computer to the computers table, a record would not be created in the
join table for old updates that had already been appended in the past. In
other words, if using the indexes to reject duplicate records, if you add a
new computer to the system the append query would generate a record for every
single update in the updates table, but many of those would be old and
probably would not apply to the new computer.

Or, If you wanted to manually specify multiple ID's to be added, you could
change the criteria to something like:

In (100,101,102)

Where you would just make a list of the update id's that you wanted to append.

Hopefully this will give you some different ideas to try to optimize your
future process. I would recommend doing at least one with the manual id
entry to see how it works, but you may want to look at the slightly more
advanced methods to optimize the future process. Feel free to post back if
you have any questions.

-Ted Allen
 
Thanks a lot, Ted! I sort of knew how the append query would work, but it
didn't make sense before because I did not have a table of "available"
updates. I now have the table for available updates and a table of
UpdateIDs, AssetTags, and Completion checks that is easily queried to show
the information from the Computers table. The append query works fine for
now, because all I have to do is change the number in the append query and
re-run it. However, sooner or later I'll try the subquery method you
mentioned.

Once again, thanks for the advice!
-Aaron
 
My pleasure Aaron, glad it helped.

Aaron Cooper said:
Thanks a lot, Ted! I sort of knew how the append query would work, but it
didn't make sense before because I did not have a table of "available"
updates. I now have the table for available updates and a table of
UpdateIDs, AssetTags, and Completion checks that is easily queried to show
the information from the Computers table. The append query works fine for
now, because all I have to do is change the number in the append query and
re-run it. However, sooner or later I'll try the subquery method you
mentioned.

Once again, thanks for the advice!
-Aaron
Click to expand...
 
Back
Top