Kimbly said:
Let me try to explain myself a little better...
One table contains contact ID, geographical location (town or city),
business classification (e.g public houses), and then individual contact info
(such as company name, address etc.)
I then sent information to one geographical area and classification, such as
public houses in Cardiff (there may up to thousands sent).
The second table ( linked to the first via contact ID in a 1 ->many
relationship) contains contact ID, mailshot sent and date sent fields. I may
send to some contacts several times, and so need to add new records each time
i send to that contact.
How could I update the second table, for a specific area and classification
without typing through each record?
If there is an easier way of recording this information, i would be grateful
to learn how!?!
Tables look OK; the second one is linked via the [contactID] field to
the first one.
To input the data, I would first set up a Query that includes the
[contactID] field and any others that will help to identify the record
in a meaningful way. You could include some calculated fields, such as
Contact = ([first name] & " " & [last name] & [company name])
and sort the Query by [last name], and maybe filter it (set Criteria) so
that you see only [BusClass]="Public House"...
You can list your new Query in Datasheet View to see if you're getting
the records you want to see, with the proper fields, in the order you
want them listed.
You can then use the Form Wizard to set up a form to display the fields
of your main table.
To set up a subform, look up "Create a subform and add it to an existing
form" in MS Access Help. You can either define your own separate Form
(using a Form Wizard) or open your Form in Design View and let the
Subform/Subreport Wizard set it up for you.
Once you have your main Form and the subform defined, you can use the
record navigator in the main Form to choose the main record you want
("Llangloffan Hangout") and, in the subform, view all the related
records ("Sep. 12, 2005", etc.)
If you don't wish to set up Forms, you can do the same thing with either
Tables or Queries, though I often prefer using Queries, since they're
more flexible. For example, you can add computed fields to them without
changing the underlying Tables. Also, you can make a Query read-only
(in Query Design View, select View-->Properties, then change "Recordset
Type" to "Snapshot"). However, for your database, since you wish to add
or change records, you'll need to leave the type of your subquery as
"Dynaset" (= acts like a Table, allowing you to change fields and append
new records).
If you wish, you can set up a subdatasheet in a Query by opening the
Query in Design View, then opening its properties sheet (via
View-->Properties) and selecting the linked Query in the "Subdatasheet
Name" property. Type the names of the linking keys into "Link Child
Fields" and "Link Master Fields". Switch to Datasheet View, and click
on the little "+" sign at the left end of a record to see the linked
records from the other table. Unlike in a Form, you can have more than
one of these subdatasheets open at once, if the Datasheet window is
large enough to allow it.
Setting up a subdatasheet in a Table is similar to doing it in a Query.
===
For example, I've defined two Tables and two Queries.
Table [T_Contacts] contains the following fields:
[T_ContactsID] Autonumber (primary key)
[Name] Text
Table [T_Appts] (appointments) contains these fields:
[T_ApptsID] Autonumber (primary key)
[Date] Date/Time
[T_ContactsID] Number
[Q_Contacts] contains the following SQL:
SELECT T_Contacts.T_ContactsID, T_Contacts.Name
FROM T_Contacts
ORDER BY T_Contacts.Name;
[Q_Appts] contains the following SQL:
SELECT T_Appts.T_ApptsID, T_Appts.Date, T_Appts.T_ContactsID
FROM T_Appts INNER JOIN T_Contacts
ON T_Appts.T_ContactsID = T_Contacts.T_ContactsID
ORDER BY T_Appts.Date;
In Design View of [T_Appts], I set the Lookup property of its
[T_ContactsID] to be a List Box, based on [Q_Contacts], with 1 bound
column, Column Count = 2, Column Heads = No, Column Widths = 0";1".
This allows you to see meaningful values instead of key numbers.
In the "Query Properties" sheet of Query [Q_Contacts], I set the
Subdatasheet Name to "Query.Q_Appts" and the Link Child Fields and Link
Master Fields values to "T_ContactsID". Both Queries are Dynaset types.
In Datasheet View of [Q_Contacts], I hid the [T_ContactsID] field by
going to Format-->Unhide Columns and de-checking the [T_ContactsID]
check box. Same for [Q_Appts], since the ID fields, though necessary
for linking the fields, are useless for data entry.
Having set up these (empty) Tables and Queries, I then entered a few
values (names and dates) to verify that they were working. No retyping
of any existing values should be necessary if these are all working.
Having produced working Queries, I used the Form Wizard to build a Form,
[F_Contacts], based on [Q_Contacts]. I added a subform to that based on
[Q_Appts] and linked the subform to [F_Contacts] via the [T_ContactsID]
key, and I tested the form and subform.
At this point, if you have done what I did, you would probably want to
move the controls about on the Form to make it easier to use, but the
basic functionality that you want should all be present.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
:
Kimbly wrote:
I have two tables..
The first contains contactIDno (autonumber) and other contact info. I would
like to add records to the second table containing mailshot details, by
copying a range of contactIDno's (specified by a query) and then adding other
details, such as date sent, using an update query. Is this at all possible?
I am currently attempting to learn Access, so my experience is limited. I
have no SQL experience. Please help!
At least part of it is possible, depending on what other resources you
have. The purpose of an Update query is to copy values into a table
based on what you can calculate based on sources such as other tables.
Unless the "date sent" is already in some location accessible to your
database, possibly it will have to come from some device like (ugh) a
keyboard.
What would be better would be to already have a table somewhere -- if
not in Access, then perhaps in Excel or Word or just plain text -- that
lists the [contactIDno] values and the corresponding [date sent] values.
Since [contactIDno] is something you conjure up out of thin air, I
fear that you don't alreay have such a list on your computer.
Absent a convenient list, my next suggestion would be to set up a
data-entry Form in Access which will display meaningful data about each
contact record (something other than the meaningless [contactIDno]
values; I'd opt for name and employer, maybe sorted by last name) and
allow a knowledgeable person to enter the corresponding new material.
The Form would then store the [contactIDno] value and the new related
data into a table.
After your Tables are populated, you can set up Reports to display or
print summaries of the material they contain.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
