Showing number series in query for grouped items

Hi,

I have a stock table which has serial numbers for each item.  I want a query 
which groups the item types and shows the first and last serial numbers for 
that item type along with how many.  Eg. 10 x mobile phones first serial 
number = xxx1, last serial number = xx10.

I tried the first, last, min and max functions but they continue to group 
and show each of the 10 items individually.

Any ideas??

Thanks
0
Utf
2/14/2008 12:27:01 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
1084 Views

Similar Articles

[PageSpeed] 48

On Thu, 14 Feb 2008 04:27:01 -0800, Flanders
<Flanders@discussions.microsoft.com> wrote:

>Hi,
>
>I have a stock table which has serial numbers for each item.  I want a query 
>which groups the item types and shows the first and last serial numbers for 
>that item type along with how many.  Eg. 10 x mobile phones first serial 
>number = xxx1, last serial number = xx10.
>
>I tried the first, last, min and max functions but they continue to group 
>and show each of the 10 items individually.
>
>Any ideas??
>
>Thanks

SELECT   Stock.Type_Code,
         COUNT(Stock.Serial_Number)  AS [Item Count],
         (SELECT TOP 1 A.Serial_Number
          FROM   Stock AS A
          WHERE  A.Type_Code = Stock.Type_Code) AS [First Serial Number],
         (SELECT   TOP 1 A.Serial_Number
          FROM     Stock AS A
          WHERE    A.Type_Code = Stock.Type_Code
          ORDER BY A.Serial_Number DESC) AS [Last Serial Number]
FROM     Stock
GROUP BY Stock.Type_Code;
0
Michael
2/14/2008 2:23:43 PM
Thats great thank you but I need to append the results to an "Invoice" table 
which already exists.  Is there no way this can be done in an append query?

"Michael Gramelspacher" wrote:

> On Thu, 14 Feb 2008 04:27:01 -0800, Flanders
> <Flanders@discussions.microsoft.com> wrote:
> 
> >Hi,
> >
> >I have a stock table which has serial numbers for each item.  I want a query 
> >which groups the item types and shows the first and last serial numbers for 
> >that item type along with how many.  Eg. 10 x mobile phones first serial 
> >number = xxx1, last serial number = xx10.
> >
> >I tried the first, last, min and max functions but they continue to group 
> >and show each of the 10 items individually.
> >
> >Any ideas??
> >
> >Thanks
> 
> SELECT   Stock.Type_Code,
>          COUNT(Stock.Serial_Number)  AS [Item Count],
>          (SELECT TOP 1 A.Serial_Number
>           FROM   Stock AS A
>           WHERE  A.Type_Code = Stock.Type_Code) AS [First Serial Number],
>          (SELECT   TOP 1 A.Serial_Number
>           FROM     Stock AS A
>           WHERE    A.Type_Code = Stock.Type_Code
>           ORDER BY A.Serial_Number DESC) AS [Last Serial Number]
> FROM     Stock
> GROUP BY Stock.Type_Code;
> 
0
Utf
2/14/2008 2:33:00 PM
On Thu, 14 Feb 2008 06:33:00 -0800, Flanders
<Flanders@discussions.microsoft.com> wrote:

>Thats great thank you but I need to append the results to an "Invoice" table 
>which already exists.  Is there no way this can be done in an append query?

You want to store three calculated values in a table?  That is not good
practise. It makes no sense to me. Anyway, you have not provided any information
about the Invoice table.
0
Michael
2/14/2008 3:16:32 PM
Hi,

I know this is not an ideal scenario.  It is being used as a temporary fix 
for a sales department who allocate stock to a dealer and then produce an 
invoice based on that.  The stock is allocated but as I do not want lines and 
lines on the invoice for the same items I wanted to group them together to 
show how many of each item had been allocated.  I have the majority of this 
done but have now been asked to include the serial numbers range on the 
invoice for the items listed.

If this cannot be done they will have to just make do !!

The invoice table is just a record of the invoices produced so it has 
invoice number, number of items, item type, first serial number, last serial 
number, cost per item and total cost.  Most of which are calculated fields 
based on the stock allocation table.  I just cannot get it to show first and 
last serial numbers !

Thanks

"Michael Gramelspacher" wrote:

> On Thu, 14 Feb 2008 06:33:00 -0800, Flanders
> <Flanders@discussions.microsoft.com> wrote:
> 
> >Thats great thank you but I need to append the results to an "Invoice" table 
> >which already exists.  Is there no way this can be done in an append query?
> 
> You want to store three calculated values in a table?  That is not good
> practise. It makes no sense to me. Anyway, you have not provided any information
> about the Invoice table.
> 
0
Utf
2/15/2008 9:39:00 AM
I have now fixed the problem !  Thank you

"Flanders" wrote:

> Hi,
> 
> I know this is not an ideal scenario.  It is being used as a temporary fix 
> for a sales department who allocate stock to a dealer and then produce an 
> invoice based on that.  The stock is allocated but as I do not want lines and 
> lines on the invoice for the same items I wanted to group them together to 
> show how many of each item had been allocated.  I have the majority of this 
> done but have now been asked to include the serial numbers range on the 
> invoice for the items listed.
> 
> If this cannot be done they will have to just make do !!
> 
> The invoice table is just a record of the invoices produced so it has 
> invoice number, number of items, item type, first serial number, last serial 
> number, cost per item and total cost.  Most of which are calculated fields 
> based on the stock allocation table.  I just cannot get it to show first and 
> last serial numbers !
> 
> Thanks
> 
> "Michael Gramelspacher" wrote:
> 
> > On Thu, 14 Feb 2008 06:33:00 -0800, Flanders
> > <Flanders@discussions.microsoft.com> wrote:
> > 
> > >Thats great thank you but I need to append the results to an "Invoice" table 
> > >which already exists.  Is there no way this can be done in an append query?
> > 
> > You want to store three calculated values in a table?  That is not good
> > practise. It makes no sense to me. Anyway, you have not provided any information
> > about the Invoice table.
> > 
0
Utf
2/15/2008 10:25:00 AM
Reply:

Similar Artilces:

Exchange/Outlook 2003
In an effort to make sure we never reach the 16GB threshold for the SBS 2003 Exchange Info Store, I'm looking to setup a GPO to give everyone consistent AutoArchive settings as well as a common location for the PST file. I have already downloaded the Office Administrative Templates for Outlook 2003, so I already have found the settings I want to modify and have made the changes, I just haven't made them live on the domain yet. My thoughts to date: * create two GPOs, one for the PST location, one for the autoarchive settings themselves * set PST location to local drive, or common netw...

page numbers disappear
Page numbers disappear after page 10 whenever I do a booklet. On Sun, 19 Feb 2006 02:03:26 +0000, 4Av wrote (in article <C5C8808C-2FA7-4019-93DF-24FA9FAB21C3@microsoft.com>): > Page numbers disappear after page 10 whenever I do a booklet. And we're supposed to guess what version you're running are we...? We're not psychic - please elaborate on 'disappear' - do they just vanish as soon as you enter them, or is this resuming a saved document, or what? Or do they not appear at all... The devil's in the details... ;o) Do you have a Master page? Have you ch...

Need ability to print all tracking numbers on invoices
We need the ability to print *all* tracking numbers on invoices. We can have literally hundreds of tracking numbers associated with one invoice and under the present setup (using rw_CreateSOPTrackingNumberString) we can only show 4 1/2 numbers. -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 ---------------- 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 open the sug...

Outlook shows offline during dial up
Having a problem with outlook 2002. Over the WAN everything works fine. Dial up and connect to the network (VPN) internet work fine. Outlook will sync up however, a red x shows up in the bottom right hand corner. Outlook shows offline, I can send and receive but must hit send and receive. I have reloaded the computer from scratch and still have the same problem. Could it be a Excange setting? Any Ideas? Thanks for the help, If you hit File | Connect to Exchange Server what happens? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize ...

Distribution Group Only From:
I have a distribution group that contains all of the users for our entire organization and I don't want it accessible from everyone, so I deselected everyone. However we do need the distribution list to receive emails from one specific SMTP address which is external to our organization. I have tried creating a contact with the email address that should have the ability to send to this distribution group and entered it in the Only From:, however they are still receiving a bounce back that they don't have permission to send to that distribution group. Anybody have a solution? ...

analytical accounting lookups should only show valid codes
When entering AA transaction distributions, the code lookup window should only show valid code combinations. It's not intuitive for the user to have to select a code and get an error message to discover which codes are valid. ---------------- 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 open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the mess...

Adding a new part number.
If we choose "edit item" and to make a new part number everything is fine. When we choose "New Item" from the Items menu, then choose "standard Item" I get a run time 94 error "invalid use of null" Any thoughts? SJ This is a multi-part message in MIME format. ------=_NextPart_000_014A_01C4B158.92936C70 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Run-time Error 94 "Invalid Use of NULL" When Editing Items in QuickSell = 2000 (869936) Confidential Article (The information in ...

Manupilate Query Results
Hi all. I need to change the way data is diplayed after a SQL query. What I am tying to do is organise data in such a way that MS project will open the file with minimum mapping. See below what I have got and what I need: I have got: Cust|CallType|Priority|CallID|Asignee|Date ect ect A | HR | X | 12 | Me | 0/0/2003 A | HR | X | 12 | John | 0/0/2003 B | Deve | Y | 14 | Stan | 0/1/2003 What I need the data to look like is: (the 1-4 is for MS Project Indents) 1 |A | 2 |HR| 3 |X | 4 |12 | Me | 0/0/2003 4 |12 | Joh...

reminder still shows up
when I run Outlook.exe /cleanreminders it simply opens Outlook, I am running Vista "Toppro" <Toppro@discussions.microsoft.com> wrote in message news:7AE8DBF7-6BE3-454A-82F9-5A040566026C@microsoft.com... > when I run Outlook.exe /cleanreminders it simply opens Outlook, I am > running > Vista Outlook version? The Windows version isn't as important and that of Outlook. -- Brian Tillman [MVP-Outlook] ...

number formatting #4
Hi. I was woundering how I can add trailing zero's to numbers. I have a colunm with number lemgths between 1 and 7 digets and need them all to be 10 diget's long. I have no trouble with adding the zero's as headers but needt hem as trailers. Thanks. Hi zuraan, You could put the following formula in a column beside the numbers and then Copy, Paste special, Values over the original numbers. =A1*10^(7-LEN(A1)) The formula assumes that the number is in cell A1 -- Regards, OssieMac "zuraan" wrote: > Hi. I was woundering how I can add trailing zero's to number...

Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find an answer searching the topics. I'm using the following formula in cell C3: =INDEX(LastName,MATCH(A3,EmpID,0)) When I type in an employee ID in A3, his/her last name shows in C3. However, when there is no value in A3, C3 shows error "#N/A". Is there a way to show a blank cell in C3 until a value is entered into A3? Thanks in advance! Mike On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote: > I'm using the following formula in cell C3: > =INDEX(LastNam...

Show Window
I am currently developing a MFC SDI application. I want to integrate a tray icon with it. When the application is minimized it hides by ShowWindow(SW_HIDE) and then when double clicked on tray icon shown by ShowWindow(SW_SHOWNORMAL). But the problem is if some other application is activated after my application is minimized, ShowWindow shows it but it's behind some screen. I have tried with SetFocus didn't work. Please help me to solve this problem. Thank you. Varuna Try BringWindowToTop. ---- Ajay Kalra ajaykalra@yahoo.com In addition to Ajay's idea you can also use SetFor...

Information Stores show dismounted yet mail is still flowing...
Background: Windows AD Mixed moded. Exchange 2003 installed on to a 2 node Microsoft cluster. 1 Front End server...connecting to the primary cluster node. Windows 2003 Standard on, FE Windows 2003 Enterprise Server on BEs (<---cluster) no service packs on either. Exchange has SP1 installed on both nodes and the FE. There are still Exchange 5.5 servers in the environment although they are not being used. The Issue: .....the information stores and public folders all show that they are dismounted...yet email is still flowing into and out of the server without any issues. I just...

NNTP groups go missing OT
Sorry - a bit off topic, but I have been accessing Win 7 groups with the NNTP bridge. Today they've disappeared. When selecting a previously accessed group, WLM says no such group and deleted the saved posts. NNTP bridge still works and I can access other groups, but all the W7 groups are gone. Very annoying! Anyone else seeing this? The MS Answers has a separate Bridge now, which you must download separately. "The teams responsible for Microsoft's community forums would like to make you aware of changes to the Microsoft Answers platform. In order to s...

Using ajax call to fetch multiple results from multiple queries and showing them 1 by 1 as the results comes.
Hello, I've 20 labels in a 2x10 table on a page. for each label, data comes by individual queries. (total 20 queries for the page.) and it takes about 30-40 seconds for the page to load, and it's not comfortable for the user to see blank page. I want that each cell will show loading.gif images. As and when data comes from query, the images should go visible=false, and the label should display the values one by one. How do I do this task using ajax? Some ASP.NET code example will be helpful for me, as I'm a kind of beginner. Thanks ...

difficulty with queries
i've been struggling with this for a couple weeks now and haven't been able to find aything in the foums on it. k, i am try to make a query that will sort by date (only displaying the parameter date), then count the results to show on a weekly report, forcasting for next week (which i am also haveing probs with) table 1 last name || fist name|| vacation start date|| vacation end date and i want the result to be query 1 date || count of ppl on vaction on that date report 1 monday||tuesday||.........so on 10 12 the prob i have is that if i do the query on a specific...

Results show as a minus. WHY?
I have an application that was developed by another developer using VB.net which reside in a SQL table. I need to use some of the data from this table, however the data shows as -0.5 or - 0.99 or -1.70 etc in my Access tables. How can I get those numbers to show as a percentage with only one decimal point to the left? Example: 3.6% or 31.4 %. Any help would be greatly appreciated. No matter what I do it shows that dang "-" at the beginning. You can use the Abs function to strip off the negative Abs([YourTable].[YourField]) And you can format that as percentage either using th...

ListView_SubItemHitTest redraws items
I have created my own CListCtrl derived control. In this I need to override OnToolHitTest(). In this a call ListView_SubItemHitTest() to detect over which item the mouse pointer is. But when I do that the item is redrawn, which make my list flickering. This only happens when I have a manifest file and when the application is dialog based and the application run on WinXP. The following code illustrates the problem: CFlickerList is derived from CListCtrl: void CFlickerList::OnCustomDraw(NMHDR* pNMHDR, LRESULT* pResult) { NMLVCUSTOMDRAW* pLVCD = reinterpret_cast<NMLVCUSTOMDRAW*>( pNM...

Excel 2007 counting numbers
In Excel 2007 I have a question... I have a cell in a column B1: MJTB1001 I need to paste to approximately 235 new rows in the column B1 as follows MJTB2002 MJTB3003 MJTB4004... etc. What formula should I use to accomplish this task? Also I have the cell MJTB2044 and I need to make 235 new cells in column to fit like this... MJTB2044 MJTB3044 MJTB4044 MJTB5044 ... etc... 235 times.... Any thoughts? Thank you. In B2 put ="MJTB"&1001+(ROWS($B$1:B1)*1001) copy down 235 rows, select the range and copy and paste special as values in plac...

show comments when cell is highlighted Vs when cursor runs over? #2
How do you make the comments appear when the cell is highlighted opposed to when the mouse cursor runs over the cell? You can press Shift/F2, or Insert/Edit Comment. If you wanted to see it "automatically", when you select the cell, you'd need a macro, but I don't think this is what you're asking. Bob Umlas Excel MVP "Don" wrote: > How do you make the comments appear when the cell is highlighted opposed to > when the mouse cursor runs over the cell? To at least Excel 2000, if you have the Tools, Options, Comment Indicator Only you will see the co...

how to show numbers that begin with 0 in excel
I need to type numbers into an excel spreadsheet some of them begin with a zero some do not. How can I have excel show the 0 in the numbers that begin with 0. If I type 0236547 the number shows as 236547. I don't want all the numbers I type to begin with zero. I am using Excel 2003. Thanks very much for your help. Best regards, Dee use a custom format with how ever many digits you need, 000000 -- Gary Keramidas Excel 2003 "Dee" <Dee@discussions.microsoft.com> wrote in message news:EA60FC8A-C7CB-432A-B2B2-EEEBF86D2646@microsoft.com... >...

how to query a dataset with xml tables inside.
This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C3DB37.4468A9E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have two xml docs, authors & articles. Both have authorID as a common = node <?xml version=3D"1.0" encoding=3D"utf-8"?> <!--This is a comment--> <Authors> <Author> <AuthorID>0</AuthorID> <Name>Mark</Name> <Login>mark</Login> <Pwd>markpwd</Pwd> </Author> <Author> <AuthorI...

Substracting numbers in non contigous cells
Hi! I need a formula to substract numbers in non contigous cells THAT ARE IN ONE COLUMN and have the result in other cell, for example: ..=2E.....................A...................B R1..................................BLANKCELL R2..................................BLANKCELL R3..................................15 R4..................................BLANKCELL R5..................................BLANKCELL R6..................60.............75 R8..................................BLANKCELL R9..................................BLANKCELL R10................................BLANKCELL R11...............

query tables for incomplete orders
I have an ordering database. I would like the ordering staff to be able to see if there are new orders from the front screen (switchboard). When the ordering staff sign off an order they tick a box which removes it from the list of items on order. Does anyone have any ideas or advice as to how I look up the orderdata tables and flag the ones which have not been signed off to the switchboard? I know what to do once the information is there but I am having trouble getting it there. Many thanks in advance for any help. Just an idea !! Create a query with the order information (just the...

Comments not showing
When a SharePoint workflow is edited and a comment is added. The comment does not display in the workflow status screen under workflow history. It shows as follows: Task assigned to Andre Wessels was completed by System Account. Comments: Instead of: Task assigned to Andre Wessels was completed by System Account. Comments: Please update to the new company logo. This happens for all standard SharePoint workflows (Approval, Collect Feedback). Any custom workflows does show the comment. The comment field is update in the task item. It just does not show. There is no error in t...