Append a record multiple times to a new table

[IncentGirl]

I am needing to take an existing table that has one record per person and add
multiple records to a new table. A field in the existing table indicates how
many duplicate records to add to the new table for each person in the
existing table.

For example in TableA
Name Count Location
Joe Bloom 5 HQ

This means I need to add 5 records to the new table for Joe Bloom.
Any suggestions?

 

[John Spencer]

Well, the first thing that comes to mind is - why do you think you need to do
this? It is seldom a good idea - seldom, not always.

The easiest way to do something like this is to have another table that has
one field - a number field (fNumber)with records from 1 to your maximum
number. Now you can use that table and your tableA to add new records to
another table.

INSERT INTO NewTable ([Name], location)
SELECT [Name], Location
FROM TableA, TheNumberTable
WHERE TableA.[Count] <= TheNumberTable.fNumber

If you can only use the Design view query grid
-- Create new query with TableA and theNumberTable
-- Add Name, Location, and Count fields to the grid
(By the way Name and Count are both reserved words and are not good names
for fields. You can run into problems with those names)
-- Set the criteria under TableA.Count to
<=[TheNumberTable].[fNumber]
-- Run the query. If things look correct then
-- Return to design view
-- Select Query: Append Query from the menu and set up the append query.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County