SQL Query problem

I have a table like below

Date, Qty
1-Jan, 10
2-Jan, 10
3-Jan, 20
4-Jan, 10

I want to write a query to display

Date_interval, qty
1-Jan to 2-jan, 10
3-Jan to 3-jan, 20
4-jan to 4-jan, 10 <-- seperate row for 4-jan even though the Qty is
same

I tried by grouping on Qty from which I get

Qty, Min(date),Max(date)
10, 1-Jan, 4-jan
20, 2-Jan, 2-jan  <-- no seperate row for 4-jan

Please help me in getting that extra row of 4-jan

Thanks
Pradeep

0
Pradeep
5/7/2007 3:02:19 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
762 Views

Similar Articles

[PageSpeed] 47

On 7 May 2007 08:02:19 -0700, Pradeep <agarwalp@eeism.com> wrote:

>I have a table like below
>
>Date, Qty
>1-Jan, 10
>2-Jan, 10
>3-Jan, 20
>4-Jan, 10
>
>I want to write a query to display
>
>Date_interval, qty
>1-Jan to 2-jan, 10
>3-Jan to 3-jan, 20
>4-jan to 4-jan, 10 <-- seperate row for 4-jan even though the Qty is
>same
>
>I tried by grouping on Qty from which I get
>
>Qty, Min(date),Max(date)
>10, 1-Jan, 4-jan
>20, 2-Jan, 2-jan  <-- no seperate row for 4-jan
>
>Please help me in getting that extra row of 4-jan
>
>Thanks
>Pradeep

I don't understand the logic here, Pradeep. Why does 4 Jan qualify for a
separate row? What is the "trigger" for going to a new row - a change in Qty,
or what?

             John W. Vinson [MVP]
0
John
5/7/2007 7:45:09 PM
On 8 May, 00:45, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On 7 May 2007 08:02:19 -0700, Pradeep <agarw...@eeism.com> wrote:
>
>
>
>
>
> >I have a table like below
>
> >Date, Qty
> >1-Jan, 10
> >2-Jan, 10
> >3-Jan, 20
> >4-Jan, 10
>
> >I want to write a query to display
>
> >Date_interval, qty
> >1-Jan to 2-jan, 10
> >3-Jan to 3-jan, 20
> >4-jan to 4-jan, 10 <-- seperate row for 4-jan even though the Qty is
> >same
>
> >I tried by grouping on Qty from which I get
>
> >Qty, Min(date),Max(date)
> >10, 1-Jan, 4-jan
> >20, 2-Jan, 2-jan  <-- no seperate row for 4-jan
>
> >Please help me in getting that extra row of 4-jan
>
> >Thanks
> >Pradeep
>
> I don't understand the logic here, Pradeep. Why does 4 Jan qualify for a
> separate row? What is the "trigger" for going to a new row - a change in Qty,
> or what?
>
>              John W. Vinson [MVP]- Hide quoted text -
>
> - Show quoted text -

The trigger to this request is that a letter is to be printed, the
contents of which should be,
Date               Del
1-Jan to 2-jan  10
3-Jan to 3-Jan  20
4-jan to 4-Jan   10

i.e. any break in the del. qty has to be seperate. If i group on qty i
get

Date               Del
1-Jan to 4-jan  10
3-Jan to 3-Jan  20

Above is confusing for the party to which the letter needs to be sent.

I hope my request is logical. I can do this with VBA (by populating a
dummy table) but just wanted to check whether a SQL query can be
written for this.

Thanks

Pradeep


0
Pradeep
5/8/2007 3:54:43 AM
Reply:

Similar Artilces:

Calling Dex Procedures from SQL
Is there any way to call dex procs from SQL or VB code? Jed There is an undocumented and unsupported method of calling Dex code from VBA. However, I know of no method of calling Dexterity from SQL. Please email me if you want an example, just remove the online. from the address below. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This p...

Problem with duplicate entries after sorting
I have a problem that has stumped me and I was hoping someone could hel me out. I have data in rows 2 through 15 with a header row and the blank rows until row 51. After I sort the data, I am getting duplicat entries for the data that was oirginally in rows 2 through 15, but th data that was orignially in rows 51 through 500 is fine. Only a coupl get duplicated, not all of them. Here is my code.... Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess OrderCustom:=1, MatchCase:= _ False, Orienta...

like query using field name
Hi All I have a column 'staffnumber' (int) and another column 'Resourceid' (nvarchar) in a table in sql server 2005 express I want to do a query to find rows where the staffnumber value is not in the Resourceid field e.g select mydate, customerid from appointments where Resourceid not like '%[staffnumber value]%' in other words the 'staffnumber' column value for each row is NOT in the 'Resourceid' column value Can this be done? Regards Steve Steve declare @par nvarchar(20) set @par='staffnumber' select my...

Why not answer printing problems
I have been waiting for an answer of how to print register, other people are trying to print home page, etc. Is this a problem that MS Money has ? In microsoft.public.money, fordfitz wrote: >I have been waiting for an answer of how to print register, other people >are trying to print home page, etc. Is this a problem that MS Money has ? A really neat way to find prior postings in this group is to use the Google Usenet Groups archive. http://groups.google.com/advanced_group_search enter words that you think would be in relevant posts into the "all of the words" box. Enter...

Internet Free/Busy problem
Hi, I'm trying to use the MS Internet Free/Busy Service. I have registered successfully at the website and added a couple of people. Unfortunately Outloook 2003 has the following two Internet Free/Busy options greyed out: Publish and search using Microsoft Office Internet Free/Busy Service (Manage button is also disabled) Request free/busy information in meeting informations ( greyed out but on) Any ideas? They discontinued the IFB service as few were using it. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert lat...

is SQL 2000 architecture different SQL Server 2005 / 2008 architecture?
Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I have been told is a good book. I am going backwards... reading about the sql server architecture in chapter 3 of the book. As I'm reading... I was wondering is it different SQL Server 2000 vs SQL Server 2005/2008? Also where can I find a book/or link that can give me more details about the internals on architecture? Thank you norm (normanchan@gmail.com) writes: > Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I > have been told is a good book. I am g...

Moving customizations from SQL 2k to SQL 2k5 CRM
This may be a non-issue but I wanted to check to see if anyone has done this and ran into issues. I have the XML customization files from our previous install of CRM 3.0 with SQL 2k database. I now want to import and publish these same customizations to CRM 3.0 on SQL 2k5. Has anyone done this or can point me to documentation that tells about the success or issues for doing this? Thanks. On 7 Feb., 06:35, Chris Treanor <ChrisTrea...@discussions.microsoft.com> wrote: > This may be a non-issue but I wanted to check to see if anyone has done this > and ran into issues. > &...

OleMainThreadWndName Problem
We have an MFC app. On some PC's running NT4.0 SP6a, we find that although the app appears to have "terminated", in fact it is still invisibly running according TaskManager. We have no problems terminating the apps from TaskManager, no "End Task" recquired. But if we forget to do this, NT 4.0 on shutting done cannot terminate the app and windows pops up saying that for app "OleMainThreadWndName" it cannot shut it down and do we wish to "End Task"? My thoughts 1. I think these are the same thing. Some residue of our app does not terminate. We don...

excel chart x/y variable problem
I am trying to plot variables on my x and y axis and have the field b standard... such as site = 1 2 3 4 5 value 1 = 10 1 6 8 3 value 2 = 2 6 3 2 9 with value 1 being on the x axis, and value 2 being on the y axis an the site # being compared between the 2. I assume you can do it using x y scatter but how??? Any help -- Message posted from http://www.ExcelForum.com If I understand you correctly, plot the value-1 and value-2 in a XY scatter chart. Then, use the site #s as data labels. To simplify the job, use either XY Chartlabeler (www.appspro.com) or Chart Tools (www.j-walk.com)....

CRM 3.0 Install Error with SQL Reporting Services and SQL 2005
Hi, Doing a CRM 3.0 Server installation. During the last page of the install wizard (or what I assume is the last page) when I goes through and does verification I get a red X next to SQL Reporting Services. The message I receive when I look at the details is: ***** The specified path is not a metabase path. Parameter name: path ***** Now SRS is installed and working. I put in the URL: http://<servername>/reportserver and every other variation on that I can find. Thoughts? Alan try http://servername/reports also assuming you did not use ssl... =======================...

Problem with Turkish Excel File
Hi to all, I'm some excel files generated by a report servers. These reports are generated in Turkish language. If I open them by using English Microsoft Excel (Microsoft Office Excel 2007 (12.0.6535.5002) SP1 MSO (12.0.6535.5002)) I can see them properly with table, images and format properties. If I open them by using Turkish Microsoft Excel (TRK Microsoft Office Excel 2007 (12.0.6545.5000) SP2 MSO (12.0.6545.5004)) I'm not able to see them properly..in particular no table is showed nor the images and so on. I found here http://groups.google.com/group/microsoft.public.excel/browse_...

How can i create a query on text input to return closest match?
I would like to create a text based parameter which will return the closest match? e.g Drink is the search citeria, the search will look for the word drink in every record and bring up those with the closest match Can you explain what you mean by closest match? Looking for the word Drink in a field and returning all the records where "drink" is included in the field would be simple. Field: SomeFieldWithText Criteria: LIKE "*" & [Enter word] & "*" But if you wanted to enter DRINK and find Drink or Drank or Drunk or Dink or Rink Or Dunk or .... in...

SQL 'for xml' and C# example??
I'm missing some piece of the puzzle. I'm using 'for xml' type queries from SQL2K - and now trying to change the front-end from ADO to C#, I can't quite figure it out.. It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right. SQL returns formatted XML, I need to load that XML into an XmlDocument somehow without ANY molestation of that dom. Any ideas? Examples? Missing links???? thanks THANKS!! I've been looking for the answer to ...

sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclo...

2006 Password Problems
I have been using MS Money for several years. The last version that I used, and was having no problems with, was MS Money 2005. I used the program on both my desktop and my laptop. I regularly backed up the data to a flash drive and transferred the file back and forth between the 2 machines. Then I upgraded to MS Money 2006 Deluxe. I restored the file from my 2005 version with no problems. I installed the 2006 to my laptop and transferred the backup file with no problems. I took my laptop on a 2 month trip keeping 2006 up-to-date and backing it up to my flash drive. Came home and tried to re...

amend a PO with a query? Please help!
Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...

Update multivalue field in table from form with SQL
Access 2007 on Vista I am using a form to establish the variables for a report before it is created. One field on the underlying table is a multivalue field, and the form includes a multi-select combo box control. After selections are made and the OK button on the multi-select combo drop down is clicked, focus moves to the next control. I have discovered though, that the underlying table does not update immediately, but does so correctly when I close the form. This sequence does not deliver the proper report data, so I need a way to immediately update the underlying table (m...

table problem
I have a table with three columns of data. Column A contains a part number such as "Part XX", or "Part ZZ". Column B has a date in the 01/01/05 format. Column C has a numeric value. There are about 20,000 rows of data. The dates cover three years. For example: PartXX 1/1/05 123 Part XX 3/7/05 456 PartZZ 9/10/07 789 PartXX 1/1/06 159 PartXX 1/1/07 234 Eart part only has one entry for each date during the year. In the fourth column I'll add a column with all 365 days for the year. The date will be in the 1/1, 1/2, 1/3 format. I want to create an output which searche...

Vlookup and IF function problem
Hi, I would like to do a kind of double Vlookup. I have 2 different worksheets with following information: Worksheet1 is the master file with all modells: 3 columns A B C B segment Honda Civic B J segment Honda Civic Coup� D segment Toyota Avensis D ... Worksheet2 is the data file: 3 columns, of which 2 are filled A B C B segment Honda Civic ... J s...

Combo values from query
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the vales of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that stilll leads to the same issues. How can I do this? As example - I have a form w...

limit on new database query?
Hi I am trying to create an olap cube to export to another product, however when I use the new database query to bring the data into excel (from another excel table) I only get 1440 rows. Is there any reason or way to change this limitation? Thanks Doug ...

a language exportation problem
Hello, I have made some customization in an english CRM version then when I tried to export this modification to a french version , it failed. I am asking if there is some modification to the XML File to make it valid during the import from a french CRM. Thanks in advance Ahmed, This is tricky business you try do. But there is a tag language in the xml file english = 1033. Change it to the correct french language tag. HTH Ahmed Maaloul schreef: > Hello, > I have made some customization in an english CRM version then when I tried > to export this modification to a french v...

Problems on shut down or restart
Each time I shut down or restart my computer I keep getting pop up window. The window states Outlook is not responding and asks if I wish to end now. If I click on end now 3 times it will go ahead and shut down. Is anyone else having this problem? Doesn't seem to make any difference if I close Outlook prior to shut down or restart. I do have another email application that may, or may not, be running. Maybe this is the problem. I am seeing the same problem. I close the main Outlook window, but it is still running in my system tray. I can kill it by bringing up the Task Manager and...

Refer to SQL statements globally
I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah...

Excel + Query Tutorial Recommended?
Can anyone recommend a good little online tutorial so that I can get started with manipulating my excel SQL queries. I have managed so far to link up excel to the database and pull out a table, add in some criteria (ie dates etc), applied a sort field and refresh. However I want to understand a bit more about how to pull out two tables and link them together, and this is where (as a beginner) I am getting stuck. I do not need an intense SQL certification course or anything like that, just a tutorial about how to edit and manipulate SQL queries in excel. If anyone can point me in th...