how do i ADD UPDATED DATA TO AN EXISTING TABLE?

  • Thread starter Thread starter massa
  • Start date Start date
M

massa

I am new at access and can use some help. I have an existing table with 3
fields in it. I need to add field 2 and field 3 data into the existing
table.My table is set up like this:
Field 1 Field 2 Field 3
10002121 10003232 22.00
10003232 10002121 32.00
10004242 10004242 42.00
The numbers in field 1 and 2 is a product id code. I need to combine the two
fields, delete all duplicates. The dollar amount in field 3 is the amount
associated with the product id in field 2. I am trying to update my data from
1/31/2008 (field 1) with the updated 3-31-2008 data fields 2 and 3.
Please help!!!
 
massa,
if I'm understanding your setup, I would do it like this.

TableMain with 2 fields
ProductCode and Amount
10002121 22.00
10003232 32.00
10004242 42.00

TableU with updates
ProductCode and Amount
10003232 22.00
10002121 32.00
10004242 42.00

Create a new query, drag both the TableMain and TableU onto the query
builder.
Create a join on the field ProductCode
Change it to an Update query
In the field for Amount in TableMain, in the update to: row put
[TableU].[Amount]
Run the query.

TableMain will now show the updated amount for each product code.


Jeanette Cunningham
 
Thank you Jeanette, This did work but the problem that occurred- if the
amount was 0.00 it did not include. How would I keep 0 null values as well.
Thanks

Jeanette Cunningham said:
massa,
if I'm understanding your setup, I would do it like this.

TableMain with 2 fields
ProductCode and Amount
10002121 22.00
10003232 32.00
10004242 42.00

TableU with updates
ProductCode and Amount
10003232 22.00
10002121 32.00
10004242 42.00

Create a new query, drag both the TableMain and TableU onto the query
builder.
Create a join on the field ProductCode
Change it to an Update query
In the field for Amount in TableMain, in the update to: row put
[TableU].[Amount]
Run the query.

TableMain will now show the updated amount for each product code.


Jeanette Cunningham


massa said:
I am new at access and can use some help. I have an existing table with 3
fields in it. I need to add field 2 and field 3 data into the existing
table.My table is set up like this:
Field 1 Field 2 Field 3
10002121 10003232 22.00
10003232 10002121 32.00
10004242 10004242 42.00
The numbers in field 1 and 2 is a product id code. I need to combine the
two
fields, delete all duplicates. The dollar amount in field 3 is the amount
associated with the product id in field 2. I am trying to update my data
from
1/31/2008 (field 1) with the updated 3-31-2008 data fields 2 and 3.
Please help!!!
Click to expand...
Click to expand...
 
massa,
can you give some examples of the 0 or null values that didn't work.
Were in TableMain or TableU?

Jeanette Cunningham



massa said:
Thank you Jeanette, This did work but the problem that occurred- if the
amount was 0.00 it did not include. How would I keep 0 null values as
well.
Thanks

Jeanette Cunningham said:
massa,
if I'm understanding your setup, I would do it like this.

TableMain with 2 fields
ProductCode and Amount
10002121 22.00
10003232 32.00
10004242 42.00

TableU with updates
ProductCode and Amount
10003232 22.00
10002121 32.00
10004242 42.00

Create a new query, drag both the TableMain and TableU onto the query
builder.
Create a join on the field ProductCode
Change it to an Update query
In the field for Amount in TableMain, in the update to: row put
[TableU].[Amount]
Run the query.

TableMain will now show the updated amount for each product code.


Jeanette Cunningham


massa said:
I am new at access and can use some help. I have an existing table with
3
fields in it. I need to add field 2 and field 3 data into the existing
table.My table is set up like this:
Field 1 Field 2 Field 3
10002121 10003232 22.00
10003232 10002121 32.00
10004242 10004242 42.00
The numbers in field 1 and 2 is a product id code. I need to combine
the
two
fields, delete all duplicates. The dollar amount in field 3 is the
amount
associated with the product id in field 2. I am trying to update my
data
from
1/31/2008 (field 1) with the updated 3-31-2008 data fields 2 and 3.
Please help!!!
Click to expand...
Click to expand...
Click to expand...
 
massa,
maybe your table has a default value of 0 for the amount field.
In A2000 to A2003, when you create number and currency fields, access
automatically puts a default value of 0.
Open the table in design view and delete the default value.
Now try your update query and see if that gives you the results you want.

Jeanette Cunningham


massa said:
Thank you Jeanette, This did work but the problem that occurred- if the
amount was 0.00 it did not include. How would I keep 0 null values as
well.
Thanks

Jeanette Cunningham said:
massa,
if I'm understanding your setup, I would do it like this.

TableMain with 2 fields
ProductCode and Amount
10002121 22.00
10003232 32.00
10004242 42.00

TableU with updates
ProductCode and Amount
10003232 22.00
10002121 32.00
10004242 42.00

Create a new query, drag both the TableMain and TableU onto the query
builder.
Create a join on the field ProductCode
Change it to an Update query
In the field for Amount in TableMain, in the update to: row put
[TableU].[Amount]
Run the query.

TableMain will now show the updated amount for each product code.


Jeanette Cunningham


massa said:
I am new at access and can use some help. I have an existing table with
3
fields in it. I need to add field 2 and field 3 data into the existing
table.My table is set up like this:
Field 1 Field 2 Field 3
10002121 10003232 22.00
10003232 10002121 32.00
10004242 10004242 42.00
The numbers in field 1 and 2 is a product id code. I need to combine
the
two
fields, delete all duplicates. The dollar amount in field 3 is the
amount
associated with the product id in field 2. I am trying to update my
data
from
1/31/2008 (field 1) with the updated 3-31-2008 data fields 2 and 3.
Please help!!!
Click to expand...
Click to expand...
Click to expand...
 
Thank you for all your help, I really hope you can walk me thru this to the
end!
Okay:
Table main has product id's /codes that are not in Table U. So when I run
update query I need to distinguish that if the code does not match Table U
enter a zero in the field. For unmatched codes in Table main we need to skip
them or enter a zero. I hope I am explaining this correctly. Presently when I
run the update query it updates fine until we get to a procduct id that is
not listed in Table u- then the alignment is off.

Jeanette Cunningham said:
massa,
maybe your table has a default value of 0 for the amount field.
In A2000 to A2003, when you create number and currency fields, access
automatically puts a default value of 0.
Open the table in design view and delete the default value.
Now try your update query and see if that gives you the results you want.

Jeanette Cunningham


massa said:
Thank you Jeanette, This did work but the problem that occurred- if the
amount was 0.00 it did not include. How would I keep 0 null values as
well.
Thanks

Jeanette Cunningham said:
massa,
if I'm understanding your setup, I would do it like this.

TableMain with 2 fields
ProductCode and Amount
10002121 22.00
10003232 32.00
10004242 42.00

TableU with updates
ProductCode and Amount
10003232 22.00
10002121 32.00
10004242 42.00

Create a new query, drag both the TableMain and TableU onto the query
builder.
Create a join on the field ProductCode
Change it to an Update query
In the field for Amount in TableMain, in the update to: row put
[TableU].[Amount]
Run the query.

TableMain will now show the updated amount for each product code.


Jeanette Cunningham


I am new at access and can use some help. I have an existing table with
3
fields in it. I need to add field 2 and field 3 data into the existing
table.My table is set up like this:
Field 1 Field 2 Field 3
10002121 10003232 22.00
10003232 10002121 32.00
10004242 10004242 42.00
The numbers in field 1 and 2 is a product id code. I need to combine
the
two
fields, delete all duplicates. The dollar amount in field 3 is the
amount
associated with the product id in field 2. I am trying to update my
data
from
1/31/2008 (field 1) with the updated 3-31-2008 data fields 2 and 3.
Please help!!!
Click to expand...
Click to expand...
Click to expand...
 
The method of creating a query using the 2 tables and joining them on the
product id will only update the codes in Table main that have matching
product id's in TableU.
I am not following the bit about the alignment being off. Could you explain
for me.

If you wish to show a zero for any codes in Table main that don't have
matching product id's in TableU, you can run a second update query like
this:
Create a query between the 2 tables again with a join on product id.
This time change the join to show all the records from Table main and only
the matching records from TableU.
In the criteria row under product id for Table main, put Is Null

Change the query to an update query
In the update to row for amount for Table main put 0

When you run this query, any product id's in Table main which don't appear
in TableU, will have their amount set to zero.

Jeanette Cunningham


massa said:
Thank you for all your help, I really hope you can walk me thru this to
the
end!
Okay:
Table main has product id's /codes that are not in Table U. So when I run
update query I need to distinguish that if the code does not match Table U
enter a zero in the field. For unmatched codes in Table main we need to
skip
them or enter a zero. I hope I am explaining this correctly. Presently
when I
run the update query it updates fine until we get to a procduct id that is
not listed in Table u- then the alignment is off.

Jeanette Cunningham said:
massa,
maybe your table has a default value of 0 for the amount field.
In A2000 to A2003, when you create number and currency fields, access
automatically puts a default value of 0.
Open the table in design view and delete the default value.
Now try your update query and see if that gives you the results you want.

Jeanette Cunningham


massa said:
Thank you Jeanette, This did work but the problem that occurred- if
the
amount was 0.00 it did not include. How would I keep 0 null values as
well.
Thanks

:

massa,
if I'm understanding your setup, I would do it like this.

TableMain with 2 fields
ProductCode and Amount
10002121 22.00
10003232 32.00
10004242 42.00

TableU with updates
ProductCode and Amount
10003232 22.00
10002121 32.00
10004242 42.00

Create a new query, drag both the TableMain and TableU onto the query
builder.
Create a join on the field ProductCode
Change it to an Update query
In the field for Amount in TableMain, in the update to: row put
[TableU].[Amount]
Run the query.

TableMain will now show the updated amount for each product code.


Jeanette Cunningham


I am new at access and can use some help. I have an existing table
with
3
fields in it. I need to add field 2 and field 3 data into the
existing
table.My table is set up like this:
Field 1 Field 2 Field 3
10002121 10003232 22.00
10003232 10002121 32.00
10004242 10004242 42.00
The numbers in field 1 and 2 is a product id code. I need to combine
the
two
fields, delete all duplicates. The dollar amount in field 3 is the
amount
associated with the product id in field 2. I am trying to update my
data
from
1/31/2008 (field 1) with the updated 3-31-2008 data fields 2 and 3.
Please help!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top