Splitting a single row into multiple copies - 15579

I have a very weird situation that I'm hoping some of you might have
seen (and possibly resolved).

We're migrating data from one system to another and the business rules
have changed.  System 1 allowed a user to create a record for an item
with a quantity > 1.  System 2 doesn't permit a quantity > 1 for
certain items.

Is there a way to create multiple rows in the source table (or an
intermediate table) based on a list of item types which are not
allowed to have a quantity > 1?

Any help would be greatly appreciated.

Joel

0
j_racicot
8/28/2007 4:32:13 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1482 Views

Similar Articles

[PageSpeed] 23

j_racicot@hotmail.com wrote:

>I have a very weird situation that I'm hoping some of you might have
>seen (and possibly resolved).
>
>We're migrating data from one system to another and the business rules
>have changed.  System 1 allowed a user to create a record for an item
>with a quantity > 1.  System 2 doesn't permit a quantity > 1 for
>certain items.
>
>Is there a way to create multiple rows in the source table (or an
>intermediate table) based on a list of item types which are not
>allowed to have a quantity > 1?


Create a table (named Numbers) with one field (named Num)
and populate it with records with values 1,2,3, ... up to
more than the greatest quantity for any item.

Then use a query something like this as the virtual
intermediate table to export:

SELECT Item, . . .
FROM sourcetable, Numbers
WHERE Num <= Quantity

-- 
Marsh
MVP [MS Access]
0
Marshall
8/28/2007 7:32:04 PM
Reply:

Similar Artilces:

Multiple views Single Document MDI
Hi Folks, I have a situation where I need 4 views in my application. All the 4 views should co exist and shall be updated independently/ simultaneously. I searched the forum and all I got was how to create multiple views for a SDI and for MDI creating multiple document templates and make them refer to a single document . I dont know whether creating multiple document templates is a good solution as I read somewhere it is not an optimal solution. Is there any simple/optimal method by which I can go on adding a view for the document? If possible please paste a link to the sample application....

Concatenate multiple rows and columns into 1 cell
I have a spreadsheet using 3 columns and n number of rows worth of data. I am trying to create one long string of this data by concatenating in the following order: A1,B1,C1,A2,B2,C2,A3,B3,C3, etc. The only way I know how to do a large range is to individually click on each cell and that will take a very long time. Is there an easy way to concatenate a range such as (A1:C500)? Thank you. You are aware that a cell can take in limited number of characters. Any more than that number, the result will be truncated please. "mj44" <mj44@discussions.microsoft.com> wrote in ...

Multiple serial numbers when making adjustments to material receip
When doing material receipts we have the ability to add multiple serial numbers. For issues (adjustments) this must be done one by one.We need the ability to issue multiple serial numbers on the same transaction. For instance, we have to issue ten cable reels in ten different lines versus one with ten serial numbers from the list. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to ...

ripping cds in a row
when i rip a cd it ripps it then ejects it but when i put in a new cd it does not roconize it it leaves up info from last cd. i have to close wmp then reopen it to get the new cds info so i can rip it does any one no why What version of WMP and Windows? This could be caused by a disabled autorun notification. Follow the instructions at http://support.microsoft.com/kb/330135 to re-enable this. Regards -- Tim De Baets http://www.bm-productions.tk kewl wrote: > when i rip a cd it ripps it then ejects it but when i put in a new cd it does > not roconize it it leaves ...

Multiple MX records
Hello, We currently have 2 Exchange 2003 servers in 2 different geographic locations. Each server has its own storage group. We have VPN and have one MX record. Server A allows local users to send and receive email (internal and external) and it also forwards incoming mails from the Internet to server B for users on the other site. Server A serves as an gateway server for server B. Users on server B can send email out to the Internet and to local users in both offices, but they cannot receive Internet mail directly. My question is can we have 2 ingress ports for incoming mail (allowing ser...

Excel
I have an Excel Spreadsheet with 12 worksheets (one for each month). What is the easiest way to merge them together into one worksheet? Hi Wendy Try this http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "wendy" <wendy@discussions.microsoft.com> wrote in message news:D689DD30-A266-4BA9-A70E-CD5761C987FB@microsoft.com... >I have an Excel Spreadsheet with 12 worksheets (one for each month). What is > the easiest way to merge them together into one worksheet? ...

Repeat Rows but not on every page!
Is it possible to set the repeat for just a certain number of pages, not all? Example: I want to repeat $2:$2 on page 1 through 6 but I don't want the repeat on pages 7 through 10. Is there a way to define this? Setup a custom print macro that prints pages 1-6 with the repeated row, and pages 7 - 10 w/o repeat? (You can record this macro yourself) -- Best Regards, Luke M "AndreaW" <AndreaW@discussions.microsoft.com> wrote in message news:6BD8310D-2369-4C64-A4CC-04E16F50CFDD@microsoft.com... > Is it possible to set the repeat for just a certain numbe...

creating a document from an Excel row
i have an excel doc with something like... title1 title2 title3 blah1 blah2 blah3 next1 next2 next3 1) i want to be able to click on the grey left-hand button for each row, and generate a 1-page report for that row. eg, if i click on the left button next to "blah", i want an excel page like title1: blah1 title2: blah2 title3: blah3 to be printed out. clicking on the button by "next" would give me title1: next1 title2: next2 title3: next3 2) And even better, going a bit further...this same page generated in a directory which has the same name a...

How to get last entered value in a ROW?
Hi, I'm wondering if ya'll excel "wizards" can help me out. I want to be able to get the "last entered" value for a particular row and subtract from it a fixed field (i.e. L15-F15 where L15 is the last entered value (or G15, H15, etc.; increments monthly) and F15 is a field that does not change in the equation.) Thanks, Bill Try this to get the last value of row 15 ActiveSheet.Cells(15, Columns.Count).End(xlToLeft).Value -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Excelwiz wanabee" <nospam@igetenoughalready.com...

Swapping columns and rows in table in Excel Sheet
I have a very large table created in an Excel sheet. I need to apply a statistical formula, requiring that the coulms and rows must be swapped. I know that it can be done easily, but I have forgotten how. Can anyone help? Klavs Dahl Christensen Have a look at Paste Special / transpose best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Excel Sheet" <Excel Sheet@discussions.microsoft.com> wrote in message news:5C5E1D1A-873D-4F9A-A56D-0C4F30257035@microsoft.com... >I have a very large table created in an Excel sheet....

Multiple Outlook.exe processes
Hi! Whenever I close Outlook 2003 it leaves Outlook.exe running in the background. It will do this each time I open/close the program, creating multiple instances, the only workaround is to end the processes manually in Task Manager. (extremely tedious!) I've tried running scanpst.exe to check for folder corruption - it comes back clean - and I've tried to repair my Office installation, but the multiple process irritation still remains. Does anyone know how to fix this? Thanks in advance. Some common reasons why Outlook will not shut down completely when you click either the...

Auto sum 2 adjacent row data into 3rd row?
I have a list of people's names in column C and cost data for each person in column M and N. Is there was to provide a subtotal of all cost data for each person in Column O? i.e.: row 1 Smith, John 100.00 440.00 row 2 Smith, John 300.00 row 3 Smith, John 40.00 row 4 Bird, Joe 50.00 500.00 row 5 Bird, Joe 450.00 Thank you in advance! :) in C2, =IF(C1=C2,"",sumif(C:C,C2,M:M)+sumif(C:C,C2,N:N)) "Tacrier" wrote: > I have a list of people's names in column C and cost data fo...

Multiple Email Accounts
I have created 4 email accounts and emails from all accounts are being deposited into the same inbox. Outlook express allowed me to manage these through identities so that each email account had it's own inbox, drafts, sent, etc. Is this possible with Windows Mail? -- Thanks, Ron WinMail doesn't have that option. Take a look ate this tool. WMIDs http://www.oehelp.com/WMIDs/Default.aspx Otherwise, create an "Inbox" for each address and use message rules to divert the messages to their respective folders. -- Bruce Hagen MS-MVP [Mai...

emailing multiple forms w/one command button
I'd like to print 2 different forms from data that is on a form w/subforms. When I click the command button I need to outlook to open and have the 2 reports as attachments. I've use a macro to send each one separtely but this is 2 steps with 2 different emails. thanks -- rjpohl "rjpohl" <rjpohl@discussions.microsoft.com> wrote in message news:308E9D23-48CC-454D-A5BA-E0E5FBFBD7F6@microsoft.com... > I'd like to print 2 different forms from data that is on a form > w/subforms. > When I click the command button I need to outlook to open ...

indirect reference to a column or row
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel This has to be an old question, and probably everyone in the world knows the answer except for me.... <br><br>I want to make and indirect reference to a row or column in a formula. Such as: <br> &nbsp;A(function that gives some row number) <br><br>I simply cannot figure how to do this- can I do this? If so, how? <br><br>Thanks, <br> Snale <br> Dale Brown In article <59bb2368.-1@webcrossing.JaKIaxP2ac0>, Snale@officeformac.com wrote: > Versio...

Functional Equivalent Of xlSheetVeryHidden For Rows?
I'm creating a rather intricate and interrelated .XLS via VBA code and the .XLS itself has quite a bit of VBA code in it. User has decided that they do not want to see certain rows on the final product. Problem with just doing a Worksheet(rownum).Delete is that a deleted row may be referenced in the VBA code. I could change the VBA code, but now we're talking about a major rewrite and a lot of man hours. Just hiding it would simplify the change greatly. The zinger is that the user does not want anybody to be able to un-hide the hidden rows. My first fallback pos...

Conditionally Hide Rows
I have a spreadsheet where I would like to be able to hide selected rows if a specific cell within that row meets a specific condition. The conditional cell is a cell reference to another sheet within the workbook. Does anyone know if this can be done and if so how? Thanks in advance for your advice. Crownman Maybe you can select the column and apply Data|filter|autofilter to hide/show the rows you want. Debra Dalgleish has some notes here: http://www.contextures.com/xlautofilter01.html Crownman wrote: > > I have a spreadsheet where I would like to be able to hide selected > r...

How to start a split form when I click on a menu page
I'm using the 'page' menu of 2007. I want to automatically start a particular split form when I click on a particular menu page. I have tried to place a subform on the page, but I could not get the form to open as a 'split' form. I would prefer doing this with a Macro. Bob ...

Data validation does not seem to work with copy/paste
I have set up some data validation for each cell within a range. When manually enter invalid data in one of those cells, the data validatio works and an error message pops up. When I copy from one cell wit data to another cell, where the data validation results in false, i allows me to paste the value with no error message. Is there any wa to fix this -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.com/showthread.php?threadid=46802 ...

Update multiple worksheets
:confused: What is the simplest method to update several worksheets a once. My report involves several steps where I create multiple pivo tables and from there I update 2 different workbooks from the pivo tables I've created. I use one set of data to create charts and fro the other data I create a report in Excel that has 2 worksheets that update manually. I currently link the worksheets but I'm concerne that I might accidentally select the wrong cell. I'm sure there is simplier way but I don't where to start -- Lizz45i -----------------------------------------------...

Copy paste a table column into another table
Hello, Using Word 2007. I have two identical documents, one in English and the other in Spanish. I have a table that contain 4 columns and rows. I want to copy the last three columns from my ENG to my SPA. When I try to do this, I paste over the 3 selected columns in my SPA and all hell breaks loose. There is no paste special. A suggestion would be appreciated. Thanks, Luke What happens, exactly? -- Stefan Blom Microsoft Word MVP "lukepw" wrote: > Hello, > > Using Word 2007. > > I have two identical documents, one in Engli...

Different coloured rows
Hi all, Does anyone know how to automatically make one row white background, next row light grey (to make reading across the row easier) and alternate all the way down the page? Thanks for any help. Matt. Hi have a look at http://www.cpearson.com/excel/banding.htm for using conditional format to achieve this -- Regards Frank Kabel Frankfurt, Germany The Stoat wrote: > Hi all, > > Does anyone know how to automatically make one row white background, > next row light grey (to make reading across the row easier) and > alternate all the way down the page? > > Thanks ...

Preview of multiples message
Hi everyone, can i get a preview of multiple messages at the same times , so that i can take printouts of mails received on a particular date without pagebreak? thaking in advance Dinoj No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, cddinoj asked: | Hi everyone, | can i get a preview of multiple messages at the same times , so that | i can take printouts of mails received on a particu...

how to copy my contacts to my cd-rw drive
I selected all my contacts, then click copy. then my computer fab, then my cd-rw drive then paste. However it didn't work. Any help would be appreciated.hanks "Garth" <mpringle1260@rogers.com> wrote in message news:xrOdnQ8VJNqtOEDcRVn-oA@rogers.com... >I selected all my contacts, then click copy. then my computer fab, then >my cd-rw drive then paste. However it didn't work. > Any help would be appreciated.hanks > Computer "fab"? What is "fab"? Fab, to mean, is short for fabrication, fabricate[d], and only for fabulous when wat...

Copy multiple file names, as text (table of contents) into a word
I want to create a Table of Contents listing for a training document. "Selecting ALL" in a directory will highlight all the file names that I want to paste in a word table or excell document. I just need the text of the document names and extensions .... How can I just "paste" the text without imbedding the file? You would need VBA code to do this. If you aren't familiar with it, it will be quicker to do it by hand. Another option is to go to a DOS prompt, then issue the DIR command, redirecting the output to a text file, like this DIR *.* >MyFileList.txt ...