A rather basic question I am sure...

Hi,

Firstly I am not a SQL person.. so I am sure that this question is rather 
basic.. for that I am sorry..

I have been given the task to create a few reports, and with this I need to 
group some sales data.

The table looks like :

transID     OrderID          Orderdate                        sku       qty
1                 1234            1/1/2010  12:12am          ABC      3
2                 1234            2/1/2010    8:19pm          DEF       5
3                 5678             1/1/2010   2:10pm          ABC      3
4                 5678             1/1/2010   2:10pm          AFE       3
5                 5678             1/1/2010   2:10pm          FEW      3
6                 5678             1/1/2010   2:10pm          DEF       3

and so on.. I have just made up the data in this post, so if the date 
structure is not perfect sorry, but its a datetime, so has both the date and 
time part.

So I want to have three reports. The first shows the totaly qty of an item 
sold for each day (for as much data as the table holds), the second is the 
same report, but shows the values by month.

I have figured it so far to be Select sum(qty), sku, Orderdate from 
t_transaction group by sku, orderdate,qty...

That seems to get back the data, but the date bit is including the time... 
and I just want them grouped by day (or month)... how can I do this...


Also the third report, will just say what are the most common other items 
orders when this item is ordered...

So if I look at SKU item ABC, the item DEF would be at the top of this 
result, as DEF was also ordered on both orders that ABC was. I would also 
get AFE and FEW but lower in the list as they are only on one order..

This one I have no idea how to go about...

Thanks for you help on this...

Let my learning begin...




0
AussieRules
6/20/2010 9:09:54 AM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
628 Views

Similar Articles

[PageSpeed] 53

AussieRules (nospam@nospam.com) writes:
> Firstly I am not a SQL person.. so I am sure that this question is rather 
> basic.. for that I am sorry..
> 
> I have been given the task to create a few reports, and with this I need
> to group some sales data. 

And you accepted, despite that you have sufficient training?

> 
> So I want to have three reports. The first shows the totaly qty of an item 
> sold for each day (for as much data as the table holds), the second is the 
> same report, but shows the values by month.
> 
> I have figured it so far to be Select sum(qty), sku, Orderdate from 
> t_transaction group by sku, orderdate,qty...
> 
> That seems to get back the data, but the date bit is including the time... 
> and I just want them grouped by day (or month)... how can I do this...

Per day use 

     convert(char(8), Orderdate, 112)

To get per month, change char(8) to char(6)

> Also the third report, will just say what are the most common other items 
> orders when this item is ordered...
> 
> So if I look at SKU item ABC, the item DEF would be at the top of this 
> result, as DEF was also ordered on both orders that ABC was. I would also 
> get AFE and FEW but lower in the list as they are only on one order..
 
Something like:

   SELECT TOP 5 sku
   FROM   (SELECT a.sku, COUNT(*) AS a.cnt
           FROM   orderdetails a
           WHERE  a.item <> @item
             AND  EXISTS (SELECT *
                          FROM   orderdetails b
                          WHERE  a.OrderID = b.OrderID
                            AND  a.item = @item)) AS x
  ORDER BY cnt DESC

Since you did not post CREATE TABLE statement for you table and INSERT
statements with the sample data, I did not care to do this myself, and
the above is not tested or checked for correct syntax. 
  

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/20/2010 10:00:42 AM
On Sun, 20 Jun 2010 19:09:54 +1000, "AussieRules" <nospam@nospam.com>
wrote:

>Hi,
>
>Firstly I am not a SQL person.. so I am sure that this question is rather 
>basic.. for that I am sorry..
>
>I have been given the task to create a few reports, and with this I need to 
>group some sales data.
>
>The table looks like :
>
>transID     OrderID          Orderdate                        sku       qty
>1                 1234            1/1/2010  12:12am          ABC      3
>2                 1234            2/1/2010    8:19pm          DEF       5
>3                 5678             1/1/2010   2:10pm          ABC      3
>4                 5678             1/1/2010   2:10pm          AFE       3
>5                 5678             1/1/2010   2:10pm          FEW      3
>6                 5678             1/1/2010   2:10pm          DEF       3
>
>and so on.. I have just made up the data in this post, so if the date 
>structure is not perfect sorry, but its a datetime, so has both the date and 
>time part.
>
>So I want to have three reports. The first shows the totaly qty of an item 
>sold for each day (for as much data as the table holds), the second is the 
>same report, but shows the values by month.
>
>I have figured it so far to be Select sum(qty), sku, Orderdate from 
>t_transaction group by sku, orderdate,qty...
>
>That seems to get back the data, but the date bit is including the time... 
>and I just want them grouped by day (or month)... how can I do this...
>
>
>Also the third report, will just say what are the most common other items 
>orders when this item is ordered...
>
>So if I look at SKU item ABC, the item DEF would be at the top of this 
>result, as DEF was also ordered on both orders that ABC was. I would also 
>get AFE and FEW but lower in the list as they are only on one order..
>
>This one I have no idea how to go about...
>
>Thanks for you help on this...
>
>Let my learning begin...
>

Hi

When you want to post examples then you should say which version of
SQL Server you are using along with the DDL for the table definitions
and the example data as insert statements see
http://www.aspfaq.com/etiquette.asp?id=5006. This makes it much easier
to understand you problem and test possible solutions.

You have the same ORDERID with different dates, which doesn't make
immediate sense.

If you are using SQL 2008 there is a DATE datatype which you can CAST
the datetime to.

If you are working on version prior to 2008 you can do the following
to remove the time portion CAST(FLOOR(CAST(orderdate AS Float)) 

E.g.


CREATE TABLE #orders ( id int not null identity, orderdate datetime ,
qty int not null )

INSERT INTO #orders ( orderdate, qty )
SELECT '20100101 09:01:20', 13
UNION ALL SELECT '20100102 10:00:00', 42
UNION ALL SELECT '20100103 10:03:00', 12
UNION ALL SELECT '20100103 10:05:00', 32
UNION ALL SELECT '20100104 09:10:00', 13
UNION ALL SELECT '20100104 10:30:00', 14

SELECT CAST(orderdate AS DATE) AS [OrderDate], SUM(qty) AS [TotalQty]
FROM #orders
GROUP BY CAST(orderdate AS DATE)

SELECT CAST(FLOOR(CAST(orderdate AS Float)) AS DATETIME) AS
[OrderDate], SUM(qty) AS [TotalQty]
FROM #orders
GROUP BY CAST(FLOOR(CAST(orderdate AS Float)) AS DATETIME)


John
0
John
6/20/2010 10:50:18 AM
Hi,
Thanks for your help.. Yes I took it on, but because I figured I could 
learn... no hard having a go..

The select now works, and the date string is returned as the value 
'20100614'. I assume that this 14/6/2010 (uk format)..

Is there a way to convert this char, into a date format, so that the client 
VB.net application can work with date datatypes ? (I am binding a chart 
control to my dataset, so have to have the values returned from the SQL 
server as a date as I can't convert in the client app)

Also sorry for not posting the SQL on the last request.

I modified the code to suit my db, and ended up with  :

 SELECT TOP 5 sku
   FROM   (SELECT a.sku, COUNT(*) AS cnt
           FROM   tbl_pick a
           WHERE  a.sku <> '8845'
             AND  EXISTS (SELECT *
                          FROM   tbl_pick b
                          WHERE  a.job_id = b.job_id
                            AND  a.sku = '8845')) AS x
  ORDER BY cnt DESC

However I get the following:

Msg 8120, Level 16, State 1, Line 3
Column 'tbl_pick.sku' is invalid in the select list because it is not 
contained in either an aggregate function or the GROUP BY clause.

I can see that the error comes from the select statement that has the 
count(*) code, but not sure where to put the group by clause....

Again, thanks heaps for your help on this..





"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D9D7A3112011Yazorman@127.0.0.1...
> AussieRules (nospam@nospam.com) writes:
>> Firstly I am not a SQL person.. so I am sure that this question is rather
>> basic.. for that I am sorry..
>>
>> I have been given the task to create a few reports, and with this I need
>> to group some sales data.
>
> And you accepted, despite that you have sufficient training?
>
>>
>> So I want to have three reports. The first shows the totaly qty of an 
>> item
>> sold for each day (for as much data as the table holds), the second is 
>> the
>> same report, but shows the values by month.
>>
>> I have figured it so far to be Select sum(qty), sku, Orderdate from
>> t_transaction group by sku, orderdate,qty...
>>
>> That seems to get back the data, but the date bit is including the 
>> time...
>> and I just want them grouped by day (or month)... how can I do this...
>
> Per day use
>
>     convert(char(8), Orderdate, 112)
>
> To get per month, change char(8) to char(6)
>
>> Also the third report, will just say what are the most common other items
>> orders when this item is ordered...
>>
>> So if I look at SKU item ABC, the item DEF would be at the top of this
>> result, as DEF was also ordered on both orders that ABC was. I would also
>> get AFE and FEW but lower in the list as they are only on one order..
>
> Something like:
>
>   SELECT TOP 5 sku
>   FROM   (SELECT a.sku, COUNT(*) AS a.cnt
>           FROM   orderdetails a
>           WHERE  a.item <> @item
>             AND  EXISTS (SELECT *
>                          FROM   orderdetails b
>                          WHERE  a.OrderID = b.OrderID
>                            AND  a.item = @item)) AS x
>  ORDER BY cnt DESC
>
> Since you did not post CREATE TABLE statement for you table and INSERT
> statements with the sample data, I did not care to do this myself, and
> the above is not tested or checked for correct syntax.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 

0
AussieRules
6/20/2010 11:04:49 AM
Got the first bit sorted out...

I just converted it back to datetime...

 convert(date,(convert(char(8), created, 112)))

I assume thats ok logic to do it that way ?

Thanks



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D9D7A3112011Yazorman@127.0.0.1...
> AussieRules (nospam@nospam.com) writes:
>> Firstly I am not a SQL person.. so I am sure that this question is rather
>> basic.. for that I am sorry..
>>
>> I have been given the task to create a few reports, and with this I need
>> to group some sales data.
>
> And you accepted, despite that you have sufficient training?
>
>>
>> So I want to have three reports. The first shows the totaly qty of an 
>> item
>> sold for each day (for as much data as the table holds), the second is 
>> the
>> same report, but shows the values by month.
>>
>> I have figured it so far to be Select sum(qty), sku, Orderdate from
>> t_transaction group by sku, orderdate,qty...
>>
>> That seems to get back the data, but the date bit is including the 
>> time...
>> and I just want them grouped by day (or month)... how can I do this...
>
> Per day use
>
>     convert(char(8), Orderdate, 112)
>
> To get per month, change char(8) to char(6)
>
>> Also the third report, will just say what are the most common other items
>> orders when this item is ordered...
>>
>> So if I look at SKU item ABC, the item DEF would be at the top of this
>> result, as DEF was also ordered on both orders that ABC was. I would also
>> get AFE and FEW but lower in the list as they are only on one order..
>
> Something like:
>
>   SELECT TOP 5 sku
>   FROM   (SELECT a.sku, COUNT(*) AS a.cnt
>           FROM   orderdetails a
>           WHERE  a.item <> @item
>             AND  EXISTS (SELECT *
>                          FROM   orderdetails b
>                          WHERE  a.OrderID = b.OrderID
>                            AND  a.item = @item)) AS x
>  ORDER BY cnt DESC
>
> Since you did not post CREATE TABLE statement for you table and INSERT
> statements with the sample data, I did not care to do this myself, and
> the above is not tested or checked for correct syntax.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 

0
AussieRules
6/20/2010 11:08:18 AM
AussieRules (nospam@nospam.com) writes:
> Thanks for your help.. Yes I took it on, but because I figured I could 
> learn... no hard having a go..
> 
> The select now works, and the date string is returned as the value 
> '20100614'. I assume that this 14/6/2010 (uk format)..

That is indeed 2010-06-14, yes.
 
> Is there a way to convert this char, into a date format, 

I see that you have already found the convert function.

> I modified the code to suit my db, and ended up with  :
> 
>  SELECT TOP 5 sku
>    FROM   (SELECT a.sku, COUNT(*) AS cnt
>            FROM   tbl_pick a
>            WHERE  a.sku <> '8845'
>              AND  EXISTS (SELECT *
>                           FROM   tbl_pick b
>                           WHERE  a.job_id = b.job_id
>                             AND  a.sku = '8845')) AS x
>   ORDER BY cnt DESC
> 
> However I get the following:
> 
> Msg 8120, Level 16, State 1, Line 3
> Column 'tbl_pick.sku' is invalid in the select list because it is not 
> contained in either an aggregate function or the GROUP BY clause.

As I said, it was not tested for correctness. But as the error message
said, I happened to forget the GROUP BY clause.

> I can see that the error comes from the select statement that has the 
> count(*) code, but not sure where to put the group by clause....
 
Well, what possible places are there, and which make sense? :-)


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/20/2010 1:31:10 PM
Reply:

Similar Artilces:

Sumif Question
I need help with a sumif formula. I have two spreadsheets. On my report spreadsheet I want to use a sumif function that looks at two columns in the other spreadsheet and give me a total based on the following criteria. 1. Look at the date in F2 and compare to the date in C2. Count if the date in F2 is greater than C2. OR 2. If F2 is blank, and today's date is greater than C2, count. Do you mean =SUMPRODUCT(--((F2:F20>C2:C20)+((F2:F20="")*(TODAY()>C2:C20)))) -- HTH Bob "stevestr" <stevestr@discussions.microsoft.com...

POP3 Question #4
Hi, I have SBS 2003 wiuth Exchange 2003 where I am downloading my email into a global mailbox. Is there a way i can view a log or status to see if mail is being downloaded? Thanks ...

Another MDI or SDI question
I would like to convert my VB app to VC++, but I am unsure as to which framework to use. The application will interface to a hardware device to monitor, record and analyze data. There will only be one set of data, but I would like to view it in various formats (spreadsheet, graph, etc.). Because of the multiple views, am I correct to assume I should choose MDI? Any advice would be appreciated. This sounds definitely like MDI. I have built, in all my apps, two SDI apps, and regretted both of them (I had to rewrite them as MDI). The rest of my apps are dialog apps, and one of those uses tabbed ...

Bubble chart question #2
I suspect not many of you are using bubble charts, but I am trying to verify whether I have a problem or if Excel does. Excel 2007 bubble chart using the following data: X Y Size -16 -4 5 -13 -15 2 -8 0 3.5 -4 -19 3 -2 -10 4 0 -4 3 0 14 3.5 8 -19 4 9 5 2 11.5 -5 4 The X-axis data does not plot the negative values correctly, they are all on the positive side. It appears that this feature preforms the same way in 2003. I realize that I can plot this using other chart types, but.... Any comments would be appreciated. -- If this helps, please click the Yes button. Cheers, Shane Deve...

Threading question
Is there a way to protect a section of code so that all the statements in the protected block are executed without being interrupted by another thread? I have a worker thread that adds a value to an array (a circular buffer) then increments the array index. If the user clicks on a button of a form in the main thread, sometimes the main thread code will then access the array after the value has been added, but before the array index has been updated. I know I've seen ways to protect code in other languages (Pascal or Ada I think) but how can this be done in vb.net? t...

H E L P * * * Need help with question about protecting a range of cell in workbook
I need to know how to protect a range of cell/s in a workbook ...

C Sharp Basics
To know more about C Sharp Basics with Examples in very easy way then please visit: http://csharpexpress.blogspot.com/ ...

Outlook 2007 Calendar question.
I want to set up a rule when a meeting request comes in it will automatically color code it to a color that I choose if there is writing in the message part of the meeting request. This would be helpful if someone has typed more information in the meeting request other then the subject. I’ve looked under automatic formatting and don’t see anything under the advanced section. That would allow me to do this. ...

New OE newsgroup user questions
I'm new to newsgroups and access via Outlook Express. Where can if find documentation or descriptions on how to really use OE effectively w/ forums. My main question right now is: How do I reply to a reply that someone posted to my new post? If I click Reply Group in OE I get a msg "Do you want to reply to the entire newsgroup. Is this the correct options to add another level of input to my original post, or will this go to every post in the newsgroup? I would also like to know how to do things like: Searching forums for specific words or phrases? Can I se...

eConnect Licensing question
Hello, I am helping a company that wants to generate journal entries to Dyanmics GP. From searching online I believe that this would be done using eConnect. My questions are: 1. Is this the correct way to communicate from an application to GP? 2. How is eConnect licensed? 3. Where can one download documentation on how and when to use eConnect? Thanks, Direct replies to alg@nomscon.com would be fine as well eConnect is a way. Integration Manager is another way. eConnect, by itself, requires some programming because it's a set of API's. There are some add-on products, like Sc...

Team/Business Unit Question
Hi, I'm trying to work out the best way to configure CRM 3 for our organisation (we provide support services to doctors) to replace an existing database. This existing database had a contacts section for details of all our professional members, linked to their organisations they belonged to. This database though also kept a separate 'module' for contact and service info that a select group of our staff could access for a confidential health service we also provide. At the moment in CRM we have one business unit for our organisation and at the moment all our crm users can se...

OWA Permissions Question with A.D.
Hi everyone Here is my dilemma. Last year we added a group to our AD to block the "Department" field in our A.D. As long as users are in this group they can not read the "Department" field. Even when using outlook, the "Department field is there, but the content is blank due to the permissions. Recently we discovered the using the OWA for exchange 2003 we could read the contents of the "Department" Field. I know that OWA/Exchange pulls data from the A.D> but i can not figure out why OWA bypasses this permission. Does anyone know what why OWA is bypa...

go to page question
How do I move around in a document--namely, how do I go to page x or to page first or page last? Press Ctrl+G. -- Herb Tyson MS MVP Author of the Word 2007 Bible Blog: http://word2007bible.herbtyson.com Web: http://www.herbtyson.com "richard" <rmk@wonderland.net> wrote in message news:ehetEr2fKHA.5608@TK2MSFTNGP05.phx.gbl... > How do I move around in a document--namely, how do I go to page x or to > page first or page last? On 12/17/2009 6:12 PM, Herb Tyson [MVP] wrote: > Press Ctrl+G. > Thank you. But I wonder why I could not find th...

Need help on OU question
Windows 2003 Domain Controller Standard Ed SP2 Installed SQL 2005 SP3 The installed Sharepoint 3 sp2 During install it created a sharepoint OU But now I can not find it any where My question is how do I display the OU that are on my server I seem to be brain dead and cannot find them anywhere Using AD sites and Services AD Users and Computers AD Directory Domains and Trusts Not sure where else to look Any ideas or suggestions would be greatly apprciated Thanks Tom Hello Thomas, Any chance that you removed the Advanced view option in AD UC? ...

In outlook
I only have "Inbox, Junk mail & Deleted items" left. Any idea where the rest went? -- Nomad ...

Formula question -simple for those who know
Hello I'm trying to add a number that was calculated by means of a formula in one column to the sum of a different column. Example: Column A (running balance) Column B (sum) =+e5+c6-d6 =sum(h1:h8) Col A's number is running balance. I copied the formula down so that the next row changes the formula to =+e6=c7-d7 I want that running balance to add to a sum in another column. I can add the first cell to it, but it doesn't change if the running balance changes. I know that is because it's pointing to that single...

Beginner's question
Hi Friends, Could you please tell me which is a god site where I can start learning about exchange server? I am a newbie so please tell me a beginner's site for Exchange server Waiting for your reply. Thanks in advance, I would recommend starting at www.microsoft.com/exchange and you will find some great info there. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Emmanuel Mathew" <emmanuel@aitstech.com> wrote in message news:0b3e01c3d52e$e2287520$a401280a@phx.gbl... > Hi Friends, > > Could you please tell me which is a go...

New OWA Question
We have two servers, old 2000 Exchange and new 2003 Exchange on Small Business Server. We used to access OWA externally by pointing the router to direct traffic on port 80 to the old server. Since we’ve migrated to the new 2003 Exchange server we cannot get OWA to work from the outside. It works fine from the inside, but if we direct the router to aim port 80 traffic to the new server, it just comes back with page not found. Still works fine using OWA internally on the new server Just cannot get to it from outside. My question is: is there a port different than 80 we need to direct to t...

Export to XML question
Hi All, The below is a test code to export xml file. The fields Comment and Tip hold xHTML code. The problem occurs if I add the Tip field then I get the following error: The XML page cannot be displayed Required white space was missing. Error processing resource 'file:///C:/1.xml'. Line 1, Position 646 It seems that XM file cannot fully exported and some text is missing. I apprecaite any input. DECLARE @SQL VARCHAR(1024) DECLARE @sqlFlds1 VARCHAR(225) DECLARE @sqlFlds2 VARCHAR(225) DECLARE @sqlFlds3 VARCHAR(225) DECLARE @sqlFlds4 VARCHAR(225) DECLARE @sqlSt...

Data Validation Question #6
Jaun, Thank you. It worked. Just curious, is it a "bug" in Excel or somethin I did? Case -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=454 View this thread: http://www.excelforum.com/showthread.php?threadid=26764 I think it's "by design". It happens when you manually move that message to some other place. Then, all the messages will appear there ! -- Regards Juan Pablo Gonz�lez "Casey" <Casey.1dtg7y@excelforum-nospam.com> wrote...

Question about Application.ScreenUpdating = False
Dear all In my excel file there are two sheets: A and B B is very hidden in my vba code I am in sheet A and I need to insert a value in Sheet B then: Application.ScreenUpdating =3D False Sheets("B").Visible =3D xlSheetVisible .. .. .. Sheets("B").Visible =3D xlSheetVeryHidden Application.ScreenUpdating =3D True But I can see the macro running (for one second I can see the sheet B visible...) How can I solve this problem? Thanks! Andr=E9. Why does it have to be visible to start with? Something in your code that does UN necessary selections? -- Don Guillett Micr...

Exchange IMF questions
Exchange 2003 SP2. IMF configured and working OK but I have some questions: 1) Is there any way to make IMF learn that certain emails are junk and certain ones are not? 2) How is the IMF filtering updated? 3) Is there a way to make IMF not check emails from certain "good" domains? 4) Does the IMF feature to deliver suspect emails into the users "Junk E-Mail" folder (rather than the Inbox) work with older Outlook 2000/2002 versions? I created the Junk E-Mail folder for those users by logging into their OWA site but they never seem to receive "suspect" ema...

Access\Visual Basic help
Hello, Can someone help me generate some VB code to do the following... I have a text box that displays a projects status, but the status is displayed as a number and the number means something like " Completed" or "In Development". Is there a way that i can have some VB code look and see that if there is an 8, then display "Completed" or if there is a 10 it displacy "In development"? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200707/1 Use an unbound text control. Are those the ONLY choices? ...

Denominations Question
Hi, I have a sheet with amounts to be paid to employees. The employees need to be paid in cash every week. These amounts normally don't exceed $1000. However, it is a nightmare if the exact denominations are not withdrawn from the bank. Is there some way I can determine the number of currency notes to be withdrawn in denominations of 1000, 500, 200, 100, 50, 20, 10, 5 & 1's? Can I do this in excel? Thanks and regards, neil Neil, Assuming the amounts to be paid are in column D starting D2 down: Populate cells E1 through M1 with the denomitations 1000, 500, ...., 1 Put th...

I have question.. I have problem with Cells.find
I have two file (p1.xls , p2,.xls). When I work in file (p2.xls) i need to find something in second file(p1.xls). Then write value from Cecha in the ActiveCell of p2.xls, provided that Cecha is found I have button and code: but this code does not work how I want !! because search in file p2.... (it work on inversely) What I should modify ?? that it search in p1.xls ??? Sub CommandButton3_Click() '------>in file p2.xls Dim szukana As Range Dim Cecha as String Cecha = InputBox("Enter the name", "Enter value") If Cecha = "" Then Exit Sub Workbooks.O...