Problems Populating Null Table-Field Values With an Update or Append Query

NSTurk725 · #1 06-09-2009, 10:54 PM

Hi, I am trying to use an update query or append query or whatver works to populate some values into an existing table without just copy-pasting things in manually. Here is what I have...

I have a table called Unique with 10 fields; "A, B, C, D, E, F, G, H, I, J" . A-E already have data in them and the remaining fields have Null values. I would like to use a query or whatever may work to fill in the data to them without having to add additional rows. I have tried using an update query and a append query, but each just add additional rows while leaving the previous field values blank.

I have attached the DB to give a live example of what I am talking about.

I feel like there is a simple solution I am overlooking. I have been playing with this for a few hours. Thank you for any help anyone can provide. I appreciate it greatly.

ConneXionLost · #2 06-10-2009, 02:19 AM

Hi NSTurk725,

A couple things here:

1. None of your tables have a primary key;

2. To merge the data onto the same rows, you'll need to identify one field in each of your source tables (ASP & RSP) that share common data. You'll then need to join those two fields. For example this would be an update query with the part numbers joined:

Code:

UPDATE RSP_Unique INNER JOIN [Unique] ON RSP_Unique.[RSP CUST PRT NUM] = Unique.CUSTM_PARTNU SET [Unique].RSPMDLCDE = [RSP_Unique].[RSPMDLCDE], [Unique].RSPASSYDESC = [RSP_Unique].[RSPASSYDESC], [Unique].[RSP CUST PRT NUM] = [RSP_Unique].[RSP CUST PRT NUM], [Unique].QTY_2009_SHU = [RSP_Unique].[QTY_2009_SHU], [Unique].AMT_2009_SHU = [RSP_Unique].[AMT_2009_SHU];

NSTurk725 · #3 06-10-2009, 01:26 PM

It still gave me additional rows. Here is sort of a visual of what Im loking for. It was sort of confusing when I first explained it. I apologize if for some reason I didnt follow your instructions carefuly, but I assigned a primary key and used your syntax.

WHAT I HAVE NOW:
H1 H2 H3 H4 H5 H6 H7 H8 H9 H10
XX XX XX XX XX - - - - - - - --
XX XX XX XX XX - - - - - - -- -
XX XX XX XX XX - - - - -- - - -
- - - - - - - - -- XX XX XX XX XX
- - - - - - - - -- XX XX XX XX XX
- - - - - - - - -- XX XX XX XX XX

WHAT I WANT:
H1 H2 H3 H4 H5 H6 H7 H8 H9 H10
XX XX XX XX XX XX XX XX XX XX XX
XX XX XX XX XX XX XX XX XX XX XX
XX XX XX XX XX XX XX XX XX XX XX

ConneXionLost · #4 06-11-2009, 02:47 AM

Hi NSTurk725,

The query I provided before does not add any rows to your "Unique" table, but it does update 17 rows (at the bottom) where the "CUSTM_PARTNU" and "RSP CUST PRT NUM" fields are the same between the Unique table and the RSP_Unique table. That's what I meant before when I was referring to "share common data". If there is another set of fields you intended to match between your tables, then please point them out since it isn't obvious to me what they might be.

Cheers,

Welcome to the Excel Forum

Forum Statistics