Append Query

[Dan]

I would like to creat an Append Query using data from two separate tables to
create new records in a third table.

Table A has an 'ID' field that falls under a 'Section'. Each Section can
have multiple 'ID's

Table B has an 'InspectionID' field.

I want to create an Append Query that uses my selected 'Section' from Table
A and selected 'InspectionID' from Table B to create in Table C new records
for each ID taht falls under that 'Section'.

If I choose Section One and Section One has ID's 1,2,3,4,5 from Table A and
I choose InspectionID of 2010(4)SI from Table B, then I want Table C to be
updated as follows

ID InspectionID
1 2010(4)SI
2 2010(4)SI
3 2010(4)SI
4 2010(4)SI
5 2010(4)SI

Thanks
Dan

 

[Ken Snell]

Your description of the tables' data and structure is not complete. What is
a "section" in table A? How are table A and table B related, if at all?
Please show us all the fields from both tables.

 

[Dan]

My database handles my Inspection Program, and Table A contains my checklist
Items. Each checklist is broken down by Staff, Area, Section. This data is
stored in Tbl_InspectionSections which has a one-to-many relationship with
Table A (Tbl_ChecklistItems) with the section field as the common field.

So in Table A, there are fields that contain each checklist items, text,
regulation, max score, etc, in addition to which section it belongs to and
which ID number it is assigned.

I then have Table C (Tbl_ChecklistItemResults) that contains all the results
from each item as it is used in different inspections. Each record contains
two identifiers, one is the ID number to associate the correct result with
the correct checklist item, the second is the InspectionID to identify which
inspection it belongs to. Table A (ChecklistItems) is one-to-many to Table C
(ChecklistResults) using the ID number as the common field.

Table B is a separate table without relationships that I use to generate and
store a unique InspectionID code and attributes about each inspection.

Not every inspection uses every section, so when I preplan for the
inspection, I want to prepopulate records in the ChecklistResults (Table C)
as I identify the sections that are used. This will make it easier for me to
load the actual results since the records are precreated.

If you need more info, please let me know

Dan

 

[Ken Snell]

OK, assuming that you're providing the Section and InspectionID from
controls on a form:

INSERT INTO Tbl_ChecklistItemResults
(ID, InspectionID)
SELECT Tbl_ChecklistItems.ID, Forms!FormName!IDControlName
FROM Tbl_ChecklistItems
WHERE Tbl_ChecklistItems.Section = Forms!FormName!SectionControlName;

--

Ken Snell
http://www.accessmvp.com/KDSnell/