Find gaps in a sequence and insert rows

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

re: Find gaps in a sequence and insert rows

On 22 May 2006 17:05:36 -0700, "Cate"
<catherine.devries@chapmantripp.com> wrote:

Use some VBA code to loop over the records in this table, and add what
was not found. Keywords: OpenRecordset, MoveNext, AddNew, Update.

-Tom.


[color=blue]
>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[/color]

re: Find gaps in a sequence and insert rows

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" <catherine.devries@chapmantripp.com> wrote in message
news:1148342736.455831.137760@y43g2000cwc.googlegr oups.com...[color=blue]
> 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
>[/color]

re: Find gaps in a sequence and insert rows

Yet another way:

Use a crosstab query, using Period as the column heading, A/C# as the row
heading, and a count of either column as the Value. Assuming that you have
at least one row with each period anywhere in the table, you could look for
null values in the period columns.

Strictly speaking Terry Kreft's answer is the better one. If you know the
periods in advance, then run a setup process at some point or periodically
to populate a table with all the account/period combinations, then use a
third column to contain (or not contain) a value, then test that column.

But the crosstab can be useful and requires no change to the table structure
and use. It's main deficiency is that you must do something to guarantee
that you get all the columns you require, and even that's pretty easy in
many cases. For example:

TRANSFORM Count(AccountsByPeriod.ID) AS CountOfID
SELECT AccountsByPeriod.Account
FROM AccountsByPeriod
GROUP BY AccountsByPeriod.Account
PIVOT AccountsByPeriod.Period in (1, 2, 3, 4);

Note the expression "in (1, 2, 3, 4)". In my example, I am assuming only 4
periods. The "in" expression guarantees that all four columns appear,
regardless of data present in the table. Yours would extend, presumably, to
(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) if the periods are months. As long
as you know ahead of time all the possible periods, the crosstab could do
the whole job.

re: Find gaps in a sequence and insert rows

Thanks for all the help - appreciated.