Need query to fill in missing data on table

  • Follow


Hi there-

I am using Access 2003. Here's what my table looks like:


Name		ID		Company		Cost
John Smith	              1234		Phil's Towing	$84	
John Smith					                $94
John Smith					                $104
John Smith					                $151


Presumably, I need a make table query that will fill in the ID and Company 
data in each row to make it look like this:

Name		ID		Company		Cost
John Smith 	1234		Phil's Towing	$84	
John Smith	                1234		Phil's Towing	$94
John Smith	                1234		Phil's Towing	$104
John Smith	                1234		Phil's Towing	$151

Any help is appreciated. Thanks. 
0
Reply Utf 1/5/2010 6:53:01 PM

Check Access HELP for more information about queries.

A "make table" query creates a new table.  An "update" query updates values 
in an existing table.

Before creating/running a query that changes values, be sure you've made a 
backup of the file!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"fgbdrum" <fgbdrum@discussions.microsoft.com> wrote in message 
news:56E89730-E5F0-4AC2-9C9E-35E95A3E2EFC@microsoft.com...
> Hi there-
>
> I am using Access 2003. Here's what my table looks like:
>
>
> Name ID Company Cost
> John Smith               1234 Phil's Towing $84
> John Smith                 $94
> John Smith                 $104
> John Smith                 $151
>
>
> Presumably, I need a make table query that will fill in the ID and Company
> data in each row to make it look like this:
>
> Name ID Company Cost
> John Smith 1234 Phil's Towing $84
> John Smith                 1234 Phil's Towing $94
> John Smith                 1234 Phil's Towing $104
> John Smith                 1234 Phil's Towing $151
>
> Any help is appreciated. Thanks. 


0
Reply Jeff 1/5/2010 8:06:58 PM

You can execute an update query which looks up the maximum ID and Company
values for each name and updates the non-Null ID and Company columns, e.g.

UPDATE  [YourTable]
SET [YourTable].[ID] =
DMAX("[ID]","[YourTable]","[Name] = """ & [Name] & """"),
[YourTable].[Company] =
DMAX("[Company]","[YourTable]","[Name] = """ & [Name] & """")
WHERE [YourTable].[ID] IS NULL
OR [YourTable].[Company] IS NULL;

However, the resulting table is not correctly normalized as it contains
redundant Name and Company columns.  You should then create an additional new
table, Contacts say, with Columns ID, ContactName and Company making ID its
primary key.  Then insert rows from the existing table into it with an
'append' query:

INSERT INTO [Contacts] 
([ID], [ContactName], [Company])
SELECT DISTINCT [ID], [Name], [Company]
FROM [YourTable];

Once you are happy that the new table is correctly populated with one row per
'contact', you can delete the Name and Company rows from the original
'YourTable' and create a relationship between the tables on the ID columns,
enforcing referential integrity and cascade updates (but probably not cascade
deletes).  The redundancy has now been eliminated, and your tables are
normalized and therefore protected from the risk of inconsistent data which
redundancy makes possible.

Note that I've used ContactName as the column name, not Name.  This is
because Name is a built in property in Access and should therefore be avoided
as a column name.

Ken Sheridan
Stafford, England

fgbdrum wrote:
>Hi there-
>
>I am using Access 2003. Here's what my table looks like:
>
>Name		ID		Company		Cost
>John Smith	              1234		Phil's Towing	$84	
>John Smith					                $94
>John Smith					                $104
>John Smith					                $151
>
>Presumably, I need a make table query that will fill in the ID and Company 
>data in each row to make it look like this:
>
>Name		ID		Company		Cost
>John Smith 	1234		Phil's Towing	$84	
>John Smith	                1234		Phil's Towing	$94
>John Smith	                1234		Phil's Towing	$104
>John Smith	                1234		Phil's Towing	$151
>
>Any help is appreciated. Thanks.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1

0
Reply KenSheridan 1/5/2010 10:30:47 PM

2 Replies
1012 Views

(page loaded in 0.041 seconds)


Reply: