Update query

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

ladybug via AccessMonster.com

I have two tables. The first table has program names (Program) as it's first
field. It stores other program info in it as well.
In the second table it has the program names again and the second field is
phone #'s (Phone).

I want to be able to run a query so that any programs that are in the first
table, but not yet in the second table will get added to the second table.
Along with the program name I would like (000)000-0000 added as it's phone #.

Can someone walk me through how to make this update query?
 
If Program is unique (I.E. a key field) in both tables, create an append
query to add records from table2 to table1 along with a static field: (000)
000-0000. Since the field is unique, duplicates will be ignored and only new
data will be allowed.

If the data is not unique, do you want programs to be added multiple times to
table2 if it appears more than once in table1? You can use the query wizard
to build an unmatched query to find data that exists in one table but not
another. Then you can use those results in an append query to add to the
table without the data.
 
Program is unique. Can you explain in more detail how to do the update query?
What do I type in for this "static field"
I know that I need to do an update query I just have never done one before...
sorry :)
If Program is unique (I.E. a key field) in both tables, create an append
query to add records from table2 to table1 along with a static field: (000)
000-0000. Since the field is unique, duplicates will be ignored and only new
data will be allowed.

If the data is not unique, do you want programs to be added multiple times to
table2 if it appears more than once in table1? You can use the query wizard
to build an unmatched query to find data that exists in one table but not
another. Then you can use those results in an append query to add to the
table without the data.
I have two tables. The first table has program names (Program) as it's first
field. It stores other program info in it as well.
Click to expand...
[quoted text clipped - 6 lines]
Can someone walk me through how to make this update query?
Click to expand...
Click to expand...
 
Is the field property set to unique? Open table2 in design mode and look at
the [Program] field's Indexed property. Is it set to Yes (No Duplicates)?
If so, create a query on table1 and make it an append query (menu option
Query -> Append Query...). The data will be appended to table2. Select the
field [Program] from table1 in the output pane at the bottom, and if the
field names are the same in both tables, the Append To: line will be
automatically filled in. Otherwise, make the proper selection by choosing
the correct field in table2. For the telephone number, simply type (000)...
in the next Field: box and select the correct Append To: field. Make a
backup of table2 just in case and run the query.
Program is unique. Can you explain in more detail how to do the update query?
What do I type in for this "static field"
I know that I need to do an update query I just have never done one before...
sorry :)
If Program is unique (I.E. a key field) in both tables, create an append
query to add records from table2 to table1 along with a static field: (000)
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
I'm sorry I thought you asked if the Program was unique in the first table.
It IS NOT unique in the second table. A program can have multiple phone #'s
that is why I cannot make it unique. What do I do in this instance. If I
were to append it currently it would try to load all the programs again. I
just want the one's that are in the first table, but not the second table to
be added in the second table.

Thank you so much for giving me a step by step explanation. It makes so much
more sense now. How do I do the unmatched query that you had mentioned
before?
Is the field property set to unique? Open table2 in design mode and look at
the [Program] field's Indexed property. Is it set to Yes (No Duplicates)?
If so, create a query on table1 and make it an append query (menu option
Query -> Append Query...). The data will be appended to table2. Select the
field [Program] from table1 in the output pane at the bottom, and if the
field names are the same in both tables, the Append To: line will be
automatically filled in. Otherwise, make the proper selection by choosing
the correct field in table2. For the telephone number, simply type (000)...
in the next Field: box and select the correct Append To: field. Make a
backup of table2 just in case and run the query.
Program is unique. Can you explain in more detail how to do the update query?
What do I type in for this "static field"
Click to expand...
[quoted text clipped - 6 lines]
Click to expand...
 
Create a new query and select Find Unmatched Query Wizard. Access will then
guide you through the steps. Basically, what you end up with is table1
joined to table2 on [Program] where all of the records from table1 are
displayed and the criteria for table2 is that [Program] Is Null.
I'm sorry I thought you asked if the Program was unique in the first table.
It IS NOT unique in the second table. A program can have multiple phone #'s
that is why I cannot make it unique. What do I do in this instance. If I
were to append it currently it would try to load all the programs again. I
just want the one's that are in the first table, but not the second table to
be added in the second table.

Thank you so much for giving me a step by step explanation. It makes so much
more sense now. How do I do the unmatched query that you had mentioned
before?
Is the field property set to unique? Open table2 in design mode and look at
the [Program] field's Indexed property. Is it set to Yes (No Duplicates)?
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
THANK YOU SO MUCH!!!!!!!!!!! YOU HAVE HELPED ME OUT TREMENDOUSLY!
Have a great day!
Create a new query and select Find Unmatched Query Wizard. Access will then
guide you through the steps. Basically, what you end up with is table1
joined to table2 on [Program] where all of the records from table1 are
displayed and the criteria for table2 is that [Program] Is Null.
I'm sorry I thought you asked if the Program was unique in the first table.
It IS NOT unique in the second table. A program can have multiple phone #'s
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Back
Top