Adding Multiple Records

[drummerian]

Hi ppl,
Basically i want to use a query to add multiple 'blank' records to a table. I have a family table (each family has a unique family ID) and i need to put a blank record into a payments table (say once a month) with their family ID and the current amount paid as 0 as well as some information a user can put in via a form.
So far this is what i've got, although i know to put multiple records into the table i need to use a different type of SQL statment?

INSERT INTO SubsOwed
VALUES (

.[Family].[FamilyID], [forms].[frmUpdateSubs].[termid], [forms].[frmUpdateSubs].[owedfrom], 0);

Thanks ppl

 

[jrbarnett]

INSERT INTO SubsOwned (Field1, Field2, Field3, Field4)
SELECT F1, F2, F3, F4
FROM YourTable
WHERE f1 = 'X'

Before this type of query will run you need to ensure:
* The data to be inserted must meet criteria to pass any check constraints and referential integrity and all mandatory fields must have data in them.
* Primary key values being inserted must not be used already in the existing destination table.

John

 

[drummerian]

how does this add multiple families?

 

[jrbarnett]

It copies multiple records from one table to another based on data in that table.

If the data is not in other tables, you have to run multiple insert statements of the type you wrote - one line per record of data.

John

 

[drummerian]

ahh i think i see, so would i have:

INSERT INTO SubsOwed (

.[Family].[FamilyID], [forms].[frmUpdateSubs].[termid], [forms].[frmUpdateSubs].[owedfrom], 0)
SELECT FamilyID
FROM Family
WHERE f1 = 'X'

 

[jrbarnett]

No, the items inside the brackets relate to fields in the SubsOwned table.
Its the items in the select statement that refer to the source data in other tables.

John

 

[dhookom]

There is a "recurring" sample database at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

that you should be able to modify to meet your needs. It creates scheduling records but it should convert to payment records.

Duane MS Access MVP