Like numbers in a field

I am working with a 25,000 row parts issue listing that was created by parts 
issue date.  There are many repeat numbers in the part number column as the 
same part would have been issued on several different dates.  I would like to 
be able to combine the like part numbers to know how many parts were issued 
over the course of all of the dates.  Any ideas???  The Column headers are 
Part Number, Quantity, Unit Price, Total Price and Transaction Date.  
0
Utf
5/20/2010 3:34:06 PM
access 16762 articles. 3 followers. Follow

4 Replies
650 Views

Similar Articles

[PageSpeed] 28

Create a new query based on your parts issue listiong table. Drag the Part 
number field into the query grid twice. Click on the Totals button, (the 
greek sigma icon on the toolbar). Leave the first part number in your query 
as Group By, but change the 2nd one to count. Run the query. 

"Ralph Kramden" wrote:

> I am working with a 25,000 row parts issue listing that was created by parts 
> issue date.  There are many repeat numbers in the part number column as the 
> same part would have been issued on several different dates.  I would like to 
> be able to combine the like part numbers to know how many parts were issued 
> over the course of all of the dates.  Any ideas???  The Column headers are 
> Part Number, Quantity, Unit Price, Total Price and Transaction Date.  
0
Utf
5/20/2010 3:45:01 PM
Try this --
SELECT [Part Number], Sum([Quantity]) AS Total
FROM YourTable
WHERE [Transaction Date] Between CVDate([Enter start date]) AND 
CVDate([Enter end date]) 
GROUP BY [Part Number]; 

-- 
Build a little, test a little.


"Ralph Kramden" wrote:

> I am working with a 25,000 row parts issue listing that was created by parts 
> issue date.  There are many repeat numbers in the part number column as the 
> same part would have been issued on several different dates.  I would like to 
> be able to combine the like part numbers to know how many parts were issued 
> over the course of all of the dates.  Any ideas???  The Column headers are 
> Part Number, Quantity, Unit Price, Total Price and Transaction Date.  
0
Utf
5/20/2010 3:48:06 PM
To get the total of all quantities per part issued you need to group the
query by Part Number and Sum the Quantity column.  In SQL it would look like
this:

SELECT [Part Number], SUM([Quantity]) AS [Number Issued]
FROM [YourTableNameGoesHere]
GROUP BY [Part Number];

Ken Sheridan
Stafford, England

Ralph Kramden wrote:
>I am working with a 25,000 row parts issue listing that was created by parts 
>issue date.  There are many repeat numbers in the part number column as the 
>same part would have been issued on several different dates.  I would like to 
>be able to combine the like part numbers to know how many parts were issued 
>over the course of all of the dates.  Any ideas???  The Column headers are 
>Part Number, Quantity, Unit Price, Total Price and Transaction Date.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1

0
KenSheridan
5/20/2010 4:02:12 PM
Great Infomation!!!  You guys really make it seem so simple.  Thanks for 
information!!!!

"Ralph Kramden" wrote:

> I am working with a 25,000 row parts issue listing that was created by parts 
> issue date.  There are many repeat numbers in the part number column as the 
> same part would have been issued on several different dates.  I would like to 
> be able to combine the like part numbers to know how many parts were issued 
> over the course of all of the dates.  Any ideas???  The Column headers are 
> Part Number, Quantity, Unit Price, Total Price and Transaction Date.  
0
Utf
5/20/2010 6:27:01 PM
Reply:

Similar Artilces:

Access 2000
Hello all, I have a field named Field213 that holds (CASETYPE) from the database. When I open the form, I want it to look at that field and make field163 visible depending on what it stored in Field213. Right now it only becomes visible if I manually change what is in field213, then field163 becomes visible. I should mention that I have field163 as visible no and display always, the same as field PBISUB, Field382 and Field163. Probably something simple, but I can't seem to see it right now. I have the following: Private Sub Field213_BeforeUpdate(iC As Integer) If Me!CASETYPE = &qu...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Serial number error in sales order entry window (2nd post)
I'm reposting this because there was no response to my first post. I'm sure I'm not the only one that has had this problem. I noticed that one of my coworkers invoiced and added (overided) a serial number into the Great Plains instead of allocating one of the existing ones. How do I return this serial number w/out affecting my inventory? I need to: 1. Return the item w/the wrong serial number. 2. Reinvoice the correct serial number. 3. NOT HAVE THE WRONG SERIAL NUMBER STUCK IN MY INVENTORY. How do I do this? Hi Paoakalani Try looking at Serial Number edit, by Blue Moon...

Manager Assist ...... Field Service Module
Hi to all, I wnat to ask about Manager Assist, that is used to manage the calls on the service call module...... is it a GP module or it is a separate software thank you in advance, ManageAssist is a seperate VB application that connects to the GP Field Service tables. It allows for both viewing of schedule calls as well as updating of certain records (i.e. Unavailable time, sick time, etc.) -- Gary Neumann Field Service Series Designer/Architect "Eng. Tareq Sabri" wrote: > Hi to all, > > I wnat to ask about Manager Assist, that is used to manage the calls on th...

Multi-field search form
Hi there, I am rather new to this place, so I would like to thank you all up front for any and all assistance you may provide. :) It has been a while since I worked with MS Access last. What I am trying to do is create a multi-field search form, where any of the fields can be filled which will display a result set. Preferably if no selection in the fields is made, the entire resultset should be shown - this is not a requirement, though. I seem to be a bit at a loss on how to procede with this task or to find any resources to help me get along. Again, thanks for any and ...

Odd Looking Number
Hi I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the account numbers (which someone input, along with addresses) have a little green mark in the corner of the cell. I thought it was to show that there is a comment, but it is not. When you click on the cell, a little warning sign shows, saying ERROR - number in this cell formatted as text or preceded by an apostrophe. When I click on the arrow by the warning sign, it says Number stored as text Convert to number Ignore error Edit in formula bar Error checking options Show formula in audinting toolbar Now I l...

Edit the Auto Fill in the To: field
On the Send new message window, is there a way to edit the auto fill list for the To: Field. when i start to type in a name in the To Field the auto fill will provide a list of names to choose from. the one i most use is on the bottom of the list. How do i edit the list to get the one i use most to the top? Do you use the others as well? Editing the list will remove them from the list. If you again use the names, they will be added back to the same position. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-...

display the value of an unbound textbox in a field within a table
I created 3 textboxs to calculate the number of business days between 2 dates. The 3rd textbox contains the value. I now want to have this value displayed in a table field. I'm sure this is easy but I'm a complete novice and have spent far too much time on this already. Can anyone help! Fibi, The general concept of tables is that they are for storage of data in the background. They are not for display of data. That is what forms and reports are for. Therefore, the appearance of the data in the tables is not normally relevant, and the display of calculated values is im...

Numbers turn red when equals to 0
Good day, Im monitoring our stock inventory using excel, i just want to know if I can make a certain cell turn red if my inventory is >5 or equal to zero.? Thanks -- shiela21cute ------------------------------------------------------------------------ shiela21cute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33169 View this thread: http://www.excelforum.com/showthread.php?threadid=529926 Check out Conditional formatting Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum...

how field name output same as input
Thanks for any help. I have a query from a couple tables, and one of the field names in the table is 'IGP' and I have a field in the query that was IGP and now I changed to IGP: iif(blah IGP blah) which gives me a circular error. The table is used by a bunch of other things, so I don't want to change the field name there, and the field name for the output is used by a bunch of things, so nor do I want to change the name there. I am using Access 2007. I thought about changing the output field to IGP_, and I could change the embedded queries in Excel that use it, but...

passing non-field value into vba
I need to pass the line item sequence number (lnitmseq) from the sales item detail entry window into vba. This isnt a visible field in the window, so i cannot "add field to vba" using the TOOLS --> customize toolbar window. Anyone have any idea how this can be done? Using the Modifier you can make the field visible, add it to VBA and then remove the modified window or make the field invisible again. While in the Window Layout mode, make sure that the Invisible fields are shown (Layout >> Show Invisible Fields) and then change the Visual Property, Visible = true and mov...

when I type a long number it shows up as smaller number and +
when converting from xls to csv format, some of my longer mortgage numbers get condensed into a smaller number with a letter, a plus sign and another number. example: 100020013120 turns into 1.0002E+11. Any way to get rid of this? Thanks, Yes, just specify the format that you want. Format Cells...>Number>0 decimal places Regards, Fred "SandyC" <SandyC@discussions.microsoft.com> wrote in message news:FA52E426-360E-4BE5-B63B-1209E673CA65@microsoft.com... > when converting from xls to csv format, some of my longer mortgage numbers > get conde...

Derived Field
I'd like to add a derived field to this situation: Parent Table : Shipments (pk)Shipment_No Shipment_Date Shipment_Size ....other fields... Child Table : Shipment_Inspection Shipment_No (connected field) Number_Out_Tolerance (bad ones in the shipment) ....other fields... I'd like to make a derived field "der_percent_good" that takes the shipment size that the inspection belongs to and does (1 - Number_Out_Tolerance/Shipment_Size)*100 to find the percent of the shipment that is Good. Thanks! ...

Date/Time field calculation
Hello, i am trying to make a sum calculation on a field that keeps duration of time. How can i do this? I get errors or zero as result. Thanks On Jun 21, 10:02 am, Stathis <s_paraske...@hotmail.com> wrote: > Hello, > i am trying to make a sum calculation on a field that keeps duration of > time. How can i do this? I get errors or zero as result. > Thanks You need to do the calculation at a level at least 1 level below what you want the answer to be in. Example msgbox DateDiff("h", #6/20/2007 1:00:00 AM#, #6/20/2007 1:30:00 PM#) will give an ans...

Rule not matching "from" field
I want to set up a rule that will match words in the "From" field. Sometimes I get mail like this: From: bill@somedomain.com [mailer@someotherdomain.com] I want to match "bill@somedomain.com". I can't figure out how to match it. I can't pick "from people or distribution list" because it won't let me match arbitrary email addresses. I can't pick "with specific words in the sender's address" it just doesn't work. So far as I can tell, it will only match the part in the brackets. Anon E. Mouse <fake@fake.com> wrote: &...

Rotating page numbers
I have a document with both page layouts (landscape and portrait) however the page numbers obviously will be pointing in the direction of the associated layout does anyone know how to be consistent in this? ...

Hide a field in a report
I am building a report using fields that are in tables. One of the fields in the report is "Do Not Use". I want to build an expression that says if "Do Not Use" is marked "1" in the table, then remove that line from the report. If "Do Not Use" is marked "0", keep that line in the report. Please help! Thanks! In your query use a calculated field like this -- Hide_Field: IIF([Do Not Use] = 1, Null, [Do Not Use]) Then in the report set the textbox Can Grow property to Yes and drag the size of the textbox to a hairline. If the output is...

wrapping text in a query field
I set up the field in table to memo and tried entering a lot of information but when I open the report that field does not expand to show all of the entries. Can this be done in query and reports both. Thanking you in advance. Mary Lou On Dec 12, 12:06 pm, MaryLou <Mary...@discussions.microsoft.com> wrote: > I set up the field in table to memo and tried entering a lot of information > but when I open the report that field does not expand to show all of the > entries. Can this be done in query and reports both. > > Thanking you in advance. > Mary Lou Go to the p...

Inserting word 'merge field' into Excel
I have data in Word merge fields that I need to put in an Excel table. Each Word file is exactly the same and there are a few hundred files. If I can't do this direct to a spreadsheet, can I do it through Access? ...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

Sequential numbering of invoices
I have recently downloaded an Excel template for invoices from Microsoft's website. Is there any way to fix it so that every time I create a new document based on this template, it automatically gives it an invoice number one greater than the previous invoice (eg, starting at 100 and then the next one I open would be 101, then 102 and so on)? http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Regards, Peo Sjoblom "Nick Xylas" <nickxylas@wmconnect.com> wrote in message news:1180551140.693535.260930@g4g2000hsf.googlegroups.com... >I have recently download...

Convert a number to a time
I have the following number 232.5 which is 232 hours and 0.5 of an hour. I want to convert this to 232 hours and 30 minutes or 232:30. How can i do this? I know i need to multiply the decimal by 60, but how can i perform a calculation on just the decimal part of a number and not the whole number? Depending what you wish to do excel stores times as well as dates as numbers, with 1 being a full day. An option is to divide everything by 24 and use a custom format of dd hh:mm although this will express the hours as days and hours It depends what you want to do really -- Dav --------------...

Parsing excel field help !
Does any know how can I check all the characters of a field and check that there is not an alphanumeric character (a-z) and if there is one changing to zero. Ex. 5J00 = would like to change it to 5000. 20A5 = would like to change it to 2005. Thank you. Luis try Sub changecharactertozero() For Each c In Selection For i = 1 To Len(c) If Not IsNumeric(Mid(c, i, 1)) Then c.Replace Mid(c, i, 1), "0" Next i Next c End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com <luis.a.roman@gmail.com> wrote in message news:1170511995.662504.85390@q2g2000cwa.googl...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...

find first number
How do I find the first number in a string? I need to update a field by removing anything that could not be a number. Unfortunately, there's no specific number of characters which I can use to start and stop. Intentions are to replace everything before the first number and after the last number. Fortunately, the numbers I want are grouped together, but in the missle of strings. thanks Here is one example: CREATE TABLE Foo ( foo_key INT PRIMARY KEY, foo_data VARCHAR(35)); INSERT INTO Foo VALUES (1, '1'); INSERT INTO Foo VALUES (2, 'aa21a'); ...