creating multiple records (with queries?)

[smartascii]

I'm working on a contact database for my company's sales efforts. It tracks interactions with prospective customers and assigns them to employees here. My question is this: For mass contact efforts (such as mailings), is there a way to create mass entries to my "Interactions" table so that no one has to enter a mailout interaction 1000+ times?

Structure of the relevant bits of the database is as follows:

Contacts Table
Interaction Table

Contacts Table is linked with Interaction table via ContactID

Queries are run to narrow Contacts by various criteria (ex.: by the employee they're assigned to).

So if I run a query to get all the contacts assigned to me, and there are 450 of them, and I send a mailout to all of them, and I want to add an interaction to those 450 people, is there an automated way to do this?

Any pointers would be welcomed.

 

[pbaldy]

Sure, an append query with the same criteria:

INSERT INTO Interaction(Field1, Field2)
SELECT Field1, Field2
FROM Contacts
WHERE SameCriteriaHere

You can includes fields from a form in the SELECT clause if you want to get values from there.

 

[smartascii]

Thanks for the quick response. You'll have to speak to me as though I'm a bit slow, because when it comes to Access, I am.

I think that what you've written there is essentially a SQL command, and that queries can be made to run those.

I also see what you're saying about getting the field information from forms. What I'm not quite connecting is how you tell it that it needs to apply the "Interaction" records being created to a specific list of "ContactIDs"

Is it as follows?

INSERT INTO Interaction(Field1, Field2, etc.)
SELECT Field1, Field 2, etc.
FROM Form
WHERE Contact ID = Query.ContactID

Or something? I'm trying to wrap my head around the logic.
And once I get the syntax right, how do I build this into a form where users can do it without needing to know any of this?

 

[pbaldy]

Not really. You said you had a query that returned the 450 records. Presumably that query has a criteria. In SQL view, it might look like

SELECT Field1, Field2
FROM Contacts
WHERE AssignedTo = "smartascii"

So I'm saying to use the same criteria in this query:

INSERT INTO Interaction(Field1, Field2, Field3)
SELECT Field1, Field2, Forms!FormName.TextboxName As Field3
FROM Contacts
WHERE AssignedTo = "smartascii"

In that example, Field3 in the Interaction table would be populated from a form, the other two from the contacts table.

 

[smartascii]

Thanks, Paul

That makes perfect sense! Now, will making this seamless enough for users to do by themselves require code, or is there another way I don't know about to have this happen without it?

 

[pbaldy]

Well, if you can create a saved query that gets the criteria from a form or something, you can simply execute that query. That would be seamless to the user and pretty simple to set up.