Posted on 2005-04-21
Last Modified: 2012-06-21
Hi, I have a Marketing database in which are 2 tables, one called "Purchases" and the other called "smsout". I have a query called "promo" which extracts all records from the cellular number field called "SMS" from the "purchases" table. The query as seen below is based on those customers who have bought items from a particular category:

FROM Customer INNER JOIN purchase ON Customer.ID = purchase.[Customer ID]
WHERE (((purchase.Item) Like "Flowers"));

My question is this, I would like to create an Update/Create query that would transfer the results of the "promo" query into a new table called "smsout1" with the following fields:
* "From" - which would be automatically populated with my contact number (i.e. 07958356777) for every record in the new table.
* "To" - which would be where the numbers from the promo query would be extracted to.
* "Message" - which is the message I would be sending to them.

I have tried to create an update query for this with no joy.
Question by:scameron03
    LVL 15

    Accepted Solution

    save a copy of the promo query as "mktable_promo"

    Change the query to a make table - and give it the table name you want

     and add new field names with an expresson.

    i.e. from:"07958356777"
    message:"insert message here"
    in new columns.

    there query sql will look like:

    SELECT Customer.SMS as To, "07958356777" as From, "insert message here" as Message
    into your_table_name
    FROM Customer INNER JOIN purchase ON Customer.ID = purchase.[Customer ID]
    WHERE (((purchase.Item) Like "Flowers"));

    eventually though you might want to create you own table with, id, from and message.. so you can add this in to the query and specify the ID you want for your from and message value.
    LVL 15

    Expert Comment

    by:Simon Ball
    many thanks.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now