Solved

Create record based on field in query

Posted on 2013-05-20
8
345 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:cubbyboy57
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 250 total points
Comment Utility
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
Comment Utility
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
Comment Utility
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

by:cubbyboy57
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Assisted Solution

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

Assisted Solution

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

/gustav
0
 

Author Closing Comment

by:cubbyboy57
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now