Search DaniWeb - crosstab

CrossTab Query 11 Years Ago by draven07 … help! As you can see, I'm working with a crosstab report that will display the product's "Total Sales… Re: CrossTab Query 11 Years Ago by LastMitch >Now my problem is, I can't figure it out how I can display the monthly total sales or quantity sold for a particular product on a grid. I don't have database. You can take a look at this: http://www.fmsinc.com/tpapers/queries/crosstab.html and this: http://www.dba-sql-server.com/sql_server_tips/t_super_sql_456_crosstab_queries.htm Re: Hyperlink field not working in crosstab query 14 Years Ago by boblarson …QUOTE=isumasama;1537933]hi all, i have created a crosstab query and one of the row heading contains a hyperlink…t believe you can use that directly in a crosstab. Create your crosstab query without that field and then create a …select query which joins the crosstab with the hyperlink field … Hyperlink field not working in crosstab query 14 Years Ago by isumasama hi all, i have created a crosstab query and one of the row heading contains a hyperlink field. my problem is that the field is not working, and on top of that it gives corrupted values too. pls help thanks CROSSTAB query 16 Years Ago by manojbiswas I have a query which gives results like ACTIVITY MONTH1 MONTH2 MONTH3 MONTH4 MONTH5 MONTH6 ... PLAN 1.5 2.1 3.1 1.0 1.2 4.6 ACTUAL 1.2 2.O 2.9 0.7 1.0 2.7 I WANT TO CHANGE THE ORIENTATION OF THE TABLE IN THE FOR AS GIVEN BELOW, MONTH PLAN ACTUAL … Re: CROSSTAB query 16 Years Ago by QVeen72 Hi, Give your Table structure... Regards Veena Re: CROSSTAB query 16 Years Ago by manojbiswas [QUOTE=QVeen72;678273]Hi, Give your Table structure... Regards Veena[/QUOTE] Actually it is a union query which is a rusult of 2-3 queries. This union query gives results like ACTIVITY MONTH1 MONTH2 MONTH3 MONTH4 MONTH5 MONTH6 ... upto MONTH36 PLAN 1.5 2.1 3.1 1.0 1.2… Re: CROSSTAB query 16 Years Ago by QVeen72 Hi, I know.. But Create a Pivot (for Columns) and Decode the Pilot (for Rows) is an OverHead to the Database.. So, if you know table structure.. then writing query is Simple.. Assumin Table Structure to be : MyDate MyPlan MyActual Your query will be: [code=vb] Select Format(MyDate,'mm-yyyy'), Sum(MyPlan), Sum(MyActual) From MyTable Group By … crosstab table and group by row 12 Years Ago by ngocham2001 Hi everyone, I am using PHP and Mysql to make a small inventory program. I have a problem with end_stock field. Detail: I have 2 tables: 1. Receiving: ID_Receive | ProductID | Date | Quantity | Price 1 | A | 01/07/2012 | 5 | 1500 2 | B | 02/07/2012 | 3 | 1100 3 | C | 03/07/2012 | 5 | 920 4 | A | 10/07/2012 | 5 | 1550 5 | C | 01/08/2012 | 4 | 1010 6… Re: crosstab table and group by row 12 Years Ago by dinacristi This is a business problem first. You cannot have an report for both quantityes and prices. On a stock problem you need to calculate the quant first and then evaluate the prices by diferent methods (aquisition, last price average, market etc). Anyway for your report you also need the "result" table from july. You then must join those 3 … Re: crosstab table and group by row 12 Years Ago by ngocham2001 You are so nice and thank you very much. Any help me? Although I read "DaniWeb-Flavored Markdown Syntax" but I still can't post my discuss. Error about syntax of code. I paste my code into my discuss, and then I selected it and select Code button in toolbar. But it isn't complete with error. What did I wrong? Please help me! Thank you … Re: crosstab table and group by row 12 Years Ago by LastMitch **@ngocham2001** Can you show the code? >I am using PHP and Mysql to make a small inventory program. This is more than a small inventory program, it's looks more like a e-commerce question. >Any help me? I can't display and calculator it? You have to show the code in php and mysql in order to help you. Re: crosstab table and group by row 12 Years Ago by ngocham2001 OK, thank you Dinacristi and LastMitch. Fisrt of all, I create a view to calculate start stock : CREATE VIEW stockwithprice AS select receiving.ProductID AS ProductID,ID_receiving, sum(receiving.Quantity) AS Quantity,receiving.Price AS Price from receiving where ((month(receiving.Date) = 7) and (year(receiving.Date) = 2012)) group by … Re: crosstab table and group by row 12 Years Ago by dinacristi you do not need to union those tables you need to join them. Put the group by (product id) in the initial stock, recived, delivered views. without the reciving_id(you can put price but will mess up things). You will get somethig like: Initial stock: A|9 B|2 Recived: A|3 B|0 Deliverd: A|2 B|1 Then (left)join those tables by prduct id. You can … Re: crosstab table and group by row 12 Years Ago by ngocham2001 Thank you, But your result is missing prices. That is very important to calcualator amount... Re: crosstab table and group by row 12 Years Ago by dinacristi http://accountinginfo.com/study/inventory/inventory-121.htm you must first decide witch metod of inventory valuation you use LIFO/FIFO/MA etc, then you can easy calculate the value. for the Weighted Average Cost (http://en.wikipedia.org/wiki/Weighted_average_cost#Weighted_Average_Cost) you can just ad a calculated column in the views "… Re: Hyperlink field not working in crosstab query 14 Years Ago by isumasama wow, u're right. it works now, thanks Crystal report help needed 16 Years Ago by bluerain25 … with crystal report? im doing a report lke this in crosstab. The report whould be able to accept Date start and… countif in mysql 15 Years Ago by diafol … seem to work. Is what I'm looking for a crosstab query?? Excel is easy with the COUNTIF() function. How the… Arranging Data in Columns--MySQL 15 Years Ago by visualmonk …, etc. I was thinking that I needed to construct a crosstab query, but that type of query seems to be designed… Pivoting a Datatable 13 Years Ago by lcfjoertoft … modifying it to do what I want. [CODE] Public Function CrossTab(ByVal dtS As DataTable, ByVal leftColumn As String, ByVal topField… Pivot variable not detected..? 12 Years Ago by PinoyDev …-30],[2012-05-31],[2012-06-30],[2012-07-27])) CrossTab ORDER BY Level Whats wrong with my pivot? Where should… Re: Pivot variable not detected..? 12 Years Ago by BitBlt …-30],[2012-05-31],[2012-06-30],[2012-07-27])) CrossTab That should do the trick. I tested this on SQL2008… Crazy CrossTab problems 16 Years Ago by muek Hi there, I'm completely lost. I have 3 tables: - work (workID, description) - machine (machineID, name) - work_machine (machineID, workID, hours) What I want is to obtain a result similar to: work, machine1, machine2 A , 1 , B , 3 , 5 The problem is that the number of works and the number of machines is unknown. Can anyone … Re: Crazy CrossTab problems 16 Years Ago by kanaku I think what you can do on your work_machine table is have references to the workid and machineid... work_machineID | workID | machineID 1 | A | 1 2 | B | 3 3 | B | 5 Then just select from this table where workID = 'A'; Re: Crazy CrossTab problems 16 Years Ago by Ramy Mahrous Muke, seems you didn't normalize your database design!! Re: Crazy CrossTab problems 16 Years Ago by muek Ok, I think that I haven't explain myself. In table "work_machine" I have: workID | machineID | hours A | M1 | 1 B | M2 | 3 B | M1 | 5 Now I would like to know how to make the query to obtain the data in the format: work | machine1 | machine2 A | 1 | 0 B | 3 | 5 Any idea? Re: Crazy CrossTab problems 16 Years Ago by Ramy Mahrous What's machine1 and machine2 ?? and what '0' in the first row in output? Re: Crazy CrossTab problems 16 Years Ago by muek Hi, machine1 and machine2 are rows from machine table The 0 represents that machine2 don't do any "A" work [QUOTE=RamyMahrous;767279]What's machine1 and machine2 ?? and what '0' in the first row in output?[/QUOTE] Re: Crazy CrossTab problems 16 Years Ago by Ramy Mahrous As I said, you've lack in your design, I am sorry but your database design is wrong. It need some normalization