ACCESS FEED: Re: [MS_AccessPros] Vertical data in Horizontal form

Abizer,

Sample db named Query_CrossTabCollapsedCumSplitCols.zip, demonstrating the suggested approach, has been uploaded to Samples folder in the Files section of this group. It makes use of the specimen data provided by you.

With a large data set, use of DCount() function (or a subquery, which, in any case, stands ruled out as mentioned in my earlier post) for generating sequential ranking numbers, is found to be slow. For optimum performance, an update query, using increments to global variable, has been used. For this purpose, a field named Rank has been added to the source table T_Sales.

On the start up form F_Sales, the user can select the desired date via combo box. Clicking the command button captioned "<< Show Result >>" leads to following chain of actions:
(a) Run update query Q_2_UpDtSalesRank It makes use of select query Q_1_SalesSorted (filtered as per selected date and sorted as per SP_ID and ItemCode).
(b) Open crosstab query Q_4_SalesCrosstab_Final, displaying the final results. It makes use of union query Q_3_SalesUnion, which in turn, is based upon union between two instances of Q_1_SalesSorted.

Note:
(a) Update query Q_2_UpDtSalesRank has form based parameters. Form F_Sales must be in open state, while attempting to run this query.
(b) For action queries using form based parameters, CurrentDb.Execute method leads to error. DoCmd.OpenQuery method needs to be used.
(c) Your source table seems to be lacking a primary key. This has been inserted (autonumber type).
(d) Your field holding dates is found to be of text type. Date values are in some absurd range. This field has been converted to date type and the values have been modified to an arbitrary realistic range.

You might like to try out the above sample (in the first stage, as it is, without yet attempting any further adaptation) and confirm whether the results are in line with those sought by you. On hearing from you, the sample file (large in size) shall be deleted so as to free up disk space.

Best wishes,
A.D. Tejpal
------------

----- Original Message -----
From: Abizer Hussain
To: MS_Access_Professionals@yahoogroups.com
Sent: Saturday, July 09, 2011 19:45
Subject: Re: [MS_AccessPros] Vertical data in Horizontal form

Hi Mr Tejpal
How are you hope in the best of health..
What you have explained is exactly what i want so thank you very very much for that..
Im very new to microsoft Access so don know how exactly to implement those things..
I have uploaded a file in assistance needed area, named "Abizer Samaple.accdb" if you can take your precious time n let me know how to make that as i have been trying since your last mail but don know where n what to do..
Im n will be grateful for you help

Thank you
Abizer

________________________________
From: A.D. Tejpal <adtp@airtelmail.in>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, July 6, 2011 2:22 PM
Subject: Re: [MS_AccessPros] Vertical data in Horizontal form

Abizer,

If the number of different distinct items sold by one sales person on a single date is not likely to be very large, even though combined total count of distinct items sold per day by all sales persons is very large (say thousands), an alternative solution, based upon crosstab query could be considered.

In your scenario, a routine crosstab query, using ItemCode field as the column head would generate an unacceptably huge number of columns (one column per ItemCode). In such a bloated query, it would be observed that against each row, a vast number of columns are blank. This is because even though the sales person for the row in question might have sold only 5 items, and thus needs only 5 columns of meaningful data, the said row has to carry all the other columns (though blank) so as to make up the overall total by all sales persons.

The solution lies in collapsing the extraneous blank columns and there is an interesting way to do it. It involves generation of ItemCode ranks per sales person per day and using the same for column heads in the final crosstab query. This stage is demonstrated via sample query Q_1_Sales given below (at the end of this post).

With this approach, the final crosstab query will need a split column arrangement. In each set, first sub-column will display the ItemCode while second sub-column shows the quantity sold. Conventional crosstab query has the limitation that only one field can be nominated as value field. This hurdle can be overcome by making a union of query Q_1_Sales with itself as demonstrated in sample query Q_2_SalesUnion given below.

Based upon the above, final crosstab query Q_3_SalesCrosstab_Final as given below, should get you the desired output in following style:

SDate SP_ID C01A C01B C02A C02B C03A C03B (-- and so on).
Item Qty Item Qty Item Qty
12-Jun-2011 300123 240 23 280 52 350 11

With the above arrangement, if 250 different items have been sold overall, but maximum distinct sold per sales person is 5, the potential 250 columns get compressed to just 10 columns, facilitating convenient presentation.

Note:
(a) Table T_Sales has fields SP_ID, SDate (date type), ItemCode, and Quantity. All fields other than SDate are presumed to be of number type.
(b) While working out the rankings, DCount() has been used instead of subquery as the latter is not found acceptable when the query in question has to feed a crosstab query.

Best wishes,
A.D. Tejpal
------------

Q_1_Sales
======================================
SELECT T_Sales.*, DCount("*","T_Sales","SP_ID = " & [SP_ID] & " AND SDate = #" & Format([SDate],"mm/dd/yyyy") & "# AND ItemCode <= " & [ItemCode]) AS Rank
FROM T_Sales;
======================================

Q_2_SalesUnion
======================================
SELECT "A" AS Part, * FROM Q_1_Sales
UNION ALL SELECT "B" AS Part, * FROM Q_1_Sales;
======================================

Q_3_SalesCrosstab_Final
======================================
TRANSFORM First(IIf([Part]="A",[ItemCode],[Quantity])) AS Expr2
SELECT Q_2_SalesUnion.SDate, Q_2_SalesUnion.SP_ID
FROM Q_2_SalesUnion
GROUP BY Q_2_SalesUnion.SDate, Q_2_SalesUnion.SP_ID
PIVOT "C" & Format([Rank],"00") & [Part] & "_" & IIf([Part]="A","Item","Qty");
======================================

----- Original Message -----
From: Abizer Hussain
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, July 05, 2011 23:47
Subject: Re: [MS_AccessPros] Vertical data in Horizontal form

Thanks Duane
I ll do that sub report thing n see if the desired result comes or may be i get better than what i have planned for.

Abizer

________________________________
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Tuesday, July 5, 2011 5:20 PM
Subject: RE: [MS_AccessPros] Vertical data in Horizontal form

Since there is a possibility of 30 to 3000 items, a crosstab is not a viable solution.

From looking at your desired layout from a private email, I would again suggest you create a main report with a record source grouped by person and date. There should be no items in this main report. Then create a multicolumn subreport of the items and quantities that displays across then down. Add the subreport to the detail section of the main report.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: mmabizer@yahoo.com
Date: Tue, 5 Jul 2011 04:28:06 -0700
Subject: Re: [MS_AccessPros] Vertical data in Horizontal form

Hi
Actually i didn't get you..
well i have just mentioned 30 items they can exceed to 300 to 3000 either.

and crosstabs shows all items in 1 date.. i just want those item which has been sold in specific date.. may be 1 or 2 or more..
I have also sent u a provate mail with pictures if that can clear what i want to say..

Thanks

________________________________
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Tuesday, July 5, 2011 3:17 AM
Subject: RE: [MS_AccessPros] Vertical data in Horizontal form

I would probably create a crosstab but it might be easier to create a multi-column subreport that displays across then down. This would also provide for wrapping to the next line since I don't know how you might possibly display 30 items and quantities across a page.

You didn't answer my question:
What is stored in your table that determines which item is numbered 1, 2, etc.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com; MS_Access_Professionals@yahoogroups.com
From: mmabizer@yahoo.com
Date: Mon, 4 Jul 2011 12:26:55 -0700
Subject: Re: [MS_AccessPros] Vertical data in Horizontal form

Hi Duane Hookom
Thanks for your time
Well i this data to make monthly report where i have to show that how many items each sales person has dispatched.. where in each date it shows horizontally how many items with quantity one has dispatched.. i tried cross tab but it doesn't give me the result i want..

i have sample file but i dont know where to upload it so everyone can see what i want.. n its difficult for me to explain too..
im sending u a peronal mail with 2 pictures of what i want..
Thank you..

Abizer

________________________________
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Tuesday, July 5, 2011 12:11 AM
Subject: RE: [MS_AccessPros] Vertical data in Horizontal form

Your field names and data are difficult to determine. I expect you can use a crosstab query to do this.

Are there ever more than 30 item codes that might be generated?

What is stored in your table that determines which item is numbered 1, 2, etc.

What is the reason for displaying in this format? Is this for a report, web page, form, query, or what?

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: mmabizer@yahoo.com
Date: Mon, 4 Jul 2011 07:04:51 -0700
Subject: [MS_AccessPros] Vertical data in Horizontal form

Hi All

Im working in a company where there are 30 products.
Each salesperson sales many items each day..
My data base looks like this

SP IDDATEITEM CODEQUANTITY
30012312/06/201124023
30012312/06/201128052

30012312/06/201135011

30015412/06/201124023

30015412/06/201136132

30035012/06/201145115

30035012/06/201159091

30035012/06/201119527

I want to make a query that displays each salesman's daily record in a row..
like this

SP IDDATEITEM CODE1QUANTITY1ITEM CODE1QUANTITY1ITEM CODE1QUANTITY1
30012312/06/2011240232805235011
30015412/06/20112402336132
30035012/06/2011451155909119527

I shall be grateful if any one helps me out..
Thank you very much

Abizer

[Non-text portions of this message have been removed]