Have 2013 results at the top...

[hardy1976]

Hi,

I have a list of 2012 payments eg..
Company 1 - £200
Company 2 - £1200
Company 3 - £2300
Company 4 - £500

Who have started to have 2013 payments..eg
Company 4 - £1000
Company 2 - £10


What I need is a query (for a report) that will display the companies that have had 2013 payments at the top of the report alongside their 2012 payments... eg

Company 2 - £10 - 2013
Company 2 - £1200 -2012
Company 4 - £1000 - 2013
Company 4 - £500 - 2012
Company 1 - £200
Company 3 - £2300

How do I go about doing this?? The main question is how do I get the company with 2013 payments to the top of the query?

 

[pr2-eugin]

Hello Hardy1976, have you tried using ORDER BY clause?

 

[hardy1976]

Yes - but I need both the old and new data at the top.

 

[pr2-eugin]

Orderby is just a Sort order that you specify on the column.. If you need to combine the company's as a group then use GROUP BY to get the desired field on top use ORDER BY.. Look into GROUP BY & ORDER BY.

 

[hardy1976]

hmm what am I doing wrong...

SELECT [Adhoc - BL].[COMPANY CODE], [Adhoc - BL].[COMPANY NAME], [Adhoc - BL].Amount, [Adhoc - BL].[DATE PAYABLE]
FROM [Adhoc - BL]
GROUP BY [Adhoc - BL].[COMPANY CODE], [Adhoc - BL].[COMPANY NAME], [Adhoc - BL].Amount, [Adhoc - BL].[DATE PAYABLE]
ORDER BY [Adhoc - BL].[DATE PAYABLE] DESC;

COMPANY CODE COMPANY NAME Amount DATE PAYABLE
4 Company 4 100 08-Feb-13
2 Company 2 200 01-Feb-13
1 Company 1 300 16-Jan-12
3 Company 3 400 02-Jan-13
4 Company 4 500 19-Dec-12
2 Company 2 600 07-Dec-12
3 Company 3 700 27-Nov-12

I want it to appear as....

COMPANY ID COMPANY NAME Amount DATE PAYABLE
4 Company 4 100 08-Feb-13
4 Company 4 500 19-Dec-12
2 Company 2 200 01-Feb-13
2 Company 2 600 07-Dec-12
3 Company 3 400 02-Jan-13
3 Company 3 700 27-Nov-12
1 Company 1 300 16-Jan-12

 

[hardy1976]

Anyone got any ideas??

 

[recyan]

what happens when you do ?

SELECT 
	[Adhoc-BL].COMPANYCODE, 
	[Adhoc-BL].DATEPAYABLE, 
	[Adhoc-BL].COMPANYNAME, 
	[Adhoc-BL].Amount
FROM 
	[Adhoc-BL]
ORDER BY 
	[Adhoc-BL].COMPANYCODE DESC , 
	[Adhoc-BL].DATEPAYABLE DESC;

Thanks

 

[hardy1976]

Hi - That puts everything into Company order... and then date payable, ie the initial sort is on Company... A to Z then dates... so the data will appear as

COMPANY ID COMPANY NAME Amount DATE PAYABLE
1 Company 1 300 16-Jan-12
2 Company 2 600 07-Dec-12
2 Company 2 200 01-Feb-13
3 Company 3 700 27-Nov-12
3 Company 3 400 02-Jan-13
4 Company 4 500 19-Dec-12
4 Company 4 100 08-Feb-13

What I have done is create a query which checks to see if there is a 2013 payment... In the main query I have added a dlookup to that table which forces it into the top part of the report.... Not the cleanest way of doing it... however I needed to find a solution.

I look forward to now being told how I should have done it

 

[recyan]

Am a bit puzzled, as I had expected the result of the query as below :

COMPANYCODE	DATEPAYABLE	COMPANYNAME	Amount
4	2/8/2013	Company 4	100
4	12/19/2012	Company 4	500
3	1/2/2013	Company 3	400
3	11/27/2012	Company 3	700
2	2/1/2013	Company 2	200
2	12/7/2012	Company 2	600
1	1/16/2012	Company 1	300

Thanks