Creat Table Query

Resolved · Low Priority · Version 2007

Creat Table Query

Chris has attended:
Access Advanced course

by - delegate Chris [5 posts] (2008 Jun 25 Wed, 16:06) Reply

I would like to split a table into 2 tables. How do I go about doing this

Version	Date	Location	Places available	Book	Next place rate:
Access Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Nov 25 Tue	Bloomsbury	4	book now	£207	£215
2002/XP	2008 Nov 26 Wed	Tooting	0	FULL
2003	2008 Dec 2 Tue	Southwark	5	book now	£198	£215
2007	2008 Dec 5 Fri	Bloomsbury	4	book now	£224	£224
2003	2008 Dec 9 Tue	Bloomsbury	0	FULL
2002/XP	2008 Dec 11 Thu	Tooting	0	FULL
Full Schedule: See all 55 Access Intermediate course dates. Bookings currently available until 24th November 2009.

RE: Create Table Query

by - trainer Carlos gold contributer [479 posts] (2008 Jun 27 Fri, 11:01) Reply

Hi Chris

The Make Table Query allows you to take an existing Access table and split from it a group of fields you want to appear in a new table. To do this

1: Select Create on the ribbon and Select Query Design

2: Select the Table(s) or queries you want to use to create the new table. Click Ok

3: Create the query by dragging the fields you want to use from the field list into the design grid. Use any criteria (eg all dates after 1/1/00) to define the data to be moved to the new table.

4: Click Make Table in the Query Type section if the ribbon

5: Use the Table Name box to name the table you want to create.

6: Use Current Database or Another Database to indicate where you want to put the new table. Click OK

7: When completed create the new table by clicking Run on the Ribbon.

The newly created table should appear in the Tables area of the database

Hope this helps

Carlos

RE: Create Table Query

by - delegate Chris [5 posts] (2008 Jun 27 Fri, 12:45) Reply

Carlos,
Thanks for this. That's really helpful!
Enjoyed the course on Wednesday.

Chris

· Access Database Optimization: First Normal Form
· Why go on a Microsoft Access Training Course?
· Access VBA Training Adds Power to your Databases
· Reasons to Consider Microsoft Access Training
· How Reorganising Your Data Can Take Your Business to the Next Level

Please browse our web site to find out more about
ms access course and other Microsoft training courses.

Access tip:

Calculating The Difference Between Dates

If you wish to calculate the time between two date fields, this can be done in a number of ways:

1. As a calculated field in a query
2. As a calculated control in a form or report
3. As a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One Date Field] - [Another Date Field]

You can also use one of the following functions:

=Month([One Date Field] - [Another Date Field])
which calculates the number of months between the two fields

=Year([One Date Field] - [Another Date Field])
which calculates the number of years between the two fields.

Another function is the DateDiff() function.

It uses an argument to determine how the time interval is measured. For example:

=DateDiff("q",[One Date Field] - [Another Date Field])
returns the number of quarters between the two fields.

Other intervals that can be used in this expression are as follows:
"yyyy" - Years
"m" - Months
"d" - Days
"w" - Weekdays
"ww" - Weeks
"h" - Hours
"n" - Minutes
"s" - Seconds