Need query to fill in missing data on table

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. 
1/5/2010 6:53:01 PM
access 16762 articles. 1 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 9

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!


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

"fgbdrum" <> wrote in message
> 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. 

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

1/5/2010 10:30:47 PM

Similar Artilces:

i need money for beer
since every one wants to place the bogus adds on these news groups i figured lets see what happiens .i need money for the folowing 1) Beer becase i want it 2) A New tv becase my 68inch is to small 3) My daughter needs new barbies like see needs anouther one 4) my new computer is old now 5) becase i just need more money & u are giving it away 6) becase every idiot on here will post crap like this & get money SO THEY SAY so why not me 7) i need money to renovate my Brand new house 8) i have to mutch food so i need more winter is coming u know So if u have extra money send it...

need help! very urgently needed
i am using an Activex control in a dialog based applicaton(VC++). created an member variable for that(myportcontroller). In a non-stati function i am able to call the Activex control methods using the membe variable and worked fine. But i need to call the activex control method in an static function. Here i cannot use the member variable as it is static function. So, i declared an object for Activex control class Using that object i called the methods. I had no compile time error But i am getting run time assertion error as "winocc.cpp line:345". Ca anybody help? I need very urgen...

data validation and autocomplete -- again...
hi, i've looked at the discussions about this topic, and i can't help but feel someone must have created a better solution. does anyone know of a better solution? the problem is simple: 1. i have a chart of accounts list 2. i want to enter text that is likely to be in this list, and want autocomplete to save keystroke effort by scrolling to that place in the list, or at least autofilling, so i can then just hit enter. thanks for any help here, fred thanks max, this is very helpful. fred "Max" <> wrote in message news:eQov1Fo6EHA.28...

Is it possible to filter and have data dropdowns via validations?
I am creating a spreadsheet where I have dropdowns via the Data Validation function. I then want to Autofilter on the values in my dropdown. I know this sounds basic, but when i attempt to AutoFilter, I still see the entire contents of the spreadsheet and the little filter downarrow (which turns blue when the filter is on) is not blue. The first row of the table should have the column headings, e.g. Name Dept Salary The heading cells don't have data validation, or dropdown lists. In the cells below, you could use data validation lists. For example, in the Dept column, a ...

Macro Needed
I am working in Microsoft Word, we have a permit form that has a dropdown box that has two options either Full LDP or Perimeter LDP. I want the following to be displayed in a text field in the form later on. the text field is text19. How can i write an IF then statement that recognizes which option was selected from the dropdown box and then displays "This permit is for construction of perimeter erosion controls ONLY." in the text19 field? To start with, you should assign meaningful bookmark names to your formfields as it will make your macro coding much easier to under...

I want create a pivot table using an external database. I can do it but the list of column data appears all in column A instead of column B, C, D etc. How do I get the data into columns with its appropriate headers? Have a look here: best wishes -- Bernard V Liengme Microsoft Excel MVP remove caps from email "sid" <> wrote in message >I want create a pivot table using an external database. I can do it but the &...

Need help #8
Cannot find outlook on my new Vista 64 computer, can you help? Sincerley Rudy -- Rudy Ramirez 702 353-4141 Fax 702 450-0580 10100 W. Charleston Blvd, Suite 170 LV,NV 89135 RUDY RAMIREZ, you wrote on Fri, 20 Mar 2009 17:27:30 -0700: > Cannot find outlook on my new Vista 64 computer, can you help? Outlook doesn't come with Windows. Outlook is part of Microsoft Office that you need to buy if you want to use it. But you can download and install a trial version of any Office Version you want, but not Outlook standalone. http://us20.trymicro...

oma needs asp net version 1 but iis is running version 2
hi all sbs 2k3 with exchange outlook mobile acess ( oma ) is broken. i've drilled down to a probable cause which is the asp net version needed to run oma, being 1. something but the thing is, iis is running asp net 2. i've read that to run both asp net 1 and 2, iis needs to run separate processes, one for each version. has anyone fixed this issue before ? how was it done ? thank you PEdro LEite from Portugal ------------------------------------------------------------ ...

Publisher needs the disk to run..
I have Publisher 2000 and running XP Pro. after years of working fine, Publisher suddenly needs the disc in the drive to open a file. This is cumbersome... Any thoughts? Oh by the way,, I have already tried "repair" & "unistall & re-install"... "Orcas2369" wrote: > I have Publisher 2000 and running XP Pro. > after years of working fine, Publisher suddenly needs the disc in the drive > to open a file. > > This is cumbersome... > > Any thoughts? > I'm running the same as you but without the problem you describe. I...

Are you the XML expert I need?
Dear developer, Do you often wonder who built a certain website and how it is done? And who manages that website right now? Strange, but developers usually stay anonymous while they should present themselfs to get more orders. We have been working on a website to change this situation: It's a special Searchengine that lookes for Webpersons, not for Websites. We want as many webpersons as possible to join us. So that visitors can search for somebody who will do the job for them. If they find that person they can view his/her profile and portfolio. They can search o...

Need more Reports
GP does not provide detailed reports itself. reports such as sales by customer, best selling items, inventory valuation and so... Also the reports that it provides are not in good design and format (not useful). Is there any report package from third party that can be purchased or there is any other solution to get in depth reports and analysis? Sure Since Great Plains cannot possibly be all things to all people, get to know the layout of the Great Plains database and where all of the data is stored. Then roll your own reports using the native Report Writer, SQL Reporting Services, Cryst...

Do I need a subquery?
Hi: I am having problems creating a query to show cars with WorkOrders. The tables involved are Customers (CustomerID as PK), CustomerCars (CardID as PK, CustomerID as FK) and WorkOrders (WorkOrderID as PK, CustomerID as FK, CarID as FK). I would like to have a query as the recordsource for a listbox that shows the WorkOrderID, CustomerName, CarYear, CarMake and CarModel. My problem is that I keep getting all of the customers' cars with the same WorkOrderID. ie If I am the customer and I have 5 cars listed in CustomerCars they all show in the query and I only want the one...

Need a Training Database
Hi I am on the lookout for a Training Database.mdb - to keep track of employee training, for generating training needs employee wise as per different training categories such as Technical, Management Development, Safety, Environment, etc. Any help where I can get one? I work with Access 2000 version Where have you already checked? -- Regards Jeff Boyce Microsoft Office/Access MVP Microsoft IT Academy Program Mentor Microsoft Registered Partner "shriil" <

Emails going missing
Hello, I have set up in my exchange server for a POP3 server to a POP3 account to pick up emails. In general, it all seems to be working - if I send an email to the pop3 account, the emails generally appear in the exchange mailbox and then appear in my connected outlook. However, a large number of emails (including say postings from this group) just dont appear. But as I say, direct emails (say from hotmail) do. Both those that get through, and those that go missing are removed from the POP3 mailbox. Can anybody give me some pointers where I should be looking in exchange to see why they ...

No data showing in query
I am new to Access and am using Access 2007. I wanted to create a query to bring data together from several tables to complete forms and form letters. I used the Query Wizard to create a query which selected data from the different tables. It worked fine and I was able to complete a form in Word. I have now used the the same process using the same tables for a couple of other queries but now there is no data in those datasheets. Anything I'm missing here? Thanks. There are three ways of creating a query. The query wizard, which you've used, is at one end of the &q...

Outlook Express Needs to be Opened Twice
The first time I open Outlook Express it connects and down loads my messages. The next time I open it it doesn't connect. I then close it and the next time I open it it connects and downloads messages if I have any. How can I fix this? Hi, This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: microsoft.public.windo...

Popup box for data entry...
This request will probably betray the fact that I'm relatively new to Excel... I need a button on a page (I can do that) that pops up a box that asks for a value. After you enter the value in the box, it puts it in a cell on that page... Can anyone help with this??? Thanks! Taylor Taylor Here are a couple of macros. Sub Test() 'allows numeric value Num = InputBox("Enter a number") If Num <> "" And IsNumeric(Num) Then Range("A1").Value = Num End If End Sub Sub Test22() 'allows string value txtstr = InputBox("...

Query leaving out some data
Access 2003 I think this must be a pretty easy question, but unfortunately I'm unable to identify the solution . . . SIMPLIFIED SUMMARY Customer Table CustomerID Company Name 3301 ABC Industries 3302 My Store, Inc. 3304 A1 Company 3305 Mom & Pop's Shop Delivery Table CustomerID OrderID 3301 988374 3302 788362 3303 700287 3304 679028 Query should pull CustomerIDs for all Orders and list the CustomerID, OrderID and Company Name. (SQL = SELECT Deliveries.CustomerID, Cu...

Data Security & Flash Drive Formatting
Last week I placed some sensitive files on a flash drive for a brief period (two or three hours) before deleting them. At this point, I'd like to make sure that these deleted files CANNOT be recovered, and I know there are tools out there that *can* restore deleted files. I need to make sure that any such tools will NOT be able to recover the files in question. So, I'm wondering if a "low-level" format operation would flip all the 1's to 0's (so to speak), such that these files would be certainly unrecoverable. Would formatting do the trick? And wo...

How to ID a table source being used in a Union Query
I have created a Union Query that uses 3 tables to pull data from. I need to see identify which of the three tables the data came from. I tried adding a column to the original tables with a default value "FP 07-09" which would ID the table for me but my problem is: I cannot fill in this column with that value on the existing rows of data all at once. I would have to paste it in one row at a time unless someone can help me understand how to fill down the column with this value or paste this in the new column on all the existing rows. Someone else created these tables and I cann...

Axapta Developer NEEDED!
Hello: I'm a recruiter for a $600 million sales company. We are looking for an individual to work out of our beautiful corporate offices in Southern California. This individual would have one year experience doing Axapta development. I can't find anyone...please help! ...

Do I really need OFFSET?
I have defined a Dynamic Name as follows: =OFFSET(Sheet1!$A$4,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1) The label for the data is in A3, the data starts in A5 and continues downward. The definition above does start plotting from A5 but adds a blank cell. There is always one extra empty data point that is plotted. Is there an easy fix so that the plot starts at A5 and stops at the last data point? Thanks. See whether changing -1 to -2 (two places) fixes the range definition. If so, you have two non-blank cells that you don't want to count instead of one. On...

I'm just getting started an I have some questions on tables.
I deal with both companies and end users and I'm trying to figure out how to set up the contact table. My plan was to set one table for companies and have contact entries as a sub form and another form for contacts with individuals but both the company contact sub form and the individual form would link to the same table? My reasoning is that I may have many contacts to one company but have individuals that contacts normally at most would be "domestic partners"and their family members and I want to track contacts whether it's email, phone calls, letters and more. The first...

append query discrepancy
I have an append query that is outputting to a table that I use for mail merge. The problem is in the table view of my query, I see the correct information in certain fields like "City, State, Zip" in the regular format of, say, "Elmira, NY 14905". When I go into the table that the query is outputting to, in the "city, state, zip" field I get a number, which corresponds to the ID Autonumber that corresponds with each city, state, and zip in the table the query is pulling it off of. Any idea how to fix this? I can send a copy of the mdb. Please feel free to...

I really need help. Need a formula. Need it yesterday.
I'm new to Excel formulas, and I need one that is well beyond me at this stage, but probably a piece of cake for an Excel Wizard. I have a modest staffing schedule on Excel that shows several teams of employees with staffing requirements that may change every thirty minutes, as does the number of employees available in each team. I have the spreadsheet linked to another that provides the total, overall staffing requirement for each half hour, so that the total staffing requirement for each half-hour automatically loads to my staffing schedule. Likewise with the total number of emp...