|
|
Update
a record in one table with data from another table.
The sample query below shows how to add data from
one table to the records in a second table. You can modify this
example to your needs by primarily changing the references outlined
in step #6 in your Update Query .
NOTE:
This type of query does not work if the data being added is the
result of a totaling or grouping.
1.Create
a new table with the following fields and save it as Yearly
Rainfall:
City Inches Last Updated
--------------------------------------------------
London 0.5 4/2/00
Paris 22.0 4/2/00
New York 18.0 4/2/00
2.
Create a new table with the following fields and save it as Current
Week:
City Inches Week Of
-------------------------------------------------
London 0.1 4/9/00
Paris 2.0 4/9/00
New York 2.5 4/9/00
3. Create a new query based on the tables Yearly Rainfall and
Current Week.
Join the tables on the City field.
4. On the
Query menu, click Update.
5. Drag the
Last Updated and Inches fields from the Yearly Rainfall table to the
query grid.
6. Create the following
entries in the Update To row of the query grid:
Field: Last Updated
Table: Yearly Rainfall
Update To: [Current Week].[Week Of]
Field: Inches
Table: Yearly Rainfall
Update To: [Yearly Rainfall].[Inches]+[Current Week].[Inches]
7. Save and then run the query.
The data in the table Yearly Rainfall is now:
City Inches Last Updated
---------------------------------------------
London 0.6 4/9/00
Paris 2.0 4/9/00
New York 2.5 4/9/00
[Return
to top]
|
|
AddTo
Query
If
you want to insert new information directly into the data table,
this code is useful. This example adds a records to a typical
'Contacts' table. You can change the fields to match your database,
just be sure to match the fields in the INSERT portion with the
fields in the SELECT portion of the code.
Add
to a Command Button : DoCmd.OpenQuery "AddToAttendeesQry"
Create
the following query (using SQL) and name it: "AddToAttendeesQry"
INSERT
INTO Attendees ( ContactID, AttendeeLastName, AttendeeFirstName,
AttendeePrefix, CompanyName, Address, City, State, PostalCode,
Status, Type, PhoneNumber )
SELECT
Forms!Contacts!ContactID, Forms!Contacts!LastName,
Forms!Contacts!FirstName, Forms!Contacts!Prefix,
Forms!Contacts!CompanyName, Forms!Contacts!Address,
Forms!Contacts!City, Forms!Contacts!State, Forms!Contacts!PostalCode,
Forms!Contacts!Status, Forms!Contacts!ContactType,
Forms!Contacts!WorkPhone;
[Return
to top]
|
|
Use
the LIKE function to get the most of your query
A
query extracts data so you can view or manipulate it, but getting
that query to return just the right records can be a challenge.
There are a number of ways to limit the results of a query, but the Like
operator is one of the easiest.
The
Like operator compares a string to a search string: Like "searchstring"
-
Where
searchstring is the pattern of characters you're
searching for. For
instance, if you were looking for all the records that contain
Cooper in the LastName field, you'd use the expression Like
"Cooper"
-
Actually,
you could omit the Like operator in this case-it's assumed.
However, to match all the last names that begin with the letter
C, you'd use the expression: Like "C*"
(Again,
you don't have to type the Like operator as Access will enter it for
you.)
When
searching for a series of entries, use: Like "[first
- last]*"
-
Where
first is the first character in the series and last
is the final character in the series. You must include the
asterisk character, the brackets, and enclose the series in
quotes.
-
To
find all the companies in a database that begin with the letters
A, B, or C, use: Like "[A-C]*"
The Like function allows you to search for more than one series.
-
Simply
separate each series with a comma: Like "[firsta-lasta,firstb-lastb,...]*"
-
To
find all the companies that being with the letters A, B, C, or
the letter F. You could use the following expression: Like
"[A-C,F]*"
-
To
return all the companies that begin with letters other than, A,
B, C, F, G, or H. Instead of a complicated expression, you simply add an
exclamation character (!) to the previous expression: Like "[!A-C,F]*"
With the Like function you can combine a static character with a
series, for example: Like "n[first-last]*"
-
Where
n is the character you want to combine with first
through last. For instance, the expression Like "F[A-O]*"
will return all the Companies with names that begin with F, but
will limit those results to only those entries where the second
letter is a letter from A to O.
[Return
to top]
|
|
Zooming in on date ranges
Access provides a number of useful date functions to create expressions to isolate records that fall within a particular period of time.
-
To find all records date-stamped in the previous seven days (excluding today): Between Date()-1 And Date()-7
-
To access all records date-stamped last week: Between Date()-6-WeekDay(Date()) And
Date()-WeekDay(Date())
-
To locate all records from the current month:
Month([myDateField])=Month(Date()) And Year([myDateField])=Year(Date())
To access all records from the previous month:
Month([myDateField])
=IIf(Month(Date())=1, 12, Month(Date())-1)
And
Year([myDateField]) =IIf(Month(Date())=1, Year([myDateField])-1,Year(Date())
To locate all records from the current year:
Year([myDateField])=Year(Date())
To find all records from the previous year:
Year([myDateField])=Year(Date())-1
In the expression to find records from a previous month, we used the "immediate if" function to set the month value to 12 when the current month is January. In all other cases, we simply subtract one from the current month. We included the And clause so that, when the current month is January, we also subtract one from the current year.
[Return
to top]
|
|
|
|
