Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Create record based on field in query

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
James Stone
Asked:
James Stone
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
pdebaetsCommented:
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
 
Gustav BrockCIOCommented:
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
 
Dale FyeCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
James Stonewarehouse managerAuthor Commented:
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
 
Dale FyeCommented:
You could easily convert my query to a make table query and add the quantity field.
0
 
Gustav BrockCIOCommented:
You could easily convert my query to a make table query and add the quantity field.

/gustav
0
 
James Stonewarehouse managerAuthor Commented:
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
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now