Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create record based on field in query

Posted on 2013-05-20
8
Medium Priority
?
353 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 1000 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 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
Dale Fye earned 600 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
Industry Leaders: 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!

 

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
Dale Fye earned 600 total points
ID: 39186270
You could easily convert my query to a make table query and add the quantity field.
0
 
LVL 52

Assisted Solution

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

Expert Comment

by:Gustav Brock
ID: 39187101
You are welcome!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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

610 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