Update with distinct

[ronwmon]

I have a database with multiple tables. One table titled netbilling that contains (among others) the following fields:

ItemNum
ItemDesc
SubsChgs

I have another table titled billcode, I want to update the billcode table with data from the netbilling table. I want all of the fields above but I want to limit the update to distinct ItemNum. ItemDesc and Subschgs will also be distinct as every instance of ItemNum has the same ItemDesc and SubsChgs.

Any help is appreciated.

Ron--

 

[SkipVought]

Hi,

If your last statement is true, then you do not need to add ItemDesc and SubsChgs to this table nor should you, as this defies a principle of normalization.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ronwmon]

I don't disagree.

This simple code doesn't work. Do you see the error?

Update BillCode SET BillCode.ItemNum = [netbilling].[ItemNum]
where (select distinct netbilling.itemnum from netbilling);

 

[Andrzejek]

Maybe:

UPDATE BillCode
SET ItemNum = [netbilling].[ItemNum]
WHERE [blue]ItemNum IN [/blue](select distinct netbilling.itemnum from netbilling);

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ronwmon]

When I run this I get a dialog box titled Enter Parameter Value with netbilling.ItemNum.

Ron--

 

[dhookom]

I expect you would need to use DLookup() but this requires you to tell us more about your significant tables, fields, relationships, and requirements.

Duane
Hook'D on Access
MS Access MVP