Add Records based on Value

[dmbfanmd]

Here is a tricky situation that I've run into. I have a table with
about 100 records, and each record has an sku and the quantity on
hand. I want to put one record into a new table for as many pieces of
inventory I have. For example:

SKU Quantity
001 2
002 4

I need the new table to have

SKU
001
001
002
002
002
002

Any ideas?

 

[Jeff Boyce]

You've described 'how' you want to do something.

If you describe a bit more about "why" you want to, and "what" you expect
you'll be able to do as a result, the newsgroup readers may be able to offer
more specific, appropriate suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[banem2]

You've described 'how' you want to do something.

If you describe a bit more about "why" you want to, and "what" you expect
you'll be able to do as a result, the newsgroup readers may be able to offer
more specific, appropriate suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I agree with Jeff!

However, one solution is to use VBA code which loops through first
table and adds records to second table as there are quantity for each
SKU. Something like this (air code, possible errors):

Dim rst As Recordset, rst2 As Recordset
Dim i As Integer, k as Integer
Set rst = CurrentDb.OpenRecordset("tblSourceTableName")
set rst2 = CurrentDb.OpenRecordset("tblDestTableName")

With rst
Do Until .EOF = True
i = nz(!Quantity)
For k = 0 to i
rst2.Append
rst2!SKU = !SKU
rst2.Update
Next i
Loop
End With
Set rst = Nothing: Set rst2 = Nothing

Regards,
Branislav Mihaljev

 

[pietlinden]

Here is a tricky situation that I've run into. I have a table with
about 100 records, and each record has an sku and the quantity on
hand. I want to put one record into a new table for as many pieces of
inventory I have. For example:

SKU Quantity
001 2
002 4

I need the new table to have

SKU
001
001
002
002
002
002

Any ideas?

SELECT pt.MasterID, pt.Tickets, Counters.Counter
FROM Counters INNER JOIN pt ON Counters.Counter <= pt.Tickets;

"Counters" is just a table with a single field, an autonumber with
values from 1 to Max(pt.Tickets) So you'd base your report or
whatever on the query and you're good to go. The only time you'd ever
need to change it is when you need more labels or whatever than the
maximum if Counters.Counter.

 

[dmbfanmd]

You've described 'how' you want to do something.

If you describe a bit more about "why" you want to, and "what" you expect
you'll be able to do as a result, the newsgroup readers may be able to offer
more specific, appropriate suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP








- Show quoted text -

The purpose is to print labels for incoming merchandise that has to be
relabeled. Each label needs a record, and each piece of merchandise
needs a label. I sincerely appreciate everyone for their time helping
me solve this quandry.

 

[Jeff Boyce]

In Access, it isn't necessary to have a table filled with what you want to
print on a label. Another approach would be to use a query as the source
for your label/report.

You mention needing a record for each label ... again, why? What will
having a record for each label allow you to do (aside from the ability to
print a label)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Here is a tricky situation that I've run into. I have a table with
about 100 records, and each record has an sku and the quantity on
hand. I want to put one record into a new table for as many pieces of
inventory I have. For example:

SKU Quantity
001 2
002 4

I need the new table to have

SKU
001
001
002
002
002
002

Any ideas?

What I've done in this situation - and what I'm sure Jeff is leading up to -
is to create a little auxiliary table named NUM with one field, N. Fill it
with values from 0 to 10000 or so (it's cheap).

Create a Query by adding your table and Num to the query grid with *no* join
line. Instead, put a criterion on N of

< [Quantity]

This will give you Quantity duplicates of each SKU; you can use this query
directly to print your labels. You can even put a textbox on the label with a
Control Source =N+1 if you want each label to have a unique sequential number.
As Jeff says, it's not necessary to put the data into a new table in order to
do this, and in fact it would just slow things down and bloat your database
unnecessarily to do so.

John W. Vinson [MVP]

 

[dmbfanmd]

In Access, it isn't necessary to have a table filled with what you want to
print on a label. Another approach would be to use a query as the source
for your label/report.

You mention needing a record for each label ... again, why? What will
having a record for each label allow you to do (aside from the ability to
print a label)?

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

Using a query would work out fine for me also, I just thought that a
table might be easier. All I need the records for is to print labels,
and I know that every label has to correspond to one record.

 

[Jeff Boyce]

See John V.'s response. He anticipated exactly where I was headed ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dmbfanmd]

See John V.'s response. He anticipated exactly where I was headed ...

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

Brilliant! You guys rock!