For emphasis I am going to shout the next bit
AS THE FOLLOWING WILL MAKE CHANGES TO YOUR DATA WHICH CANNOT BE UNDONE TEST
THIS ON A COPY OF YOUR DATABASE FIRST AND BACKUP YOUR DATABASE BEFORE MAKING
THE FINAL RUN ON YOUR LIVE DATA
You can do this with a single query statement.
You need to create a table which just holds the period numbers (called
Periods in the example below) with a single field (called period in the
example below) you then fill the table with each period e.g.
Period
1
2
3
4
5
6
7
8
9
10
11
12
In the example below it is assumed that the table you quote is called
PeriodsAccount with the two field names you quote.
Create a new query
Change to SQL view
Paste in the following SQL
INSERT INTO PeriodsAccount ( [A/C #], Period )
SELECT a.[A/C #] , a.Period
FROM
(SELECT DISTINCT PeriodsAccount.[A/C #], Periods.Period
FROM Periods, PeriodsAccount) as a
Left Join PeriodsAccount as b
ON a.[A/C #] = b.[A/C #]
AND a.Period = b.Period
WHERE b.[A/C #] Is Null
Edit the SQL for table and field names.
Check the output by switching to datasheet view
Run the query.
and again for emphasis
AS THE ABOVE WILL MAKE CHANGES TO YOUR DATA WHICH CANNOT BE UNDONE TEST THIS
ON A COPY OF YOUR DATABASE FIRST AND BACKUP YOUR DATABASE BEFORE MAKING THE
FINAL RUN ON YOUR LIVE DATA.
--
Terry Kreft
"Cate" <ca***************@chapmantripp.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
Hi all
Using A2K, I have a table which lists account numbers and details for
12 periods (a year's worth) for each account:
A/C # Period
============
1001 1
1001 2
1001 3
For any account, any particular period may be missing (so there may be
no row for, say, period 10 for Account 1001). Is there any way I can
loop through the table and create records for the missing periods?
Thanks in advance