Repeating records based on a number in a field

[Guest]

I need to variably repeat records based on a field in my table. The table is
simple, I have a Zipcode field, a Carrier Route field, and a Copies field.
What I want is to repeat the zip and carrier the number of times that is in
the copies field. Is there anyway to simply do this? So if the copies field
has a 6, the query should create a table that has the zip and carrier
repeated 6 times. Then the next record should do the same, and append to the
new table. Any help would be greatly appreciated.

 

[Tom Ellison]

Dear Confusled:

Create a 1 column table with numbers in it. Put all the consecutive numbers
from 1 to N in this table, where N is the maximum number of repeats you
could ever want.

In a query, use this table in a cross product with your existing rows, and
filter it where this N is <= the number of repetitions you want.

Tom Ellison

 

[Guest]

I may be a novice, but I am not sure I understand what you mean by a cross
product, and filtering. Thanks thus far though, been trying to follow your
direction.

 

[Tom Ellison]

Dear Confusled:

Here's a sample:

SELECT T.Zipcode, T.CarrierRoute
FROM YourTable T, Numbers N
WHERE N Value <= T.Copies

That's about it.

In the FROM line, having two tables (or queries) separated by a comma is a
cross product.

The WHERE line is a filter.

In the above I have assumed a table Numbers containing values 1 to N, where
N is the maximum number of copies desired.

Tom Ellison

 

[Vincent Johns]

I agree with what Tom Ellison is suggesting, but I thought a numerical
example might also be helpful.

First, your Table with the numbers might look like this (it gives your
Query something to hang onto):

[Numbers] Table Datasheet View:

Value
-----
1
2
3
4
5
6
7
8
9

If you want more than 9 copies of some record, add more numbers to this
Table.

Your Zip Code Table would contain more fields than I show here, but
you'd probably have at least these three:

[YourTable] Table Datasheet View:

Zipcode CarrierRoute Copies
------- ------------ ------
11234 1803 6
99112 9227 2

The Query to give you the list you want will contain a filter (the
expression in the SQL below that contains the "<=") that will let it
produce ONLY the records where the number of the copy is no more than
than the [Copies] field.

The tricky part is the Cartesian product (which Tom referred to as a
cross product). What's happening here is that you create a large set of
combined records by grabbing each one from the first Table and attaching
it to each of the records in the other Table. In my example, the
product would be 2 from [Zips] times 9 from [Numbers] -- all
combinations would be present. But the filter hides all but those with
a small enough [Numbers] part, so you get only 6 from the first [Zips]
record and 2 from the other one. (This is the same as Tom's Query,
except for the ORDER BY, which isn't really necessary.)

[Q_Zips] SQL:
SELECT T.Zipcode, T.CarrierRoute
FROM YourTable AS T, Numbers AS N
WHERE (((N.Value)<=[T]![Copies]))
ORDER BY T.Zipcode;

If done correctly, the result, a combination of a Cartesian product and
a filter, gives you just the set of records you want.

[Q_Zips] Query Datasheet View:

Zipcode CarrierRoute
------- ------------
11234 1803
11234 1803
11234 1803
11234 1803
11234 1803
11234 1803
99112 9227
99112 9227

Here's a cautionary note: Unless there is some reason that you must
store these results into a Table, I suggest leaving this dataset in the
form of a Query. You can base other Queries, as well as Forms or
Reports, on this Query pretty much the same as you could base them on a
Table, and the Query is easier to maintain, and it probably takes less
space in your database file. Storing these values into a Table (when
you have essentially the same information elsewhere in your database)
exposes you to extra maintenance problems in having to update the same
information more than one place, and headaches if somehow the updating
isn't done consistently (forcing you to have to decide which of the
differing values is correct).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

I agree with what Tom Ellison is suggesting, but I thought a numerical
example might also be helpful.

First, your Table with the numbers might look like this (it gives your
Query something to hang onto):

[Numbers] Table Datasheet View:

Value
-----
1
2
3
4
5
6
7
8
9

If you want more than 9 copies of some record, add more numbers to this
Table.

Your Zip Code Table would contain more fields than I show here, but
you'd probably have at least these three:

[YourTable] Table Datasheet View:

Zipcode CarrierRoute Copies
------- ------------ ------
11234 1803 6
99112 9227 2

The Query to give you the list you want will contain a filter (the
expression in the SQL below that contains the "<=") that will let it
produce ONLY the records where the number of the copy is no more than
than the [Copies] field.

The tricky part is the Cartesian product (which Tom referred to as a
cross product). What's happening here is that you create a large set of
combined records by grabbing each one from the first Table and attaching
it to each of the records in the other Table. In my example, the
product would be 2 from [Zips] times 9 from [Numbers] -- all
combinations would be present. But the filter hides all but those with
a small enough [Numbers] part, so you get only 6 from the first [Zips]
record and 2 from the other one. (This is the same as Tom's Query,
except for the ORDER BY, which isn't really necessary.)

[Q_Zips] SQL:
SELECT T.Zipcode, T.CarrierRoute
FROM YourTable AS T, Numbers AS N
WHERE (((N.Value)<=[T]![Copies]))
ORDER BY T.Zipcode;

If done correctly, the result, a combination of a Cartesian product and
a filter, gives you just the set of records you want.

[Q_Zips] Query Datasheet View:

Zipcode CarrierRoute
------- ------------
11234 1803
11234 1803
11234 1803
11234 1803
11234 1803
11234 1803
99112 9227
99112 9227

Here's a cautionary note: Unless there is some reason that you must
store these results into a Table, I suggest leaving this dataset in the
form of a Query. You can base other Queries, as well as Forms or
Reports, on this Query pretty much the same as you could base them on a
Table, and the Query is easier to maintain, and it probably takes less
space in your database file. Storing these values into a Table (when
you have essentially the same information elsewhere in your database)
exposes you to extra maintenance problems in having to update the same
information more than one place, and headaches if somehow the updating
isn't done consistently (forcing you to have to decide which of the
differing values is correct).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

I agree with what Tom Ellison is suggesting, but I thought a numerical
example might also be helpful.

First, your Table with the numbers might look like this (it gives your
Query something to hang onto):

[Numbers] Table Datasheet View:

Value
-----
1
2
3
4
5
6
7
8
9

If you want more than 9 copies of some record, add more numbers to this
Table.

Your Zip Code Table would contain more fields than I show here, but
you'd probably have at least these three:

[YourTable] Table Datasheet View:

Zipcode CarrierRoute Copies
------- ------------ ------
11234 1803 6
99112 9227 2

The Query to give you the list you want will contain a filter (the
expression in the SQL below that contains the "<=") that will let it
produce ONLY the records where the number of the copy is no more than
than the [Copies] field.

The tricky part is the Cartesian product (which Tom referred to as a
cross product). What's happening here is that you create a large set of
combined records by grabbing each one from the first Table and attaching
it to each of the records in the other Table. In my example, the
product would be 2 from [YourTable] times 9 from [Numbers] -- all
combinations would be present. But the filter hides all but those with
a small enough [Numbers] part, so you get only 6 from the first
[YourTable] record and 2 from the other one. (This is the same as Tom's
Query, except for the ORDER BY, which isn't really necessary.)

[Q_Zips] SQL:
SELECT T.Zipcode, T.CarrierRoute
FROM YourTable AS T, Numbers AS N
WHERE (((N.Value)<=[T]![Copies]))
ORDER BY T.Zipcode;

If done correctly, the result, a combination of a Cartesian product and
a filter, gives you just the set of records you want.

[Q_Zips] Query Datasheet View:

Zipcode CarrierRoute
------- ------------
11234 1803
11234 1803
11234 1803
11234 1803
11234 1803
11234 1803
99112 9227
99112 9227

Here's a cautionary note: Unless there is some reason that you must
store these results into a Table, I suggest leaving this dataset in the
form of a Query. You can base other Queries, as well as Forms or
Reports, on this Query pretty much the same as you could base them on a
Table, and the Query is easier to maintain, and it probably takes less
space in your database file. Storing these values into a Table (when
you have essentially the same information elsewhere in your database)
exposes you to extra maintenance problems in having to update the same
information more than one place, and headaches if somehow the updating
isn't done consistently (forcing you to have to decide which of the
differing values is correct).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Tom,
OK, Got it. Took a little bit of tweaking to fit my tables etc... of
course, but it worked awesome!! Thank you!!

Jack Houle