If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
updating graphics inserted with Insert and Link option | Ann Pillman | General Discussion | 1 | October 11th, 2004 04:04 PM |
Insert another doc as second or third page? | Mike Fitz mfitzge9 @ nycap.rr.com | New Users | 0 | April 28th, 2004 02:44 PM |
Increment column value based on insert string | A.W.J. Ales | Worksheet Functions | 1 | April 15th, 2004 02:44 PM |
Increment column value based on insert string | Bharnick | Worksheet Functions | 0 | April 15th, 2004 01:08 PM |