how do i ADD UPDATED DATA TO AN EXISTING TABLE?

[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!!!

 

[Jeanette Cunningham]

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]

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!!!

 

[Jeanette Cunningham]

massa,
can you give some examples of the 0 or null values that didn't work.
Were in TableMain or TableU?

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

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!!!

 

[Jeanette Cunningham]

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

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!!!

 

[massa]

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.

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

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!!!

 

[Jeanette Cunningham]

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

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.

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

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!!!