Solved

Create record based on field in query

Posted on 2013-05-20
8
350 Views
Last Modified: 2013-05-22
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
Comment
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
8 Comments
 
LVL 12

Accepted Solution

by:
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.addnew
    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 
        rsto.addnew
        '* 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

Open in new window


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

by:Gustav Brock
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

by:Dale Fye (Access MVP)
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:James Stone
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

by:Dale Fye (Access MVP)
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

by:Gustav Brock
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

by:James Stone
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

by:Gustav Brock
ID: 39187101
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

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.

Join & Ask a Question