Expanding the table in MS Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:
I have got a problem regarding expanding the table. I have a table in excel.
It looks like this:

Pdt name Quantity Serial No. KIT No.
Pre-Paid 1300 0172702100 0280132101
Pre-Paid 1000 0172700700 10280127501
Pre-Paid 700 0172704700 0280163701

My problem is that I have a serial no. and KIT no. of a product which has a
quantity of 1300. In excel I have to insert rows and type each of the serial
no. How can I make Access to do that for me? I want MS Access to expand the
table in below format (so that I do not have to insert each row and type the
serial no. of the product):

Pdt name Quantity Serial No. KIT No.
Pre-Paid 1 0172702100 0280132101
Pre-Paid 1 0172702101 0280132102
Pre-Paid 1 0172702102 0280132103
Pre-Paid 1 0172702103 0280132104
-- -------- -------- ----------- -------
this way upto 1300.

Thanking you in advance
 
You need a counter table, with a record for each value from 0 to the highest
quantity you could ever need.

1. Create a table with one field named CountID, of type Number.
Mark the field as primary key.
Save the table with the ntame tblCount.

2. Use the function at the end of this web page to populate the function for
you:
http://allenbrowne.com/ser-39.html
Change the Const line to the number of records you want (e.g. 10000.)

3. Create a query that uses your existing table and tblCount as well.
There must be no line joining these 2 tables in the upper pane of query
design.

4. Drag tblCount.CountID into the grid.
In the Criteria row under this field enter:
< [Table1].[Serial No.]
substituting your table name for Table1.
Uncheck the Show box so this field does not output.

5. Type this into a fresh colum in the Field row:
SerialNo: [Serial No.] + tblCount.CountID
(This assumes that [Serial No.] Is a Number field, so you can add to it.)
 
In Excel type the first two rows of serial numbers. Select both rows then
drag the bottom right corner down the column and Excel will automatically
increment the rows for you. You can do the Kit No. the same.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Back
Top