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

[Jan T.]

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.

 

[Jeff Boyce]

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.]

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]

Jan

You are not limited to using SQL statements only, are you?

Could you create an Append query (create a query that returns what you want,
including the new payment amount, then convert it to an Append query by
clicking on the Query menu choice in query design mode)?

I'm still not clear on something. You haven't mentioned an amount. If you
are intending to create "blank" payment records, reconsider.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jan T.]

Yes, I make "blank" payment records and then the plan was to update
theese fields via a form and an update query. That way I can see all
payments that is not completed and update them as they are paid one
by one. (Invoices are sent out just once a year for theese customers).

The form is almost finished except for the update query so I hoped I
could use it.

Several times I have tried to convert my query to an Append query,
but it is failing. Find it difficult to write.

Regards
Jan

PS.
I probably need the excact SQL statement I think. This feels a little
complicated for a newbie. The to tables are:

tblCustomers tblPayments
------------- -------------
CustomerID pk PaymentsID pk
FirstName CustomerID pk
LastName Amount
Address Date
City
StatusID


Hope this would be sufficient to build the SQL Append sentence.

If possible, it will be very much appriciated. Thanks a lot.

Jan T.

 

[Jeff Boyce]

Jan

I'll continue to recommend against adding a "blank" payment record, simply
so you can look for blanks to fill in later.

An alternate approach, and one that would not add lines before their time,
would be to use a query to find folks who do not have payments. One
advantage to this approach is that it is dynamic.

If you are determined to add blank rows, you can also check Access HELP for
Append queries for more assistance with syntax and menu choices.

Best of luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jan T.]

Well, thank very much for your help. I will try to see Access Help for
Append queries as you recomended.

Regards
Jan