SQL query for items on hand and sold

Help me with a query for eash item qty on hand and sold in one statement?
I know on hand comes from item.quantity and sold qty comes from 
transactionentry.quantity but I don't know how to show them together in a 
report.
0
sammm (54)
9/16/2009 3:46:06 AM
pos 14173 articles. 0 followers. Follow

1 Replies
310 Views

Similar Articles

[PageSpeed] 28

This is a multi-part message in MIME format.

------=_NextPart_000_009A_01CA3853.0089EAC0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Sammm,

Do you realize the the OnHand is an available column on the Detailed =
Sales Report?  After running the report, right click on the body of the =
report | Show/Hide Columns | and select the OnHand column to display it.

--=20

Jeff=20
Check Point Software

Search this newsgroup -- http://tinyurl.com/2lmk4w  (Stolen from DRS)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

You must be using Outlook Express/Windows Mail or some other type of =
newsgroup reader to
see and download the file attachment(s).  If you are not using a reader, =
follow
the link below to setup Outlook Express.  Click on "Open with =
newsreader"
under the MS Retail Management System on the right.

http://tinyurl.com/75bgz
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D


  "sammm" <sammm@discussions.microsoft.com> wrote in message =
news:329DF105-D34D-44A7-A273-FA5DD6BB2D2B@microsoft.com...
  Help me with a query for eash item qty on hand and sold in one =
statement?
  I know on hand comes from item.quantity and sold qty comes from=20
  transactionentry.quantity but I don't know how to show them together =
in a=20
  report.
------=_NextPart_000_009A_01CA3853.0089EAC0
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3Dtext/html;charset=3Dutf-8 http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18813"></HEAD>
<BODY style=3D"PADDING-LEFT: 10px; PADDING-RIGHT: 10px; PADDING-TOP: =
15px"=20
id=3DMailContainerBody leftMargin=3D0 topMargin=3D0 =
CanvasTabStop=3D"true"=20
name=3D"Compose message area">
<DIV><FONT color=3D#008000>Sammm,</FONT></DIV>
<DIV><FONT color=3D#008000></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#008000>Do you realize the the OnHand is an available =
column on=20
the Detailed Sales Report?&nbsp; After running the report, right click =
on the=20
body of the report | Show/Hide Columns | and select the OnHand column to =
display=20
it.</FONT></DIV>
<DIV><BR>-- <BR><BR>Jeff <BR>Check Point Software</DIV>
<DIV>&nbsp;</DIV>
<DIV>Search this newsgroup -- <A=20
href=3D"http://tinyurl.com/2lmk4w">http://tinyurl.com/2lmk4w</A>&nbsp; =
(Stolen=20
from =
DRS)<BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D</DIV>
<DIV>&nbsp;</DIV>
<DIV>You must be using Outlook Express/Windows Mail or some other type =
of=20
newsgroup reader to<BR>see and download the file attachment(s).&nbsp; If =
you are=20
not using a reader, follow<BR>the link below to setup Outlook =
Express.&nbsp;=20
Click on "Open with newsreader"<BR>under the MS Retail Management System =
on the=20
right.</DIV>
<DIV>&nbsp;</DIV>
<DIV><A title=3D"http://tinyurl.com/75bgz&#10;CTRL + Click to follow =
link"=20
href=3D"http://tinyurl.com/75bgz">http://tinyurl.com/75bgz</A><BR>=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #008000 2px solid; PADDING-LEFT: 5px; =
PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
  <DIV>"sammm" &lt;<A=20
  =
href=3D"mailto:sammm@discussions.microsoft.com">sammm@discussions.microso=
ft.com</A>&gt;=20
  wrote in message <A=20
  =
href=3D"news:329DF105-D34D-44A7-A273-FA5DD6BB2D2B@microsoft.com">news:329=
DF105-D34D-44A7-A273-FA5DD6BB2D2B@microsoft.com</A>...</DIV>Help=20
  me with a query for eash item qty on hand and sold in one =
statement?<BR>I know=20
  on hand comes from item.quantity and sold qty comes from=20
  <BR>transactionentry.quantity but I don't know how to show them =
together in a=20
  <BR>report.</BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_009A_01CA3853.0089EAC0--

0
cptsoft (1248)
9/18/2009 6:27:27 PM
Reply:

Similar Artilces:

adding a new item to existing matrix in rms 2.0
how come when you add an item to an existhing matrix non of the information is being forward from the main matrix (ex. supplier, price, cost, reorder #, barcode format, etc.) even the size, color, and 3rd dimention that you entered to the new item is not being fowarded. what is going on with 2.0 thank jack I think the Matrix is still is a little buggy.. Get all hotfixed up and see if the helps. There have been 3 for 2.0 hotfixes already. "jack" <jack@discussions.microsoft.com> wrote in message news:45CB84DE-B9B1-4DAC-9043-D8120CB44472@microsoft.com... > how come...

SQL Server
I'm currently using MS-Access to write reports using a SQL Server db as the data source in a client/server arrangement. The database is sufficiently large (2gb) that data retrieval speed is an issue. The current setup is linked tables to the SQL Server db via ODBC, and then the usual queries. 1. Can views and/or stored procedures on the SQL Server side along with paramaterized queries provide a significant performance boost? 2. I've read that views and stored procedures can be created w/i Access. Is this the same as creating them in SQL Server? 3. Are there other things I sho...

SBS2008 and SQL 2005
Hello All, So here is my question, we have the SBS 2008 Premium, and all the installation media are sent to us. And that includes the SQL 2005 and 2008 installation disks. But after I installed the SBS 2008 on my first server which we also would like to turn it to SQL, I looked into the Program And Features and found SQL 2005 already installed. But I haven't really insert any SQL installation disk. So is that a valid copy of SQL or just some thing like SQL Express? Do I need to uninstall that and install from my SQL install disk or I can start to use that? Thanks! Ben T...

item look up #3
a customer is having an issue. he scans an item in the pos screen and all details show up. but when he tries to look for it later he cant find it. hes tried it in manager too. he would usse name, or barcode, or anything and still nothing. do we know what the problem is? Thanks If the item is set as inactive (in the item properties) it will not show up in the Find screen, but will scan in at the POS. You can turn off the find option, but I think it is easier to use one of the SOM reports to find the item, such as the price report. The item will show up in any report that includes it as...

Using Matrix Items
Someone...want to tell me how or if I can default which item is first (selected) when a matrix item is scanned? Is there a way to set this up? -- Thank You Vince :) What are the dimensions you use? Rob "Vince" <Nagarra@yahoo.com.(donotspam)> wrote in message news:E001F1F0-3002-4E1B-8E75-8E62FC6EC01C@microsoft.com... > Someone...want to tell me how or if I can default which item is first > (selected) when a matrix item is scanned? Is there a way to set this up? > -- > Thank You > Vince :) Using Lot Matrix, what I have is a six pack of coke or a sin...

SBS 2008 Fax Sent Items Not showing all faxes sent
Hello, I need to allow sent faxes to be seen by all users. I have Windows XP and Windows 7 64bit client machines. Currently they can only see their sent faxes. In SBS 2003 they were able to see all sent faxes. Any suggestions? Could you create an Outlook rule based on the fax sent acknowledgement that copied the fax to a public folder? "RodSoh" <RodSoh@discussions.microsoft.com> wrote in message news:A4541A0E-18C2-4A83-A13D-8E7D045C29E8@microsoft.com... > Hello, > > I need to allow sent faxes to be seen by all users. I have Windows XP...

I don't have 'as items arrive' option
I have the 5 minute synch option, which works great. But I do not have the 'as items arrive' option. Yes, I am running a MSFP version, I checked the build. Man I am so close to gettting this to work. Please help. Thanks, On Mon, 2 Apr 2007 12:42:00 -0700, MidMOman <MidMOman@discussions.microsoft.com> wrote: >I have the 5 minute synch option, which works great. >But I do not have the 'as items arrive' option. >Yes, I am running a MSFP version, I checked the build. >Man I am so close to gettting this to work. > >Please help. Thanks, You are either mist...

entering multiple costs for an item
I am very new to Retail Management, and am trying to enter inventory, and create a purchase order. I can't figure out how to enter various costs for the same item from the same supplier. For example, when we purchase in lots of 50 pcs., the item cost is .50 per item (total cost of 25.00). However, if we purchase in lots of 100 pcs, the item cost is .47 per item (total cost of 47.00). We can also purchase the item in boxes of 10 (5.50 per box for a cost of .55 per item). How can I enter these costs so when I place a purchase order, the cost will be correct according to the quant...

Query emails coming to a specific domain
Hello; We have about 5 domains on our exchange server that we receive email from. We are in the process of not renewing one of our domains but would like to check to see if email from that domain is still coming through. Is there any way to do that? I tried the Message Tracking Center with the *.domain.com but it didn't like that. It wanted a specific user. Thanks Check SMTP logs as well. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <nospam@newsgroup.com> wrote in message news:...

Auditing Mailbox item access
Don't ask me why I am asking, you don't want to know....but Is it possible to monitor and log access not only to a mailbox, but actually to individual items within the mailbox and be able to trace it back to the specific item and user who accessed it? I am aware of the ability to log when a non-primary account logs into a mailbox, but that is not granular enough for the scenario I need to look into. The powers that be want to be able to see not only who accessed a particular mailbox, but actually who accessed a particular message within that mailbox. Just wanted to know if...

A SQL error has occurred. Going off line with Outlook CRM 4.
I have seen this in multiple workstations. Running the diagnostics, does not report any errors, however removing the temprary data under advenced fixes the problem by deleting the content of the off line database. The problem with this fix is that it may take up to 12 hours for some of our overseas employees to do their initial off line. When is SP1 due? ...

Add items to SupplierList
I just imported a new catalog of items to our database using EMS import, were newly insterted and some were updated. The problem I'm having is getting the supplier cost to update for the items that were in already and adding the new items to the supplierlist table. I imported the SupplierID to the Item table just can't figure out the query to go the other way updating and adding new. Any help would be great. You need to use T-SQL in the management studio. Use an InnerJoin of the supplier id to the item id and set the cost to = supplier cost if it is the most recent. "Do...

getting consolidate list of "follow up" items
Dear All, I would like to get a consolidated list of items (sent and received mails)that have matured - i.e. whose follow up time is up. I have the habit of tagging a sent mail (after actually sending) for follow up. Similarly, mails received are also tagged for my further action (for follow up). Would appreciate a consolidated follow up report generation way - rather that opening each folder and looking for mature items (i.e. red). Thanks in advance. Sanjay ...

401k Tracking Without Itemized Details
My 401k does not give me an itemized listing of investments. Instead it lists them by investment type as follows. Type Shares Price Market Value Company Owned Stock 1000 65 65000 Equity Units 150 300 45000 Common Assets 250 1 250 I do know what the company owned stock is given it is who I work for. With each quarterly statement it updates the inform...

query to make a list of products based on delivery history
I have a table which lists all deliveries made of our product. From this table, I'd like to make a list of all products. My problem is the products will have many duplicates as they can be ordered multiple times and I just want a list showing all the individual products that we offer. -- TIA Try something like: SELECT DISTINCT [our product] FROM [which list all deliveries made]; If you can't figure this out, come back with table and field names. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Def...

Update Query based on Current Recordset
I have a continuous form with filtered records. I want to run an update query on a field in a table based on the current filtered records on the form. What code would I use to accomplish this task? Any help appreciated in getting me started. NEWER USER, Without a wee bit more information, like what are you trying to update, field names, table names, do you want this attached to a button or an event, etc... The best I can do is... UPDATE SomeTable SET SomeTable.SomeField= WhateverValue WHERE (((SomeTable.SomeOtherFied)=WhateverValue)); Of course, the above would be an ...

Changing icon for list item
Hi all, I would like to change the icon for the first column of the list control in report view. Right now, the method I used is remove the LVITEM at that location and reinsert at the exact same place with a different icon. I know this is not efficient compared to directly changing the icon. I had tried GetItem adn SetItem but even though i specified a different icon using the index into the imagelist, it still won't change. Could someone please enblighten me on this? thank you very much. Hallo Lonewolf! > I would like to change the icon for the first column of the list &...

Updating item properties.
We want to change the commission properties for items in our database, with different commission rate for different as below: Men’s Clothing – 2% Women’s Clothing – 2% Men’s Accessories – 1% Men’s Accessories – 1% And this will be a percent of sale. Is this done with an update query? It can be done with an SQL update query. How do you know if an item is men's acc... ? "Davidjc52" <Davidjc52@discussions.microsoft.com> wrote in message news:C57AEFC9-2DEC-46C7-AD96-EE950322155E@microsoft.com... > We want to change the commission properties for items in our database...

IIF Query for Numeric Values in Text Field
I’m attempting to flag interest rate spread errors and omissions in a file from a sales database using IIF statements I adapted from another database (I’m a novice at this). The field I’m querying is text format and contains both alpha and numeric values. When I test for a null value the IIF works fine, but when I test for a numeric value I get an #ERROR. Spread Error1: IIf(([Tbl 1 Eligible Closed Deposit Opps Appended]!Spread Is Null),1,0) Spread Error2: IIf(([CCC Fall 2007 Eligible Products]![IB/NIB]="NIB" And [Tbl 1 Eligible Closed Deposit Opps Appended]!Spread<4),1,0...

On Hand Quantity to be Adjusted by Picked (or Fulfilled)
In order for the On Hand quantity to match what is on the shelf at any given time, the On Hand should be adusted by the picked/fulfilled but not posted quantities. Those adjusting fields should be available quantities to view on the Item Inquiry screen and SmartList "Item by Site", as well. On Hand = Calculated On Hand after posting less unposted picked/fulfilled quantities. The less frequent the invoicing/posting occurs, the more this is an issue. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To ...

Insert query with two left outer joins gives "Record is Deleted" m
Hi, I have built an insert query to combine data from 3 tables into one table based on a key value (RA_ID) on a form. Two of the from tables may or may not have data associated with the main from table. So I have coded the select from statement using left outer joins. When I run the query with a row in the first child table but not in the second it works fine but when I run the query with a key value that exists in the 2nd child table but not in the first I get a message saying "record is deleted". Anybody have any ideas what is causing this? Here is the query......... ...

getpivotdata multiple items
Is there any way to have getpivotdata retrieve more than one set o data, other than doing it like below? =GETPIVOTDATA('Roll-up Pivot'!$F$2,"hours enrg E &I$163)+GETPIVOTDATA('Roll-up Pivot'!$F$2,"hours enrg M " &I$163) For example, would it be possible to us a range of cells as a referenc (&I$163:I$180)? Thanks, Stephani -- Message posted from http://www.ExcelForum.com ...

SQL Server and MSDE ?
cenario.... I have a server box running SQL Server 2000 and RMS HQ Server. I have another server running MSDE, RMS SO and HQ Client. (at another location, connected via ADSL VPN) The problem i have is that HQ Client downloads all worksheets from HQ Server. BUT it will not upload data back to HQ Server on a 401 it gives me a correlation error. SQL Server and MSDE use different collation. any ideas on how to change the default collation so they are both the same? You can do it using SQL - see SQL Books online or MSDN. Also, HQ Administrator and SO administrator both have options on the dat...

SQL Server 2000 / GP 8 Performance problem
Hi, We have a default installation of SQL Server 2000 servicing about 5 GP clients. As soon as more than one person tries using the payroll module everyone starts slowing down. A nework trace indicates multiple small queries going back and forth (ping pong) creating stored procedures and executing them. This looks like SQL server issue at first sight. Can someone give me ideas on where I can start looking. thanks Hi Have you applied SQL SP3a onto the SQL server? Regards James "Dilan Arumainathan" <dilan_a@antispamimpark.com> wrote in message news:egPPRo%23BFHA.3820@TK...

Postpone subform query in tabbed control
I have a form containing a tabbed control. One tab in the tab control contains a subform loaded from a query. As I understand it, every time I page from row to row on the main form, the query in the subform runs. I dont want the query to run until the tab is accessed. If I happen to be on the tab, I do want it to run. Hope this makes sensnse. Is this possible? ...