SQL View duplicating records

I've created a view on inventory transactions linking IV30300 and
IV30400, wanting to pull the lot number information into the view for
each inventory trx.  I have a transaction that was an INVADJ, with one
item on it, recorded at 3 different quantities, 3 different costs, and
3 different lot numbers.  In my results pane, this transaction is
listed 9 times, once for each combination of qty, cost, and lot
number.

What causes this to happen and how do I change my query to pull only
one line per combination of qty, cost and lot number in this
transaction?

Thanks for the help.

Frank Hamelly
MCP-GP, MCT
East Coast Dynamics
www.eastcoast-dynamics.com
0
fhamelly1 (1404)
5/2/2008 6:26:55 PM
greatplains 29623 articles. 6 followers. Follow

1 Replies
392 Views

Similar Articles

[PageSpeed] 0

Frank,

I could see how this is happening if you are not including the Line Sequence 
Number (LNSEQNBR) field in your join condition and if you are just using the 
document number (DOCNUMBR) and item number (ITEMNMBR) to join the tables.

Is it possible for you to post the query for the view?
-- 
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com


"Frank Hamelly, MCP-GP" wrote:

> I've created a view on inventory transactions linking IV30300 and
> IV30400, wanting to pull the lot number information into the view for
> each inventory trx.  I have a transaction that was an INVADJ, with one
> item on it, recorded at 3 different quantities, 3 different costs, and
> 3 different lot numbers.  In my results pane, this transaction is
> listed 9 times, once for each combination of qty, cost, and lot
> number.
> 
> What causes this to happen and how do I change my query to pull only
> one line per combination of qty, cost and lot number in this
> transaction?
> 
> Thanks for the help.
> 
> Frank Hamelly
> MCP-GP, MCT
> East Coast Dynamics
> www.eastcoast-dynamics.com
> 
0
MarianoGomez (3440)
5/3/2008 6:56:00 PM
Reply:

Similar Artilces:

It takes time to load records in MS CRM Client
HI All, I am facing a serious problem, whenever i access CRM through Client, it takes time to load and after getting records in it, it is not shown properly. Why so??? please help me out. Client is always connected to server, Is there any way to check if it is connected or not. Thanks in Advance. Nitin Verma Nitin, I had the same issue with a client of mine and after working through all of the standard MS suggestions we found that by removing the clients local OST file and recreating it from the exchange server the issue was resolved. MS informed us of 2 items. 1) Having more then 1,000 e...

Invalid Object Name
I've searched and searched and can not find out how what's wrong with this query or table. Perhaps this should be posted to a SQL forum but I'm not sure. Someone has a database that worked a few days ago and he is now getting an error message: 'Could not execute query; could not find linked table. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Partxxx'. (#208). I know that the employee's security is correct, and when checking the ODBC a 'test successful' message is received. I'm at a loss. I finally did an Access database ...

long formula obscures cell view
I have a workbook with very long formulas (basically external links to several other workbooks). As a result, when I point to a cell containing such a formula, the multi-line formula description drops down and obscures the contents of the cell. I presume that there is a way to prevent that from happening, but I couldn't find it in Tools>Options You can hide the Formula Bar under View > Formula Bar, or do it under Tools > Options > View. HTH Jason Atlanta, GA >-----Original Message----- >I have a workbook with very long formulas (basically external links to >s...

Presenter View and Hyperlinks
I have hyperlinks to program files in a high school math class but they will not work in presenter view, where I can see my notes etc. I have tried using the second monitor as an extension of the computer monitor so that the editing view is on my computer and the slide is viewed on the second monitor. The hyperlink can be executed on the second monitor but the program opens on the computer monitor and isn't visible to the class. The only way I can find to view the program is when both are showing the slide. Is there a way which allows me to see my notes and the audience to see ...

MFC List View Control Dragging Problem
Hi All, Our application is using two different list controls to view data. 1) ResultList 2) BatchBuilder The ResultList control contains different item columns and the data from ResultList can be dragged to the BatchBuilder control, using the MFC Drag and Drop mechanism. Everything is working fine with dragging, with the exception in the first column in the ResultList control. When i try to drag the items from the ResultList columns from Right to Left or straight down the bottom, the dragging is being done properly, but when i try to drag the items from Right to Left, instead of getting d...

Settings for viewing PPT presentation in Word Doc
One of my users has been receiving MS Word docs with embedded PPT presentations and viewing them just fine. She recently felt the need to change "something" in the latest Word doc and now she only sees the first slide - double clicking does not start the slideshow. Others in her office can access the document and the presentation works fine. It appears that she inadvertantly changed some setting in her Word options that govern how an embedded PPT file works. She is using Office 2007 and I can't find anything. Her project is going to continue at least another 10 mo...

view web pages in outlook
I have outlook 2002 SP1 and trying to view web pages with in the Outlook. For some reason I can view all of the pages but one. It works fine if viewed in Explorer but wont work if assigned to a Public folder as home page. I have created many public folders and and most of them use webpage as home page on our intranet server. This is internet site and it just displays unable to display the folder. Permissions are ok. Any ideas? It works fine on other machines. Exchange is 5.5 SP4 on win2k server. George ...

Unable to 'view source' on Outlook e-mail -
Hello, running Office XP on XP Pro - Used to be able to select an e-mail in Outlook and right-click 'View' -- > 'Source' and notpad would open with the HTML of the e- mail message - I used it to check links, etc. Stopped working a few weeks ago - Menu option is still there, but nothing happens - Any help appreciated - Bob ...

Customizing an Associated View Filter On
Hello, Does anyone know how to customize the Filter On default in an associated view? Specifically, if you go to contacts, then select cases, it defaults to active cases. We would like it to default to all cases. However, we don't want it to default to all cases when you are in the case view. Jerry I have not seen any way to modify that setting. -- Matt Parks MVP - Microsoft CRM "Jerry Weinstock" <'ibizinitiatives' at you know what to do if you are a human being 'gmail.com'> wrote in message news:eePXbk7RGHA.4608@tk2msftngp13.phx.gbl... He...

How do i activate the Macro recorder toolbar
While recording a macro, I accidently closed the macro recorder toolbar .. Now whenever I record a macro, the recording toolbar does not appear. there fore I have to use the menu option to stop recording the macro. Also I cannot use relative and absolute reference. Can you please let me know how I can activate my macro recorder toolbar to appear the moment start recording my macro. Thanks --- Message posted from http://www.ExcelForum.com/ Tools>Customize>Toolbars. Select "Stop Recording" toolbar and "Reset". Put a check in the checkbox. Stop Recording Toolb...

Microsoft Access and SQL Server
My company is converting from Oracle to SQL Server 2005. My front end is Microsoft Access 2003. In the conversion, they changed our customer and transaction numbers from decimal fields to BigInt. When I do a make table query in Access, those fields change to text as it is my understanding Access does not support BigInt. Am I correct? Many of my applications use a combination of make table and append queries to get the data I need (most of my stuff my run standalone with no network connection). Linking the tables through queries, reports, etc., will not work with inconsistent data ...

CRM with MSDE, not SQL Server 2000
I seem to be reading some conflicting things. I'm trying to install CRM on windows server 2003. Do I need to use sql server 2000 or can I setup msde to work with it as the CRM database? For the CRM Server, only SQL Server 2000 is supported. Frank Lee Workopia, Inc. www.workopia.com "Dr. Silk" wrote: > I seem to be reading some conflicting things. I'm trying to install > CRM on windows server 2003. Do I need to use sql server 2000 or can I > setup msde to work with it as the CRM database? > > And the best way to get that for the smaller offices is v...

SQL Required on SBS2008 STD
I have SBS2008 STD on a 64Bit platform and need to run a management program that requires SQL server. Can I purchase SQL server and possibly use this on the SBS2008 STD platform? Does Microsoft have migration / upgrade software to SBS 2008 PREMIUM ? I have read a blog, that, technically it is possible to migrate from STD to PREMIUM but there are problems with the EULA. The core component that is required is Windows Server 2008. What are the options available to me? Thanks for your advice in advance. -- Jack How many users will need access to SQL? Would SQL 2008 Express (...

this record can not be deleted
cant delete test records that ive created,error message its associated with another record. attempted to delete previous associated record, no luck. can i clean out old records? looking for options i have quotes, orders and invoices that are linked together.(test runs) i can't delete some of them via the user interface. I assume this is for history integrity. I would reinstall, except for the pain it took getting my contacts and accounts in. I have the same problem. I added some custom fields into the contact table and now I can't delete it. It tells me the data is part ...

make a duplicate table in access and save changes
I have a table that gives the rates of Dearness Allowance.These rates are given for different periods in percentages. ex: 01-01-2009 to 30-06-2009 37% 01-07-2009 to 30-11-2009 45% etc.. This is the default table and is applicable to almost 95% to 97% of employees. I want to make a duplicate of this table and display it in an unbound subform ( which is a continuous form). If the user makes any changes in it, it is then clear that Default rates are not applicable to him. Then, i want to store the data in a new table tblDeviations where i will store the EmployeeId also ...

CRM 3.0
Hi All, One of our users is experiencing a missing toolbar in their CRM Outlook Desktop client for Microsoft CRM 3.0. The main toolbar appears, but the "Track in CRM" toolbar does not appear in the Opened Email View. I have searched the web and the closest thing I can find is this thread posted two years ago to this newsgroup: http://groups.google.com/group/microsoft.public.crm/browse_thread/thread/2a2a23357f48a2fe Unfortunately, the solution did not work for me & apparently for others. I have reinstalled, removed and replaced the COM addins, applied Rollups 1,2 & 3,...

Is there a way to know when a Contact Record was created?
Is there a way or place to see when a Contact Record was created? I used to us ACT and part of the Properties or General Info automatically recorded the Creation Date for that Contact. It was very useful info to purge my Data Base of Old Inquiries that never developed. "WIM4246" <WIM4246@discussions.microsoft.com> wrote in message news:93299CA1-AD1A-421D-8371-97DF1AE6BB03@microsoft.com... > Is there a way or place to see when a Contact Record was created? > > I used to us ACT and part of the Properties or General Info automatically > recorded th...

SQL
Could anyone help with this:- Can this be achieved in a SQL statement other than making physical tables (other than the FromTo table) ? I have one table called auaudit and need one query to select records that have a field called aufrom and a 2nd query to select all records that have a field called auTo A 3rd query would use the results from the above queries to test if field RD in both are not equal. If not equal would put the HD field from query 1 and the HD field from query2 and the data field from query2 into a new table called FromTo And a 4th query would do the same as the 3rd b...

Viewing other user's e-mail
I am the administrator of our network running Windows Server 2003 and Exchange Server 2003. I am trying to figure out how to view other people's e-mail folders from within the System Manager. I went through Method 1 on KB article 821897 and I now have the Security tab available. I then went into System Manager, went to the properties of the First Organization and removed the Deny setting for "Receive As" ans "Send As". I just don't get how to actually open another user's mailbox. What am I missing? ...

Workbook duplication
How do I duplicate a completed workbook so that I can use all formulas etc. with a differenct set of data? I have copied and pasted but I get formulas that link back to original workbook. Any help will be appreciated. Open workbook, file save as different name, Okay "Becamp11000" <becamp11000@wmconnect.com> wrote in message news:20040709161516.05811.00001451@mb-m05.wmconnect.com... : : How do I duplicate a completed workbook so that I can use all formulas etc. : with a differenct set of data? : : I have copied and pasted but I get formulas that link back to original : wor...

Cumulative totals from individual records
I have labor data for employees given by hours worked on a given day. Eg. Tom, 5-hrs, 5-Jan-2001 What I need is cumulative hours worked by month: E.g. Tom, 100-hours-to-date, Jan-2001 How can I use Access to generate cumulative-hours-to-date by month from hours-worked-on-given-days? Note: It’s something I could get from Excel Pivot table, but I have well over 60,000 records (in the millions). -- Richard Use a totals query to get the totals for a period (e.g. a month.) Then use a subquery to get the progressive total. The Year-to-date example should illustrate how: ...

SQL update for Payables manual payment distributions
Hi All Can someone please provide me with a SQL script to update the distributions of a payables manual payment. The correct check amount is already available for each transaction in the manual payments screen. I now want to use the same value (check amount) for each payment and update the related distribution line items. Thanks in advance, I'm being dense here, but I don't understand what you are trying to do. Do you have the manual payments already saved to a batch and you want to set the distribution accounts without having to open the window? If yes, the dis...

Duplicate Range names
Suppose I have Sheet1 containing a named range, "Something". If I copy to a new sheet (Edit | Move or Copy Sheet), the new sheet also contains "Something", and I can reference either one (Sheet1.Something or Sheet2.Something). So far so good. Now, I need to programmatically create multiple sheets, each of which contains "Something". How would I define Something on each sheet? TIA, Jim With Worksheets("sheetname") .Range("A1:A10").Name = "'" & .Name & "'!Something" End With -- HTH Bob (the...

tabbed view like excel sheet
how create views like excel sheet in is botton like tabed view This was in MSJ few years ago: http://www.microsoft.com/msj/0599/c/c0599.aspx --------- Ajay Kalra ajaykalra@yahoo.com > how create views like excel sheet in is botton like tabed view http://www.codeproject.com/tabctrl/foldertabcontrol.asp Lynn ...

Default Accounts View for PO Line
When entering a purchase order line and selecting an account number, the default is set to "Vendor Accounts". Is there any way this can be set to defalt to "All Accounts"? I have tried VBA, but it seems to default back to "Vendor Accounts". Regards Konrad ...