By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Find gaps in a sequence and insert rows

P: n/a
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

May 23 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On 22 May 2006 17:05:36 -0700, "Cate"
<ca***************@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.
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


May 23 '06 #2

P: n/a

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

May 23 '06 #3

P: n/a
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.
May 23 '06 #4

P: n/a
Thanks for all the help - appreciated.

May 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.