Help with INSERT

I'm very new to Access, and trying to learn SQL at the same time.

I'm working on a database in Access to track calls and correspondence.
Thanks to help from this group I now have a query to pull all contacts that
have not received a call or letter in the last 45 days. Now, lets say its
time to send out a newsletter to all the contacts. The tables are one to
many, Contacts to Calls.

I have a small form that has fields for Date, Time, Subject and Notes. I put
in a Date and a Time, the subject of the letter or call and any other notes.
Since I'm sending this letter out to all of my contacts I need to insert one
record in the Calls table for each of the contacts in the Contacts table. The
Date, Time, Subject and Notes all need to come from the form.

How can I build an INSERT statement that will create one record in Calls for
each record in Contacts and fill in the other four fields Date, Time, Subject
and Notes from the form?

I'm sorry this post is so long. I'm just trying to be clear. I appreciate
everyone's help and don't want this to be as confusing for you as it is for
me.

Thanks

Hi,


You make a join with a standard SELECT statement.


If tableA has three records, a, b, and c, while tableB as four records, 1,
2, 3, and 4, then

SELECT tableA.f1, tableB.g1
FROM tableA, tableB



will generate the 12 possibilities

a 1
a 2
a 3
a 4
....
c 3
c 4



You can then change the SELECT statement into an INSERT statement, in the
query designer, from its menu or form the toolbar. The goal is to get the
SELECT statement right, as you want it, and once you are happy with it,
change it for an insert (if you need it: you can build a from or a report
from a SELECT query, no obligation to necessary use a table).


Hoping it may help,
Vanderghast, Access MVP

"Scott Bridges" wrote in message
...
I'm very new to Access, and trying to learn SQL at the same time.

I'm working on a database in Access to track calls and correspondence.
Thanks to help from this group I now have a query to pull all contacts
that
have not received a call or letter in the last 45 days. Now, lets say its
time to send out a newsletter to all the contacts. The tables are one to
many, Contacts to Calls.

I have a small form that has fields for Date, Time, Subject and Notes. I
put
in a Date and a Time, the subject of the letter or call and any other
notes.
Since I'm sending this letter out to all of my contacts I need to insert
one
record in the Calls table for each of the contacts in the Contacts table.
The
Date, Time, Subject and Notes all need to come from the form.

How can I build an INSERT statement that will create one record in Calls
for
each record in Contacts and fill in the other four fields Date, Time,
Subject
and Notes from the form?

I'm sorry this post is so long. I'm just trying to be clear. I appreciate
everyone's help and don't want this to be as confusing for you as it is
for
me.

Thanks

I want to create a record in my tblCalls table for each of the people in my
tblContacts table. So my JOIN would be on ContactID which exists in both
tables.

I guess I can't explain it very well. If I have 35 people in tblContacts and
I want to send out a newsletter to all of them, I need to create 35 records
in tblCalls. One record for each of the 35 people in the tblContacts table. I
have a form with text boxes for Date, Time, Subject and Notes. The form has a
button to Submit. When I fill in the fields and click Submit, I need to add
35 new records to tblCalls that each have a ContactID (from tblContacts) and
the Date, Time, Subject and Notes that are pulled from the form. Does this
help? I hate being ignorant. This database stuff is killing me.

"Michel Walsh" wrote:

Hi,


You make a join with a standard SELECT statement.


If tableA has three records, a, b, and c, while tableB as four records, 1,
2, 3, and 4, then

SELECT tableA.f1, tableB.g1
FROM tableA, tableB



will generate the 12 possibilities

a 1
a 2
a 3
a 4
....
c 3
c 4



You can then change the SELECT statement into an INSERT statement, in the
query designer, from its menu or form the toolbar. The goal is to get the
SELECT statement right, as you want it, and once you are happy with it,
change it for an insert (if you need it: you can build a from or a report
from a SELECT query, no obligation to necessary use a table).


Hoping it may help,
Vanderghast, Access MVP

"Scott Bridges" wrote in message
...
I'm very new to Access, and trying to learn SQL at the same time.

I'm working on a database in Access to track calls and correspondence.
Thanks to help from this group I now have a query to pull all contacts
that
have not received a call or letter in the last 45 days. Now, lets say its
time to send out a newsletter to all the contacts. The tables are one to
many, Contacts to Calls.

I have a small form that has fields for Date, Time, Subject and Notes. I
put
in a Date and a Time, the subject of the letter or call and any other
notes.
Since I'm sending this letter out to all of my contacts I need to insert
one
record in the Calls table for each of the contacts in the Contacts table.
The
Date, Time, Subject and Notes all need to come from the form.

How can I build an INSERT statement that will create one record in Calls
for
each record in Contacts and fill in the other four fields Date, Time,
Subject
and Notes from the form?

I'm sorry this post is so long. I'm just trying to be clear. I appreciate
everyone's help and don't want this to be as confusing for you as it is
for
me.

Thanks

Hi,


From what I understand, assuming the form is open, something like:


SELECT a.*, FORMS!FormName!ControlName1, FORMS!FormName!ControlName2
FROM myTable As a



would take all records from table myTable, and "horizontally merge" them to
the two mentioned controls


Hoping it may help,
Vanderghast, Access MVP


"Scott Bridges" wrote in message
...
I want to create a record in my tblCalls table for each of the people in my
tblContacts table. So my JOIN would be on ContactID which exists in both
tables.

I guess I can't explain it very well. If I have 35 people in tblContacts
and
I want to send out a newsletter to all of them, I need to create 35
records
in tblCalls. One record for each of the 35 people in the tblContacts
table. I
have a form with text boxes for Date, Time, Subject and Notes. The form
has a
button to Submit. When I fill in the fields and click Submit, I need to
add
35 new records to tblCalls that each have a ContactID (from tblContacts)
and
the Date, Time, Subject and Notes that are pulled from the form. Does this
help? I hate being ignorant. This database stuff is killing me.

"Michel Walsh" wrote:

Hi,


You make a join with a standard SELECT statement.


If tableA has three records, a, b, and c, while tableB as four records,
1,
2, 3, and 4, then

SELECT tableA.f1, tableB.g1
FROM tableA, tableB



will generate the 12 possibilities

a 1
a 2
a 3
a 4
....
c 3
c 4



You can then change the SELECT statement into an INSERT statement, in the
query designer, from its menu or form the toolbar. The goal is to get the
SELECT statement right, as you want it, and once you are happy with it,
change it for an insert (if you need it: you can build a from or a report
from a SELECT query, no obligation to necessary use a table).


Hoping it may help,
Vanderghast, Access MVP

"Scott Bridges" wrote in message
...
I'm very new to Access, and trying to learn SQL at the same time.

I'm working on a database in Access to track calls and correspondence.
Thanks to help from this group I now have a query to pull all contacts
that
have not received a call or letter in the last 45 days. Now, lets say
its
time to send out a newsletter to all the contacts. The tables are one
to
many, Contacts to Calls.

I have a small form that has fields for Date, Time, Subject and Notes.
I
put
in a Date and a Time, the subject of the letter or call and any other
notes.
Since I'm sending this letter out to all of my contacts I need to
insert
one
record in the Calls table for each of the contacts in the Contacts
table.
The
Date, Time, Subject and Notes all need to come from the form.

How can I build an INSERT statement that will create one record in
Calls
for
each record in Contacts and fill in the other four fields Date, Time,
Subject
and Notes from the form?

I'm sorry this post is so long. I'm just trying to be clear. I
appreciate
everyone's help and don't want this to be as confusing for you as it is
for
me.

Thanks