Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access Training and help » microsoft.access.courses - Looking up two field in another table and merging them

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

microsoft.access.courses - Looking up two field in another table and merging them

Resolved · Low Priority · Version Standard

Reply Wed 22 Nov 2006, 09:25Delegate Linda said...

Linda has attended:
Access Intermediate course

Looking up two field in another table and merging them

I am trying to write a database where sheet A displays names from sheet B , and although the names are displayed in three fields in on sheet B, they should show as one field on the sheet A. Is possible?

For upcoming training course dates see: Pricing & availability

Reply Wed 22 Nov 2006, 11:18Trainer Orok said...

RE: Looking up two field in another table and merging them

Hi Linda

This sounds like a combination of performing Maketable Query on Sheet B where a concatenation calculation of the name fields in Sheet B is required to create a new table named Sheet A

Steps are as follows
1. Perform a query on table = Sheet B
2. In the Query perform a calculation similar to the following

SheetB.field1 +SheetB.field2+SheetB.field3
or
SheetA.field1 +(" ")+SheetA.field2+(" ")+SheetA.field3 if spaces are required between the names

3. Select from the Query menu > Maketable query, and name table as Sheet A
4. Select Query> Run
5 Save query for later use

Reply Fri 24 Nov 2006, 10:33Delegate Linda said...

RE: Looking up two field in another table and merging them

The problem is sheet A already exists. The field with the info from sheet B is just one of many. I am literally trying to format one cell in sheet A to display three other cells from a different sheet sheet B. Creating sheet A from sheet B is not really an option, because ther is also going to be a sheet C & D from which similar information will need to be extracted. Any more ideas?

Reply Tue 5 Dec 2006, 12:17Trainer Richard said...

RE: Looking up two field in another table and merging them

Follw the same process Orok suggested, but instead of creating a new table, simply create a lookup from table A to the new query, pointing to the field that has the 3 names merged.

Access articles

· Get A Head Start With Access 2010 Templates
· How To Sort And Filter A Table In Access 2010
· Discover The Easy Way To Create And Modify A Table In Access 2010
· Manage Your Macros With Access 2010
· Quick MS Access Training: Creating Custom Database Tables in 12 Simple Steps

Access tip:

Create a custom Close button on a Form

By default, Forms that you create in Access have a full set of control buttons, that is, a title bar with Minimize, Maximize and Close buttons in the top right-hand corner and a Control Menu in the top left-hand corner.

Sometimes Access needs to follow certain procedures or events as users close a Form (such as automatically opening another Form perhaps). So it is often a good habit to create a custom Close button on a Form for everyone to use, rather than the standard closing cross.

To create a custom Close button:

1) In Form Design View, draw a Command Button onto the Form.

2) Use the Command Button Wizard to select Form Operations under Categories, and Close Form under Actions.

3) Choose Finish.

Access then creates the button and code to close the form for you. You can add any extra code that needs to run as the Form closes to the same Close procedure.

However, the creation of a custom Close button is pointless if the closing X in the top right-hand corner of the Form remains active. To make the closing X inactive, open the Form Properties box then on the Format tab set the Close Button setting to No. This will grey out the closing X leaving it visible, but inactive.

View all Access hints and tips