Re: SQL Question; Populating one table with data from another table.


That sounds like the right way to do it. However, I am a newbie when it
comes
to SQL. I successfully made a recordset from table "tblCustomers" with
StatusID <> 2.

But, I failed generating new records in table "tblPayments" based on the
query
from "tblCustomers". I want to generate one new payment (record) for each
Customer in qryCustomers. That is, if I have 18 Customers, then 18 new
payments
should be added to the table "tblPayments".

INSERT INTO tblPayments(CustomerID)
FROM qryCustomers(CustomerID)... (but this does not work, right...?).

I know that the general syntacs is something like this:
INSERT INTO table_name (column)
VALUES (value)
However, I can't figure out how to write my SQL command to make it work?

Any idea?

Thank you in advance.

Jan T.



"Jeff Boyce" <nonsense@xxxxxxxxxxxx> skrev i melding
news:upQ3GuKaGHA.3652@xxxxxxxxxxxxxxxxxxxxxxx
Jan

Can you come up with a query that returns all the customers with StatusID
<> 2 from your ???? table? If so, you can use THAT query as a starting
point to create a new query and convert it to an append query, appending
new payment records.

By the way, I assume you are creating a true payment record (with an
actual payment amount), rather than a dummy payment record. What amount
are you putting in for someone with StatusID <> 2?

Regards

Jeff Boyce
Microsoft Office/Access MVP



"Jan T." <post@xxxxxxxxxxxxxx> wrote in message
news:esPJE7JaGHA.4580@xxxxxxxxxxxxxxxxxxxxxxx
I have a database containing table "tblCustomers" and
table "tblPayments".
Now, I want to run a query to Insert new payments for certain
customer that have some criteria. That is, if the customer has
StatusID <> 2, a new record should be added to the table
tblPayments. Is this possible to do in a query?

The table tblPayments has the following fields:

PaymentID
CustomerID
PaidAmount
Date

I tried to do this with VBA but it failed. It would probably be
much easier to do it with SQL.

Any help would be very much appriciated. Thank you in advance.

JGT.





.

Relevant Pages

  • Re: [PHP] OOP slow -- am I an idiot?
    ... OOP has overhead. ... That saves you putting wasteful SQL queries in your ... If you need to do a complex query with a couple of joins and such, ... I want to create a "customer" class which fetches its attributes from a ...
    (php.general)
  • Sub Query Consolidation, Syntax Question
    ... I am new to writing free hand SQL, and have a two part question. ... #1 I'm trying to consolidate a number of queries into one query. ... to me that there should be a way to consolidate this into one place, ... Information] RIGHT JOIN NetShipPlusOpen ON [Customer Information].[Sold To ...
    (microsoft.public.access.queries)
  • Efficient coordinated queries??
    ... A large chunk of it can be brought in with a single query and each row represents an item to process. ... For a hypothetical example consider a customer order database where we need to process through all customers and can bring in a row of customer data as our main query but there are 0..n1 rows of customer order data, 0..n2 rows of customer feedback information, 0..n3 rows of customer discounts available, etc. ... Instead I want to bring in the data as individual DataSets representing an item to process. ... Fully denormalize in a gi-normous SQL query and go back to just a single row of data per item. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: comboBox & northwinds sample orders form
    ... True, but OFTEN to display a customer name when you have a customer id, we ... dlookup) are suggest. ... the problem is that you don't need dlookup in a query. ... You can always just shove in the sql in place of the ...
    (microsoft.public.access.formscoding)
  • Re: Query going wrong
    ... statement from the Query. ... I am not a big programmer so I have no clue what it means when it's in SQL ... >> When I goto the Report Query Report it prints all the same information for ... >> Then when I am in a query where one customer has had the same service ...
    (microsoft.public.access.queries)