Add activity for each customer

[nostr4d4m]

Hej,

Table 1: Customer primary key customer Id, other fields: CustomerName and SendInvitation (Yes/no)
Table 2: Activities primary key activityId, foreign key customerId. Another field: Name, description


I need to create query that would create new activity for each customer that has SentInvitation = yes

To make it mo clear:
Lets say 5 out of 2 customers has SentInvitation field value equal to yes



CustomerId, CustomerName, SendInvitation
1, Tom, yes
2, john, no
3, Petter, yes
4, Lisa, no
5, Alex, no

What i want is after running a query that only requires me to enter activity description. After running it my Activities table should have 2 new records that should look like that:

ActivityID, customerId, CustomerName, Description
autonumber, 1, Tom, "Come visit me"
autonumber, 3, Petter, "Come visit me"

Or more in other words

For each Customer that has sendinvitation = yes Do
Create new activities record with values:
ActivityID - auto-generated , customerId = customers.CustomerId , CustomerName = customers.CustomerName , Description - user provided

[pbaldy]

This get you started?

INSERT INTO Activities(...)
SELECT ...
FROM Customer
WHERE SendInvitation = True

[nostr4d4m]

Hej,

Thanks for fast replay, but could you give me one full code for the example i gave you.
I am kinda newbie in access. Still have trouble moving data from one table to another and still struggling with SQL

[pbaldy]

Did you get this sorted out? If you start a regular query on the customer table and change it to an append query, you should be able to do it all in design view.

[nostr4d4m]

Nah, no success yet. I been trying it too

[orange]

See last example here
http://beginner-sql-tutorial.com/sql...-statement.htm

[nostr4d4m]

Ok now i can insert some stuff into activities. But how i define description value? Check google and all examples has either all values selected from other table or hardcoded ( Values(...) )
I need to select few values from table customers and other values i want to define myself, as description, created, activity type as customer table does not got any info about it.

[pbaldy]

How do you want to define it? It's easy to refer to a form control to get input for the query.

[nostr4d4m]

ok than i write:

Insert into T_Customers(name, description)
From T_Activities ;


but if activities does not have field named description? how do i define it in this example if description should be taken from Form!MyForm!description

[pbaldy]

SELECT Name, Forms!MyForm.description
FROM T_Activities

[nostr4d4m]

ok i played with it for a while but can find the right order to put it. So my Query looks like this:

INSERT INTO ActivityHistory ( CompanyId, CompanyName, Description)
SELECT Companies.CompanyId, Companies.CompanyName
FROM Companies
WHERE (((Companies.ResendMail)=True));

I also need to insert a Description that should be taken from field:
Forms![InsertActivity]![Comment]

How do i add it here?

[pbaldy]

SELECT Companies.CompanyId, Companies.CompanyName, Forms![InsertActivity]![Comment]