Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Query 1 Table To Then Populate Another!

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm new to Access 2010 and still waiting for delivery of my Access book so my apologies if this is a daft or easy question

I have set up a Business Database with all the usual fields in a table. I have then set up another table for the contacts with-in the business (i.e. name, number, email address). Done the relevent links (One to Many) and that happly works. If I manually add in a business and then add on the first contact in the same query screen it all works fine.

I now want to set up forms for my users, So I set up a form to add a business in (Which is fine and works no problem), I then want to do one of two things.

1st Click a button to take the business details just entered then go to a form to add on the additional contact(s).
2nd To add a form to search for a business then add on contacts.

I've been able to do a basic Criteria search from a query but not able to use that to add the relevent contacts.

Your thoughts please...

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Delete a VBA Module From Excel
- Delete a VBA macro module from Excel with this macro. This macro allows you to fully remove a macro module from Excel.
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics

The last time I used Access was version 2! So I'm effectively starting from new. I have good theory knowledge but no real hands on experience.

I know this is going to be a very basic question but I'm still trying to find a good book / guide for Access 2010 (Beta) to get me really going (any suggestion welcome). So my apologies in advance.

I'm setting up a Database of names and address, I then need to add multiple notes to a single contact(s) in the database.

I've set up my names & address table, it also has a unique number (its to match a reference we have in the business) for each contact (along with the ID field that automatically there).

Initially I've set up a new table with fields for the unique contact number and the note held (would also like Date but have not done that bit yet!). Then linked the unique contact on each table.

I suppose my main question is how do I get the data in there from a form, I've set up a form to enter in contacts which works. I want to now set up a form to 'find' a contact then add a note. I also need to add the 'note' part to the 'enter contacts' form too (but don't know how to link the note to the contaqct at entry?). Then I guess to date / time stamp it.

I hope I've not bored you all too much yet! any help would be welcomed.


I imported some business card info into Outlook 2003 in the following way.

First I imported the data into Access. (I created the access file format by doing an EXPORT from Outlook to Access, so the file format would be recognizable to Outlook).

I then input the contact data from the Access table into Outlook. It worked beautifully.

One glitch !! When creating an email to one of these contacts, the recipient address displays in the top of the new mail message as follows:

Elizabeth M Jensen LSW (Business Fax)

This is true for all these contacts.

I do not know why "Business fax " appears. How do I fix this?

The field "Phone 3 selector" does contain the text "Business fax", but I did not populate that field.

Many thanks for any assistance.



i need to report the business calls on my phone bill, so i download my phone bill on one sheet and my contact list on another sheet.

now i need to match the numbers on my phone bill with the names from my contacts.

i tried the vlookup function and it works fine, i used one column for numbers and the other for the contact name.

now i have contacts with 2-5 numbers, so for one contact i put his three numbers in three columns and the fourth one for the name but it didnt work with vlookup, in the same time some contacts have only one number, example attached, what function should i use to return the contact name and assign the word "business" to him ??

i hope that anyone could help, regards

Hi i have two tables, firstly business area tbl contains an ID, description and email field, secondly initiatives table holds business area description and business email. When the user enters the business area description from the dropdown lookup in the initiatives input form i want the email field to pre populate based on the email in the business area table email field.

Can this be done on my tables or only on my form?


I have a text file of contacts with entries as follows:

Full Name: Bill Smith
Business: 1 800-888-8888

Full Name: Jim Jones
Company: Kool-Aid International
Business Address: South of the Border, Somewhere
[blank line]
Business: 1 888 888-8888

and so on.

Most contacts have some but not all of the typical address fields filled out: some have street addresses and tele #s, some just have tele #s, etc.

I imported the text file (.csv) into Excel, and split the data into 2 columns (using the ":" as the break.) That gives me 1 column with the fields listed and the other column as the data. But because 1 record may have 3 fields (Name, email, tele) and another record will have more fields or/or different fields, I cannot easily create a database with records going vertically and fields horizontally.

Any ideas how to get this cleaned up?

In MS Access, I have a Query which looks at the last business day
records entered into a database.

Using the criteria in the Query by example desgin: <Now()-1

This works fine for business days records in Tuesday, Wednesday and
Thursday and Friday.

Beacause using the critieria: Tuesday's query would have Monday's
records entries.
Wednesday's query would have Tuesday's records
Thursday's query would have Wednesday's
records entries.
Friday's query would have Thursday's
records entries.

The problem occurs when I want to look business day records for Monday.
Whereby, Mondays records
should have last weeks Friday entries...

Is there a solution to overcome this problem?

Kind regards,


Normally I work with cleaning up messy data and now I have a contact list I'm converting from a spreadsheet. Should be easy right?

I'm trying to avoid duplicating contact names but the problem is some contacts are assigned to mulitple areas. But I want the user to be able to query market and pull back the correct contact for a given category if business. How do I do that without using the name more than once?

For instance Bob Smith is in the Contact table with an ID, first last name and a notes field. How could I assign him to multiple markets with nothing common between him and the markets table? The markets table, has a unique ID, a name, and a notes field.

I thought I could make a drop down and assign him to markets but that only lets me put in one field. So then it seems like I'd have to have more than one market field? Or more than one line of Bob Smith?

I took a look at the contacts.mdb but it doesn't seem to help this situation. Any help appreciated.

Hello all,

I have a very large Excel database page, and on another page I have a bunch of tables that make calculations based off what's in the database. Everything works fine w/r/t my formulas, but the problem is the performance is very slow. Any time I make a change/update/save the spreadsheet needs a solid ~10 or so seconds to "think" and update the cells. I'm guessing the reason is because in my charts, I literally have hundreds of formulas like the one below, which are slowing down performance.

I don't have much expertise with macros/VBA/Essbase, etc....but I'm wondering if there is something I can do to make the spreadsheet run faster. Any help is much appreciated.


=SUMPRODUCT(('Business Data'!CD$3:CD$2500)*('Business Data'!$L$3:$L$2500="BSC")*('Business Data'!$A$3:$A$2500="R2"))+SUMPRODUCT(('Business Data'!CD$3:CD$2500)*('Business Data'!$L$3:$L$2500="BSC-CB")*('Business Data'!$A$3:$A$2500="R2"))+SUMPRODUCT(('Business Data'!CD$3:CD$2500)*('Business Data'!$N$3:$N$2500)*('Business Data'!$L$3:$L$2500="BSC")*('Business Data'!$A$3:$A$2500="R2"))+SUMPRODUCT(('Business Data'!CD$3:CD$2500)*('Business Data'!$N$3:$N$2500)*('Business Data'!$L$3:$L$2500="BSC-CB")*('Business Data'!$A$3:$A$2500="R2"))

Hi, what a great resource. Hope you are able to help with the following puzzle:

I am making a local directory, and am trying to automate (with a formula) an Excel doc with the following info:

COLUMN A: Neighborhood name (hundreds of items)
COLUMN B: Business Category (6 different in total)
COLUMN C: Business Name (hundreds of different items)

So it looks like this:

Column A Column B Column C

Neighborhood 1 Category 1 Business 1
Neighborhood 1 Category 1 Business 2
Neighborhood 1 Category 1 Business 3
Neighborhood 1 Category 2 Business 4
Neighborhood 1 Category 2 Business 5
Neighborhood 1 Category 2 Business 6
Neighborhood 2 Category 1 Business 7
Neighborhood 2 Category 1 Business 8
Neighborhood 2 Category 3 Business 9
Neighborhood 2 Category 3 Business 10
Neighborhood 2 Category 5 Business 11
Neighborhood 2 Category 5 Business 12

And so on. All Neighborhoods are alpha-ordered, and all Categories 1-6 are alpha ordered within each Neighborhood.

I would like to generate one single, long, document (into Word or whatever) a single list that goes like this:

Neighbourhood 1
Category 1
Business 1
Business 2
Business 3
Category 2
Business 4
Business 5
Business 6

Neighborhood 2
Category 1
Business 7
Business 8
Category 3
Business 9
Business 10
Category 5
Business 11
Business 12

IS this in any way automate-able? Thanks so much, happy to make more clear if needed... Thanks, Kanga

I have contact data for 4 contacts in one record, i.e. on one line in a table; although there are 4 contacts separate contacts they share the same phone number, address, etc.

I would like to get each contact into a separate record with is associated phone number, address, etc.

I have tried creating 4 copies of the same table and then merging with a query but got lost.

I have tried an append query but the access help is useless.

Any ideas??

Hi Everyone!

I am new here and need some help. I have Microsoft Excel 2003, not all that experience and have never used Macros. I am designing a database for my uncle and have reached a problem. Currently my data looks like this:

Column A1 is business name and is fine
Column B1, C1 is city and address which are also fine
Columns D1, E1, F1, and G1 is where my problem arises. Each of these columns will need multiple lines of data in them. D1 is contacts (and for some companies I have 10+ contacts) E1 is contact title (need this to line up with D1), F1 is contact email and G1 is contact phone number. I need all of these to line up. Currently I just have them on separate lines, but some do not line up. How can I divide these cells into separate rows (not columns) so they have lines and are easy to read and follow? Any help would be much appreciated!! Thanks in advance.


I have a table with two foreign keys (contact_ID and client_ID) one of which will always be blank (the table is either assigned to a contact or a client)

I have a combobox (d_owner) in a subform which populates will all contacts associated with a client. I also want to put the clients name in the combobox then two things could happen:

1. enter a contacts name into d_owner
1a i want the contact_ID to go into the contact_ID field in the table
1b i want client_ID to be blank in the table

2. the opposite

I was thinking about using some vba to populate an unbound combobox then having two hidden fields bound to contact/client_ID and when d_owner changes putting the correct ID in the correct hidden field

is there a better way to do this?



edit: the control source for d_owner is currently:

SELECT, contacts.contactName FROM clients INNER JOIN (client_contacts INNER JOIN contacts ON client_contacts.contact_id = ON = client_contacts.client_id WHERE ((([form]![client_ID]) AND ((contacts.contactType_id)=3));

this works fine for populating with just contacts

I've found some partial solutions but not one that solves my problem entirely.
I have a long list of contacts in one column. Each cell contains data as follows:
Last, first middle title - Business Name (999) 999-9999 1111 Main St, city, zip.
Occationally name or business name is missing. The appearance is exactly as shown with commas, spaces, hyphens, and parenthesis.
Can anyone help me with formulas to convert this to a column for name (last, first middle title), column for business, column for phone number and column for address.
Thank you.

I love to start things by saying "Is there a way to", however over the years I have learned that the answer to that questions is almost always invariably yes. So I will start it this way.


I have a form that lists all my contacts. My contacts are sometimes suppliers, I have a sub-form "subfrmSupplierContacts" on my contact details form "frmContactDetails". I want to be able to search my "frmContactsList" for example for "dan", and have it return to me all the contacts who have the name dan in either first, last, or company name.(I have this allready)

However, I would like it to search my "subfrmSupplierContacts" first, last and company name also, BUT, only return to me the Single Contact Entry.

Here is what I have tried so far:

I can create a query where the one to many fields are joined, however I have 31 Supplier Contacts for "XX Company" so therefore I have 31 entries with the same Company Name all the time. I only want "xx Company" to show 1 time.

I contemplated doing an ADODB.Recordset to open that "qryContactsAllData" and have it search that and create a very complex Filter Criteria with specific numbers, however I know there has to be a better way to do this. Maybe using some combination of Totals (ie group by)

Does anyone have any suggestions as to how I should create a search that will search for for the info I put in a search box on all the one to many tables, but only return to me the ones in the results windows.

Thanks for any time spent trying to assist me with this,



I have a friend who used to work for a large organization and has been made redundant. He has a list of contacts which has been saved in Microsoft Word as a four column table. The document is 37 pages long. The data is in four columns only - 'Name', 'Email', 'Phones' and 'Business'.

There is a lot of data in each column;
the 'Name' contains both first names and last names. The 'Email' column often contains more than one email address. the Phones column often contains more than one phone number and the phone number is in many different formats; some times with a county code, some times an area code, sometimes with brackets, sometimes with +61 etc for the country code. the 'Business' contains any combination of business names, and addresses all rolled into one.
He wishes to somehow get this information into Outlook as contacts and I've offered to sort it out for him.

Obviously the data was never enterered very well in the first place but regardles, we no longer have access to the original data source and what we have is it.

I thought the best way would be to somehow dump all the data into Excel and then manually cut and paste it into columns such as 'First Name', 'Last Name', 'Phone1', 'Phone2', 'Business Name', 'Street Address', 'City' etc etc. From here I would then be able to 'Normalise' the data and import it into Outlook.

As this is many hours of work, my question is 'Am I on the right track here?'

Is there an easier way to do all this. Any other suggestions on how to go about this mamoth task.

Here's an example of how the data looks


Aaron Simcock
Office phone: +61 2 xxxx 6866
Cell phone: +61 411 xxx 258
Axx xxxxx - Sydney

Adam Laura
Office phone: 02 xxxx-4000; Dir: X4232
Office fax: 02 xxxx-4111
Cell phone: 0410 xxx 061
xxxxx xobin

Adelle Howse
Office phone: +61 2 xxxx 6623
Cell phone: +61 409 xxx 467
xxxxxxx Holdings
Corporate Finance & Investment

Thanks in advance,


Hi Guys

im in the process of creating a simple MI package for work that allows me to keep track of the departments performance on an individual level.

We have a simple submission form that send data to a "raw data" page containing the following details.

Business week - Todays Date - Agent name - sales - conversion

Ive then created a table with data validation list at the top that allows the manager to pick his name, "which populates all his team in the table below" then a date "to show that days performance" all populated from the raw data issue is.

How on earth can I ask the table to populate a sum total for the business week? as each manager submits data daily, each agent will have multiple entries for the same business week, at the moment i've been using "vlookup(data$data" which works fine but obviously wont for multiple entries.

Hope that makes sense? any ideas guys? Access is out of the question as the business doesnt use it.

cheers guys


Hi, this function in Access 2007 is quite cool and helps me a lot but it seems to have limitations? What i'm doing - i collect all non-customer business names and contact details in my sales areas. I add these names and contact details to my database and assign the business name (RECORD) to a specific sales rep. It might happen that i assign up to 150 businesses to one sales rep. My problem - when i email my HTML form (TABLE) via this function in Access 2007 then the system will email the 150 records to the sales reps email address because i added the sales reps email address to each record ie 150. The solution i'm looking for - the system must allow me to email the sales rep say 5 leads per day, ie date depended or something else like the next lead will be emailed as soon as the sales rep reply on the ones already being send to him. The reason why this tools work so nice for me is the fact that my sales reps are remote with email access, the replies from the sales reps are automatically added to the database, quite cool. Any ideas? Thanks for your assistance.

Hello All

I have created a pivot table and want to format this to remove the
subtotal's below each major total i.e.

Business Name1
Business Name1 Subtotal
Business Name2
Business Name2 Subtotal
Business Name3
Business Name3 Subtotal
Location1 Grandtotal

Business Name1
Business Name1 Subtotal
Business Name2
Business Name2 Subtotal
Business Name3
Business Name3 Subtotal
Location2 Grandtotal

Is there a method to do this? I've tried numerous settings in the 'table
options' such as hiding the subtotals and grandtotals.

I'd appreciate any assistance.

Many thanks to all who respond

Hi all, I have the following sumproduct formula that is adding up values based on several different criteria. This formula works fine.

=SUMPRODUCT(('Business Data'!DA$3:DA$4570)*('Business Data'!$L$3:$L$4570="BSC")*('Business Data'!$A$3:$A$4570="R1"))+SUMPRODUCT(('Business Data'!DA$3:DA$4570)*('Business Data'!$L$3:$L$4570="BSC-CB")*('Business Data'!$A$3:$A$4570="R1"))

What I want to do is add to that an if statment that will also only do the sumproduct whenever the value in column J contains TBD. I'm sort of close (I think) with that if statement, what I have is below, although it may be completely correct.


I'm hoping for both a little help on refining the if statement so that it works correctly, and then also integrating it with the above sumproduct formula.

Any help is much, much appreciated. Thank you.

I have set up a search box which searches for business names in all sheets of the attached worksheet. The search part is working...

Ideally, when the options show up in the list box, when I double click one of them, I would like the form to fill out with the data of the particular business chosen. I would then add an 'Amend' button when someone changes the information on the form. Also, when the search results show up in the listbox, is there any way of showing up the 'Status' information alongside the business name?

Any help is much appreciated!


And thanks to Ger Plant for solving my previous issue!

I'm submitting to the greater wisdom and trying to design a properly normalised database.

I need to record which of the contacts stored in my database are related to which others, and how. When I was working using multi-value fields this was doable using a single combo box. And I know if a multi-value field can do it, I can do it. I'm a bit confused how this junction table's going to work though. I've made a table with

ContactToContactID - autonumber primary key
ContactID1 - number
ContactID2 - number
NotesOnLink - memo

And I've created relationships between both contact ID 1 and contact ID 2 and the main contact ID field in my main table. But I'm a bit flumoxed about how to get from here to a form in which you lookup one contact and then are able to lookup their linked contacts (name fields!) in a subdatasheet below. Amongst other problems, I don't see how to generate that asymmetry between the two contact ID fields, one in the main form, and a series of others in the subdatasheet below. It's not even a split between 1 and, the other, 2. As each individual might ocurr as contact1 or contact2 in different links.

Maybe I'm just tired.

Just created a form based on the main table, and dragged in the links junction table as a subdatasheet. Only the contactToContactID and NotesOnLink fields showed. Obviously access would cut out the usually redundant home field from the subdatasheets displayed within a table. Don't know how to get round that one.

I have a list of Business names with their Address locations directly in the cell below, i.e.

Business Name
Business Name
Business Name
Business Name
Business Name

I have tried to make a macro that will move each Address from the cell below to the next cell directly to the right of the
appropriate Business Name, i.e.

Business Name | Address
<blank row>
Business Name | Address
<blank row>
Business Name | Address
<blank row>
Business Name | Address
<blank row>

Thus far any macro I have created seems to get itself into an endless loop within the area I tried to create it until the DeBugger
cuts in and tell's me there is an error (obviously).
What I need to know is how to get the macro to move on down the list. It just seems to want to make the changes over the exact
same cells I used to create it and go no further.
I hope you can relate to what I've been trying to describe to you, thanks for all assistance.


I have a list of 180 apps, their respective categories (20 different ones) next to them and then their business units (15 different ones). I want to form tabs for each business unit and then a final tab with a matrix/pivot table (whatever works) of just the application name and not the count of each category or business unit. Can someone please help me with any step I could take for this... I kno how to make pivot tables but cant make the data not be numbers, but i havent been able to figure out how to do anything else for any other step... please help me! PLEASE!

I am very new to Microsoft Access, but am very comfortable using other database engines (sql server, mysql).

I have a form called mainForm, that has a text box and a button to search a table called details, the result of the search appears in a sub-form called detailsForm.

This setup works fine.

What I want to do now, is based on which record the user is on in the detailForm, when they click a button, use Field1 from the active details record to do a lookup in another table.

I am having troubles doing this using the same technique as before... the technique I used before was to make a query with: where .... Like [Forms]![Mainform]![Text1]

So now I am trying to make another query and doing where ... like [Forms]![mainForm]![detailsForm]![Field1] - this isn't working.

Can anyone help?


I have a simple spreadsheet that looks like this:

Origin State Dest State Company

....and so on. Basically, the first 2 columns have all states/provinces and
column 3 has the company we use. I need to create one matrix with all
states/provinces in Row 1 and in Column 1, and all the cells in between are
populated with the company.

Question is How can I do this using MS Excel using Pivot Table. I can do this using Business Objects or Crosstab Query in MS Access but how do I do it in Excel using Pivot Table? This can be done by using INDIRECT and MATCH functions but this involves a couple of steps. Is there any shorter method?

I have attached an excel sheet that gives what I have, What I can do using Access or Business Objects and what I get using Pivot Table in Excel. The normal Pivot Table in Excel gives numbers instead of the company name even if I use Max or Min function (which works fine in MS Access query). So what am I missing here?

Thanks in anticipation,
Warm Regards,