Can i combine tables in access

Total Access Speller. Total Access Startup. Total Access Statistics. Multi-Product Suites. Total Access Ultimate Suite. Total Access Developer Suite. Total Visual Developer Suite. Visual Basic 6 Products. Total VB Statistics. Total VB Enterprise Suite. Other Products. Sentinel Visualizer. Total ZipCode Database. All Products: Demos, Catalog, Awards, etc.

All Products. Product Awards. Forum and Ticket Submissions. Support Options. Product Updates. After Total Access Detective finds different records between your Microsoft Access tables , view the results and press the [Combine] button: Options for Combining Records from Two Tables A variety of options let you specify how the table should be created and populated: Options for Merging Data from Two Tables The example above is from comparing two tables in your current database.

Access provides a special type of query that you can use to vertically splice together the data from two or more tables. This is the union query, which can be constructed only by using the SQL View pane in the query designer. The following steps show you how to construct a union query to combine data from two tables into a single recordset, limited to addresses in the U.

Open Open the two tables tblClients and tblLeads and examine their structure and data. Access will present a blank SQL view. Then type in the first part of the query:. Yes, you must type it—there is no query by example equivalent to a union query. Important: You cannot undo an append query. Consider backing up your database or the destination table. Step 1: Create a query to select the records to copy.

Step 2: Convert the select query to an append query. Step 3: Choose the destination fields. Step 4: Preview and run the append query. On the Create tab, in the Queries group, click Query Design. Double-click the tables or queries that contain the records that you want to copy, and then click Close. The tables or queries appear as one or more windows in the query designer.

Each window lists the fields in a table or query. This figure shows a typical table in the query designer. Double-click each field that you want to append. The selected fields appear in the Field row in the query design grid. The data types of the fields in the source table must be compatible with the data types of the fields in the destination table.

Text fields are compatible with most other types of fields. Number fields are only compatible with other number fields. For example, you can append numbers to a text field, but you cannot append text into a number field. This figure shows the design grid with all fields added. Optionally, you can enter one or more criteria in the Criteria row of the design grid.

The following table shows some example criteria and explains the effect they have on a query. If your database uses the ANSI wildcard characters, use single quotation marks ' instead of pound signs.

Finds all records where the exact contents of the field are not exactly equal to "Germany. Finds all records except those beginning with T.

Finds all records that do not end with t. If your database uses the ANSI wildcard character set, use the percent sign instead of the asterisk. In a Text field, finds all records that start with the letters A through D. Finds all records that include the letter sequence "ar". Finds all records that begin with "Maison" and that also contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown indicated by a question mark. Finds all records for February 2, If your database uses the ANSI wildcard character set, surround the date with single quotation marks instead of pound signs.

Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what that value is. For example, a field may require a fax number, but some of your customers may not have fax machines.

In that case, you enter a pair of double quotation marks with no space between them "" instead of a number. On the Design tab, in the Results group, click Run. Verify that the query returned the records that you want to copy.

If you need to add or remove fields from the query, switch back to Design view and add fields as described in the preceding step, or select the fields that you don't want and press DELETE to remove them from the query.