Field Based record creation

S

Senexis

Hello,

I have a question concerning taking data that I have, and creating multiple
records (rows) of the same record based off of a number in that record. I
think this is a real easy solution, probably involving a cross query... But
I'm having trouble setting it up.

Example.

I would like to take data in a table that looks like this:

Candy..........P/N..........O/H QTY..........Barcode
Snickers.......34kf93.......00005............3453453
Kisses.........fg4yjh3......00002............8849993
Cookie.........sd3hddd......00001............4564532
Life Savers....465fd4hs.....00004............3452564

And make it look like this:

Candy..........P/N..........O/H QTY..........Barcode
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Kisses.........fg4yjh3......00002............8849993
Kisses.........fg4yjh3......00002............8849993
Cookie.........sd3hddd......00001............4564532
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564


I may have to account for the O/H QTY to show for a quantity of 00001 in
every record times whatever the original O/H QTY.

Thank you!

-Senexis.
 
J

John Vinson

Hello,

I have a question concerning taking data that I have, and creating multiple
records (rows) of the same record based off of a number in that record. I
think this is a real easy solution, probably involving a cross query... But
I'm having trouble setting it up.

Example.

I would like to take data in a table that looks like this:

Candy..........P/N..........O/H QTY..........Barcode
Snickers.......34kf93.......00005............3453453
Kisses.........fg4yjh3......00002............8849993
Cookie.........sd3hddd......00001............4564532
Life Savers....465fd4hs.....00004............3452564

And make it look like this:

Candy..........P/N..........O/H QTY..........Barcode
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Kisses.........fg4yjh3......00002............8849993
Kisses.........fg4yjh3......00002............8849993
Cookie.........sd3hddd......00001............4564532
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Click to expand...

You can certainly genereate a *query* to do this - I think it would be
a Very Bad Idea to store this (redundant and repeating) data in a
Table though.

Create a table named NUM, with one long-integer field N. You can use
Excel Fill Down - Increment to create a list with N from 1 through
65536 or so. Copy and paste (or Import) this into Access; make N the
Primary Key (and only field) of the table.

Now create a query

SELECT yourtable.Candy, yourtable.[P/N], yourtable.[O/H QTY],
yourtable.[Barcode], Num.N
FROM yourtable, Num
WHERE N <= Val([O/H QTY];

This "Cartesian join" query will give you as many duplicates of the
record as needed based on [O/H QTY]. You can base a Form, report, or
export on this Query without the overhead of storing the data in a new
table.

John W. Vinson[MVP]
 
K

Ken Snell \(MVP\)

Do you need to do this as a "regular" query? I do not know of a way to do it
that way. However, one can envision using VBA programming to create the
appropriate SQL statement that would produce the results, where the code
would read the field's value and then generate a long "UNION" query that
would produce the desired results.

Pardon me for asking, but this is a very unusual type of query output that
you seek. What do you want to do with the output? There may be a better way
to approach the problem if you tell us more about why and what you want to
accomplish.
 
S

Senexis

This worked exactly like what I wanted, thank you! :)

John Vinson said:
Hello,

I have a question concerning taking data that I have, and creating multiple
records (rows) of the same record based off of a number in that record. I
think this is a real easy solution, probably involving a cross query... But
I'm having trouble setting it up.

Example.

I would like to take data in a table that looks like this:

Candy..........P/N..........O/H QTY..........Barcode
Snickers.......34kf93.......00005............3453453
Kisses.........fg4yjh3......00002............8849993
Cookie.........sd3hddd......00001............4564532
Life Savers....465fd4hs.....00004............3452564

And make it look like this:

Candy..........P/N..........O/H QTY..........Barcode
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Snickers.......34kf93.......00005............3453453
Kisses.........fg4yjh3......00002............8849993
Kisses.........fg4yjh3......00002............8849993
Cookie.........sd3hddd......00001............4564532
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Life Savers....465fd4hs.....00004............3452564
Click to expand...

You can certainly genereate a *query* to do this - I think it would be
a Very Bad Idea to store this (redundant and repeating) data in a
Table though.

Create a table named NUM, with one long-integer field N. You can use
Excel Fill Down - Increment to create a list with N from 1 through
65536 or so. Copy and paste (or Import) this into Access; make N the
Primary Key (and only field) of the table.

Now create a query

SELECT yourtable.Candy, yourtable.[P/N], yourtable.[O/H QTY],
yourtable.[Barcode], Num.N
FROM yourtable, Num
WHERE N <= Val([O/H QTY];

This "Cartesian join" query will give you as many duplicates of the
record as needed based on [O/H QTY]. You can base a Form, report, or
export on this Query without the overhead of storing the data in a new
table.

John W. Vinson[MVP]
Click to expand...
 
S

Senexis

Ken,

Thank you for answering! I'm trying to use existing data off a massive
report with tons of data, to create a list of for every item off an
inventory. The report however does not have repeating lines for each
authorized quantity. So I needed a way to create a record for each
authorized quantity.

I also thought of either a crossquery or a complex union query could do it,
but I just didn't know how to set it up, I would have never thought of a
using a regular one like John Vinson. He did a neat trick using a query and
a <=Val expersion to return exactly what I was looking for to answer this.

Thanks!

-Senexis.
 
Top