Reading a field(s) in a table to build a new record for insertion into a new table

[LittleRock Z]

Objective is to provide a list of all names in a table/record and show the record (LOT) it is from.
I am trying to help a church manage their cemetery lots and plots. There is one table and each record is a LOT and contains all the information about the 8 PLOTS contained in a LOT. There can be 8 names recorded in any one LOT record. One name for each of the 8 PLOTS in the LOT. To arrive at a list of all the names, in any / every LOT I plan to read each record and write each name and associated LOT number into a new table. I can then create a form listing all names, sorted by name. I did not design the database, I am trying to deal with what I got. There must be many solutions, but I want to do it with VB code. How to open and read the input table SHEET1 and insert multiple records ina table of ALLNAMES, of multiple names found in any one LOT record in SHEET1. I don't expect the reply to answer the whole thing, just the DIM statements to open the the table and insert records into ALLNAMES

[CJ_London]

So you are trying to normalise your data from an excel spreadsheet? So currently you have say

LOT....PLOT1....PLOT2....PLOT3 etc
1.......Harry.....Mary.......George etc?

and what you want is

LOT...PLOT...OccupiedBy
1.......1.......Harry
1.......2.......Mary
1.......3.......George
etc

If so, why do you want to use VBA? Much easier and faster to use SQL.

In SQL it would be

INSERT INTO newTable (LOT,PLOT, OccupiedBy)
SELECT LOT, 1, PLOT1 FROM sheet1
UNION SELECT LOT, 2, PLOT2 FROM sheet1
UNION SELECT LOT, 3, PLOT3 FROM sheet1
etc

in VBA it would be

dim sqlstr as string
dim db as dao.database

set db=currentdb
sqlstr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) SELECT LOT, 1, PLOT1 FROM sheet1"
db.execute sqlstr

sqlstr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) SELECT LOT, 2, PLOT2 FROM sheet1"
db.execute sqlstr

etc

both methods will create records for all LOTS - you can remove them afterwards or prevent them from being added by excluding them with criteria

You want to do it the painfully slow way? (known as RBAR)

dim rs as dao.recordset
dim db as dao.database
dim sqlStr as string

sqlStr="SELECT * FROM sheet1"
set rs=db.openrecordset(sqlstr)

while not rs.eof
    sqlStr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) Values("& rs!LOT & ", 1, '" & rs!PLOT1 & "')"
    db.execute sqlstr
    sqlStr="INSERT INTO newTable (LOT,PLOT, OccupiedBy) Values("& rs!LOT & ", 2, '" & rs!PLOT2 & "')"
    db.execute sqlstr
    etc

    rs.movenext

wend

[Micron]

There is one table

As Nancy Reagan said, "Just say NO" to that. That is spreadsheet thinking and does not make for a good relational database design. OK, there are minor exceptions for a flat file but they are rare cases. I wonder if the reason you inherited it is because the design became too much hassle for someone else. VBA is not what you need.

Off the cuff I'd say you need at least 3 tables (lots, plots, and whatever you call those who are interred) and that would not allow for a table for churches or cemetery owners. Also, what if the plot has one interred above another? How would you manage that with one table? Lots and plots would not be that much different from columbariums, walls and niches I think. While I've never used my columbarium db (it was just an exercise in managing the concept) it sure isn't all packed into one table. Not saying you'd need the same since it was meant to be used in any country and handle sales, but it might need to look a bit closer to this than what you have:

[orange]

I agree with CJ and Micron that a normalized structure would be the starting point. You may not need all the tables and detail suggested in Micron's data model, but it certainly shows how the various entities are related. You can adapt his model to suit your requirement. It is quite eye-opening to review some cemetery/burial plots/cremation and columbarium software and see some combinations/options.
Here is a link to 8 Best Cemetery Software it may give you another view of the things involved.

Here is a link to a thread regarding Database Design of a Burial Register that may help with context and approach.