Solved

# Create record based on field in query

Posted on 2013-05-20
350 Views
Hopefully I can explain what I need....

I have a table that has a serial number (unique number) and "qty per carton" field.

say the serial number is "1PB0J841E "  with a qty per carton of 4 -- I would like to see 4 results to my "query"
1PB0J841E  4 times with a field that has 1PB0J841E001, 1PB0J841E002, 1PB0J841E003,1PB0J841E004. (still unique numbers)

The qtys per carton vary from 1 to 200, most are 1

Ideally I would like to end up with a table that has the original serial number and the new serial number(s).

I just don't know  VBA or Access Basic to create the new table (From what I read I don't think I can do this in a query)

My query to get the original results (serial number and qty per carton) is:
SELECT dbo_OP_DA_CARTONS.Carton_Serial_No, dbo_OP_DA_CARTONS.Qty_Per_Carton
FROM dbo_OP_DA_CARTONS
WHERE (((dbo_OP_DA_CARTONS.Company_Code)="D") AND ((dbo_OP_DA_CARTONS.Order_No)=141788));

Any help would be appreciated.
0
Question by:James Stone
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2
• 2
• +1

LVL 12

Accepted Solution

pdebaets earned 250 total points
ID: 39183316
Should the new record (ex.: 1PB0J841E001) have a quantity of 1?

And you want these new records in the same table as the original record (ex.: 1PB0J841E ) that has a quantity of 4?

I can see potential problems with that, especially if you plan to sum quantities later on, but here goes:

``````dim dbs as dao.database
dim rsti as dao.recordset
dim rsto as dao.recordset
dim strSQL as string
dim strOutputTableName as string
dim i as integer
set dbs = currentdb()
strOutputTableName = "tblMyOutputTableName"    '<<< Change this, if needed
'* Delete table if it exists
on error resume next
docmd.deleteobject actable, strOutputTableName
err.clear
on error goto 0
'* Copy the table structure
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "dbo_OP_DA_CARTONS", strOutputTableName , True
set rsti = dbs.openrecordset("Select Carton_Serial_No, Qty_Per_Carton from dbo_OP_DA_CARTONS WHERE (((dbo_OP_DA_CARTONS.Company_Code)='D') AND ((dbo_OP_DA_CARTONS.Order_No)=141788))",dbopensnapshot)
set rsto = dbs.openrecordset("Select * from [" & strOutputTableName  & "];", dbopendynaset)

do while not rsti.eof
'* Write out the original record
rsto!Carton_Serial_No = rsti!Carton_Serial_No
rsto!Qty_Per_Carton  = rsti!Qty_Per_Carton
rsto.update
'* Write out one record for each item
for i = 1 to rsti!Qty_Per_Carton
'* Create new serial no using padding and number
rsto!Carton_Serial_No = rsti!Carton_Serial_No & string(3 - Len(i),"0") & i
rsto!Qty_Per_Carton  = 1
rsto.update
next i
rsti.movenext
loop
set rsti = nothing
set rsto = nothing
set dbs = nothing
``````

Make sure to change the value of "strOutputTableName " if needed. To run this code, create a new subroutine in a standard module, then copy and paste the code into it. You can run the code by pressing F5.
0

LVL 50

Assisted Solution

Gustav Brock earned 100 total points
ID: 39183604
You can also create a small table with a Number field and 200 records.
Then you can expand your query like this:

SELECT
dbo_OP_DA_CARTONS.Carton_Serial_No,
dbo_OP_DA_CARTONS.Qty_Per_Carton,
dbo_OP_DA_CARTONS.Carton_Serial_No & Format(dbo_Number.Number,"000") AS Carton_Serial_No_New
FROM
dbo_OP_DA_CARTONS,
dbo_Number.Number
WHERE
dbo_OP_DA_CARTONS.Company_Code = "D"
AND
dbo_OP_DA_CARTONS.Order_No = 141788
AND
dbo_Number.Number <= dbo_OP_DA_CARTONS.Qty_Per_Carton
ORDER BY
dbo_OP_DA_CARTONS.Carton_Serial_No,
dbo_Number.Number;

/gustav
0

LVL 48

Assisted Solution

Dale Fye (Access MVP) earned 150 total points
ID: 39184025
I use a technique similar to that recommended by Gustav.  But instead of adding 200 records to the table, I only add 10.  The table (tbl_Numbers) contains a single field (lngNumber), which is a long integer, and 10 records (the numbers 0 - 9).  From this table you can create a query (qry_Numbers) that gives you as many numbers as you want, something like:

SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

This will give you the numbers 0 to 999

Then, for your query, you would use:

SELECT  [serial number] & Format([lngNumber], "000") as NewSerial
FROM yourTable, qry_Numbers
WHERE lngNumber > 0
AND lngNumber <= [qty per carton]

Because the query generates the numbers 0 to 999, you need to exclude the 0, but then include any number <= to your [qty per carton] value.
0

Author Comment

ID: 39186258
I think pdebaets has what I need - No I don't want to have the new numbers in the same table as the orginal and yes all the new numbers would have a qty of 1.

Hopefully I can get this to work tonight (I am not working on it during the day).

Thanks for the replies. Will let you know soon.
0

LVL 48

Assisted Solution

Dale Fye (Access MVP) earned 150 total points
ID: 39186270
You could easily convert my query to a make table query and add the quantity field.
0

LVL 50

Assisted Solution

Gustav Brock earned 100 total points
ID: 39186519
You could easily convert my query to a make table query and add the quantity field.

/gustav
0

Author Closing Comment

ID: 39187094
All 3 solutions do what I needed, I originally thought I just wanted a query, but after seeing the code pdebaets suggested, I decided to rethink what I was recording.

I know I will use the query "tricks" in the future, as I do serialize numbers and that is a quick way I had not seen before.

Thanks for all the help on this!

james
0

LVL 50

Expert Comment

ID: 39187101
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month6 days, 18 hours left to enroll

#### 724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.