Josh,
For getting the priced differentials, we can use two supplementary queries.
The first one (Q_4A_PricedTotals) as given below, makes use of query Q_4_Totals (already provided to you in my earlier post) having inner join with query Q_Prices on fields Fdoc and Dte. As stated by you, Q_Prices (having fields Fdoc, Dte and Price) carries the price for each Fdoc on each date. It is understood that in this query, there is only one record per combination of Fdoc & Dte and there is no missing value.
Q_4A_PricedTotals - Simple Select Query
(It makes use of queries Q_4_Totals and Q_Prices)
==================================
SELECT Q_4_Totals.*, Q_Prices.Price, Nz([TotAmt]*[Price],0) AS TotPricedAmt
FROM Q_4_Totals INNER JOIN Q_Prices ON (Q_4_Totals.Dte = Q_Prices.Dte) AND (Q_4_Totals.Fdoc = Q_Prices.Fdoc)
ORDER BY Q_4_Totals.Fdoc, Q_4_Totals.Dte;
==================================
Final results are provided by query Q_6A_TotPricedDiff_Datewise (having unequal left join between two instances of Q_4A_PricedTotals) as given below:
Q_6A_TotPricedDiff_Datewise - Totals Query
(It makes use of query Q_4A_PricedTotals)
==================================
SELECT Q1.Dte, Sum(IIf([Q1].[Rank]=1,Nz([Q1].[TotAmt],0),(Nz([Q1].[TotAmt],0)-Nz([Q2].[TotAmt],0)))) AS TotFinDiff, Sum(IIf([Q1].[Rank]=1,Nz([Q1].[TotPricedAmt],0),(Nz([Q1].[TotPricedAmt],0)-Nz([Q2].[TotPricedAmt],0)))) AS TotPricedFinDiff
FROM Q_4A_PricedTotals AS Q1 LEFT JOIN Q_4A_PricedTotals AS Q2 ON (Q1.Fdoc = Q2.Fdoc) AND (Q1.Rank = Q2.Rank + 1)
GROUP BY Q1.Dte;
==================================
Query Q_6A_TotPricedDiff_Datewise as given above, provides output in following style, giving date-wise overall totals for financial differences (as done earlier) as well as priced financial differences:
--------------------------------------------
Dte TotFinDiff TotPricedFinDiff
--------------------------------------------
You might like to try it out and confirm whether the outcome covers your revised needs.
Best wishes,
A.D. Tejpal
------------
----- Original Message -----
From: joshzulaica
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, June 28, 2011 00:26
Subject: [MS_AccessPros] Re: Crosstab Query - Column Differences Conflict
Hey A.D.,
Sorry to bother you again. I have encountered a detail I should add to my differences, before calculating the totals.
The process so far has been, for every date "i":
Difference of F1(i) = Amt(i) - Amt(i-1)
Difference of F2(i) = Amt(i) - Amt(i-1)
Difference of F3(i) = Amt(i) - Amt(i-1)
Difference of F4(i) = Amt(i) - Amt(i-1)
And finally calculate the total sum for all Fdoc's, for every date.
Now I want to take a different approach. Keeping the original table, and the queries we already had, let's say I had a separate query with the following information:
[Dte] [Fdoc] [Price]
01/05/2011 F1 10
01/05/2011 F2 9.9
01/05/2011 F3 9.9
01/05/2011 F4 10
04/05/2011 F1 9.8
04/05/2011 F2 10
04/05/2011 F3 9.8
04/05/2011 F4 10.1
09/05/2011 F1 10.1
09/05/2011 F2 9.9
09/05/2011 F3 9.8
09/05/2011 F4 10.2
(...and so on for every date available; the price for each instrument only appears ONCE per date.)
I would need to calculate for every date "i":
Difference of F1 in Price(i) = [Amt(i) - Amt(i-1)] * Price(i)
Difference of F2 in Price(i) = [Amt(i) - Amt(i-1)] * Price(i)
Difference of F3 in Price(i) = [Amt(i) - Amt(i-1)] * Price(i)
Difference of F4 in Price(i) = [Amt(i) - Amt(i-1)] * Price(i)
To finally obtain a similar query as the Q_6_TotDiff_Datewise we had before.
So for every instrument difference, I need to multiply times its price, for that date in which we're calculating the difference. Then SUM over all the instruments for each date to finally obtain a query:
Date - TotFinDiffInPrice
--------------------------
01/05/2011 ???
04/05/2011 ???
04/05/2011 ???
09/05/2011 ???
12/05/2011 ???
15/05/2011 ???
I tried playing around with the code and queries you gave me, but for some reason it tells me there are ambiguous references, and I can't seem to include that separate query somehow. I'm still in the process of understanding the first queries; and as you might see, this additional step gives it a complicated turn.
Thanks for your time. :)
Josh Z.
--- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@...> wrote:
>
> You are most welcome Josh! Glad your problem stands resolved.
>
> The process of learning something new is a constant one and it can be quite a fun to explore query based solutions.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ----- Original Message -----
> From: joshzulaica
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Sunday, June 26, 2011 00:34
> Subject: Re: [MS_AccessPros] Crosstab Query - Column Differences Conflict
>
>
> A.D.,
>
> That's awesome!! Exactly what I needed. I really need to study more about joins, so I can start thinking things like this; it makes it way simple. And it's really all about structuring.
>
> With this, I'm about done with this database.
> Thanks a lot for your help, really.
>
> Greetings,
> Josh Z.
>
> --- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@> wrote:
> >
> > Josh,
> >
> > If you need to tabulate date-wise, the sum of financial differences for all instruments, there is no need to fall back upon the crosstab query Q_5_Crosstab_FinalResult.
> >
> > The source used for setting up the crosstab query, (i.e. unequal outer join between two instances of totals query Q_4_Totals) can be used directly in a totals query. Sample query Q_6_TotDiff_Datewise as given below, should get you the desired results:
> >
> > Q_6_TotDiff_Datewise
> > ===============================
> > SELECT Q1.Dte, Sum(IIf([Q1].[Rank]=1,Nz([Q1].[TotAmt],0),(Nz([Q1].[TotAmt],0)-Nz([Q2].[TotAmt],0)))) AS TotFinDiff
> > FROM Q_4_Totals AS Q1 LEFT JOIN Q_4_Totals AS Q2 ON (Q1.Rank = Q2.Rank + 1) AND (Q1.Fdoc = Q2.Fdoc)
> > GROUP BY Q1.Dte;
> > ===============================
> >
> > Based upon test data in my sample db uploaded earlier to Samples folder in Files section (Query_CrosstabWithColumnStepDiff.zip), the output of
> > Q_6_TotDiff_Datewise is seen to be as follows, in line with your stated objective:
> >
> > Dte TotFinDiff
> > --------------------------------
> > 01-May-2011 6000
> > 04-May-2011 1900
> > 09-May-2011 -500
> > 12-May-2011 -6600
> > 15-May-2011 5000
> > --------------------------------
> >
> > Best wishes,
> > A.D. Tejpal
> > ------------
> >
> > ----- Original Message -----
> > From: joshzulaica
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Saturday, June 25, 2011 01:14
> > Subject: Re: [MS_AccessPros] Crosstab Query - Column Differences Conflict
> >
> >
> > A.D.,
> >
> > This additional query you added actually fixed the problem.
> > Once I tried your sample Db, I applied it to mine; all the final subtractions are now displaying, and so are the subtractions for those instruments with only one value. Thanks a lot.
> >
> > It does slow down a bit but so far it's nothing to worry about. I will consider outputting the results in (c) and (d) to tables.
> >
> > How would you go from Q_5_Crosstab_FinalResult to a Query (or Table) with the following format?
> >
> > [Date] [Total Sum of Differences]
> > 01/05/2011 6000
> > 04/05/2011 1900
> > 09/05/2011 -500
> > 12/05/2011 -6600
> > 15/05/2011 5000
> >
> > I really need to have all dates back into the same field, instead of having each as a separate field as seen in the final crosstab query, since I'll be running operations over the total sum of differences, depending on the date.
> > (I tried using a Make-Table query with Q5 as source but it isn't working, it picks up each date as a different field.)
> >
> > Thanks a lot for your help!
> > Josh Z.
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@> wrote:
> > >
> > > Josh,
> > >
> > > As explained by you, some instruments might not have any transaction on some of the dates. Even for such cases, the full complement of columns is required to be generated, with appropriate display of values and differences. This will need another layer of outer join involving a Cartesian output covering each distinct Fdoc and Dte.
> > >
> > > Sample file named Query_CrosstabWithColumnStepDiff.zip, uploaded to Samples folder in Files section demonstrates this approach.
> > >
> > > For ready reference, SQL of sample queries mentioned below, is placed at the end of this post:
> > > (a) Q_1_Fdocs (Distinct values of Fdoc)
> > > (b) Q_2_Dates (Distinct values of all Dtes irrespective of Fdocs)
> > > (c) Q_3_FdocDteRanks (Based upon Cartesian join between Q_1_Fdocs and Q_2_Dates)
> > > (d) Q_4_Totals
> > > (e) Q_5_Crosstab_FinalResult
> > >
> > > You might like to try it out (in the first stage, as it is, without waiting for final adaptation into your project) and confirm whether the results are in line with your requirements or if anything needs further attention.
> > >
> > > Note:
> > > Eventually, on actual use involving large data set, if the performance is found to be slow, you could consider directing the output of (c) or (d) into a table and use that as the basis for further steps.
> > >
> > > Best wishes,
> > > A.D. Tejpal
> > > ------------
> > >
> > > Sample Queries:
> > > ----------------
> > > Q_1_Fdocs
> > > ====================================
> > > SELECT T_Data.Fdoc
> > > FROM T_Data
> > > GROUP BY T_Data.Fdoc;
> > > ====================================
> > >
> > > Q_2_Dates
> > > ====================================
> > > SELECT T_Data.Dte
> > > FROM T_Data
> > > GROUP BY T_Data.Dte;
> > > ====================================
> > >
> > > Q_3_FdocDteRanks
> > > ====================================
> > > SELECT Q_1_Fdocs.Fdoc, Q_2_Dates.Dte, CLng(DCount("*","Q_2_Dates","Dte <= #" & Format([dte],"mm/dd/yyyy") & "#")) AS Rank
> > > FROM Q_1_Fdocs, Q_2_Dates;
> > > ====================================
> > >
> > > Q_4_Totals
> > > ====================================
> > > SELECT Q_3_FdocDteRanks.Fdoc, Q_3_FdocDteRanks.Dte, Q_3_FdocDteRanks.Rank, Sum(T_Data.Amt) AS TotAmt
> > > FROM Q_3_FdocDteRanks LEFT JOIN T_Data ON (Q_3_FdocDteRanks.Fdoc = T_Data.Fdoc) AND (Q_3_FdocDteRanks.Dte = T_Data.Dte)
> > > GROUP BY Q_3_FdocDteRanks.Fdoc, Q_3_FdocDteRanks.Dte, Q_3_FdocDteRanks.Rank;
> > > ====================================
> > >
> > > Q_5_Crosstab_FinalResult
> > > ====================================
> > > TRANSFORM First(IIf(Q1.Rank=1,Nz(Q1.TotAmt, 0),(Nz(Q1.TotAmt, 0)-Nz(Q2.TotAmt, 0)))) AS StepAmt
> > > SELECT Q1.Fdoc
> > > FROM Q_4_Totals AS Q1 LEFT JOIN Q_4_Totals AS Q2 ON (Q1.Rank = Q2.Rank + 1) AND (Q1.Fdoc = Q2.Fdoc)
> > > GROUP BY Q1.Fdoc
> > > PIVOT Q1.Dte;
> > > ====================================
> > >
> > > ----- Original Message -----
> > > From: joshzulaica
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Sent: Friday, June 24, 2011 01:56
> > > Subject: Re: [MS_AccessPros] Crosstab Query - Column Differences Conflict
> > >
> > > Hey A.D., thanks a lot for your suggestion, it is quite intuitive and simple. I knew the problem could be seen from another perspective!
> > >
> > > There's a slight issue though, for some reason, when I run Q_C for my instrument list, it is not calculating the last subtraction; this also means that for instruments which only have amounts (Amt) for ONLY one date, it simply doesn't display anything (the whole row turns out as Null, instead of having two values, one for that day with the value unmodified, and one for the next day with that same value, in negative).
> > >
> > > So let's say that for an instrument, I only had one amount, of 544, on Jan 5, which is the 4th column.
> > >
> > > Then the table, instead of showing this:
> > > 0 0 0 544 -544 ...
> > > Shows this:
> > > 0 0 0 0 0 ...
> > > (0 stands for Null in this case)
> > >
> > > What is causing the problem in the SQL code?
> > > Thanks for your time!
> > >
> > > Josh Z.
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "A.D. Tejpal" <adtp@> wrote:
> > > >
> > > > Josh,
> > > >
> > > > For each financial instrument (say Fdoc), you wish to display the amounts in crosstab style (one column per date) in such a manner that first column shows the actual amount while all other columns depict only the difference as compared to the preceding column.
> > > >
> > > > As a pure SQL based alternative, sample crosstab query Q_C, using unequal self outer join, as given below, should get you the desired results. It makes use of query Q_B, which in turn is derived from query Q_A, both given below.
> > > >
> > > > T_Data is the source table having fields Fdoc, Dte and Amt. Based upon totals query Q_A, sequential rankings date-wise per Fdoc are generated in simple select query Q_B. These rankings provide the basis for unequal self outer join in query Q_C.
> > > >
> > > > Q_C - Final Query (Crosstab)
> > > > (Makes use of query Q_B given below)
> > > > ================================
> > > > TRANSFORM First(IIf(Q1.Rank=1,Q1.TotAmt,(Q1.TotAmt-Q2.TotAmt))) AS StepAmt
> > > > SELECT Q1.Fdoc
> > > > FROM Q_B AS Q1 LEFT JOIN Q_B AS Q2 ON (Q1.Rank = Q2.Rank + 1) AND (Q1.Fdoc = Q2.Fdoc)
> > > > GROUP BY Q1.Fdoc
> > > > PIVOT Q1.Dte;
> > > > ================================
> > > >
> > > > Q_B - Intermediate Query (Simple select query)
> > > > (Makes use of query Q_A given below)
> > > > (Serves as source for query Q_C above)
> > > > ================================
> > > > SELECT Q_A.*, DCount("*","Q_A","Fdoc = '" & [Fdoc] & "' AND Dte <= #" & Format([dte],"mm/dd/yyyy") & "#") AS Rank
> > > > FROM Q_A;
> > > > ================================
> > > >
> > > > Q_A - First Stage Query (Simple totals Query)
> > > > (Serves as source for query Q_B above)
> > > > ================================
> > > > SELECT T_Data.Fdoc, T_Data.Dte, Sum(T_Data.Amt) AS TotAmt
> > > > FROM T_Data
> > > > GROUP BY T_Data.Fdoc, T_Data.Dte;
> > > > ================================
> > > >
> > > > Note:
> > > > (a) As subqueries are not suited in a query meant to be used as source for a crosstab query, DCount() function has been used in query Q_B above so as to get rankings as per dates for a given Fdoc.
> > > > (b) The suggested solution is in the context of Access 2003 desktop.
> > > >
> > > > Best wishes,
> > > > A.D. Tejpal
> > > > ------------
> > >
> > > >
> > > > ----- Original Message -----
> > > > From: John Viescas
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Sent: Thursday, June 23, 2011 12:10
> > > > Subject: RE: [MS_AccessPros] Crosstab Query - Column Differences Conflict
> > > >
> > > > Josh-
> > > >
> > > > One solution that comes to mind is to write some code to dynamically build the
> > > > query you need and then run it. You can open the QueryDef object that is your
> > > > current Crosstab query, get the names of the fields as generated, and build the
> > > > SQL to run using the original Crosstab as input. You retrieve the names of the
> > > > columns you want using an integer index. Maybe something like this:
> > > >
> > > > Dim db As DAO.Database, qd As DAO.QueryDef, qd2 As DAO.QueryDef
> > > > Dim intI As Integer, strSelect As String
> > > >
> > > > ' Point to this database
> > > > Set db = CurrentDb
> > > > ' Get the input query
> > > > Set qd = db.QueryDefs("qxtbFinancials")
> > > > ' Point to the query to update
> > > > Set qd2 = db.QueryDefs("qryFinancialsDiff")
> > > > ' Start the Select string
> > > > strSelect = "[" & qd.Fields(1).Name & "] As [" & qd.Fields(1).Name & "Diff]"
> > > > ' Loop through the remaining fields
> > > > For intI = 2 To qd.Fields.Count - 1
> > > > strSelect = strSelect & ", ([" & qd.Fields(intI).Name & "] - [" & _
> > > > qd.Fields(intI - 1).Name & "]) As [" & qd.Fields(intI).Name & "Diff]"
> > > > Next intI
> > > > ' Update the SQL of the 2nd query
> > > > qd2.SQL = "SELECT " & strSelect & " FROM qxtbFinancials;"
> > > > ' Done
> > > > Set qd2 = Nothing
> > > > Set qd = Nothing
> > > > Set db = Nothing
> > > > ' Open the revised query
> > > > DoCmd.OpenQuery "qryFinancialsDiff"
> > > >
> > > > The above assumes that your Crosstab query is named qxtbFinancials and you have
> > > > a second query whose SQL can be modified named qryFinancialsDiff.
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of joshzulaica
> > > > Sent: Thursday, June 23, 2011 1:33 AM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Crosstab Query - Column Differences Conflict
> > > >
> > > > Hello, and thanks for reading, I'm new here.
> > > >
> > > > I have a cross-tab query with Date (dd/mm/yyyy) as columns, and serial numbers
> > > > of financial instruments as rows. The total amounts are the Value.
> > > >
> > > >
> > > > What I need is to calculate the "daily net change", in order to do this, I have
> > > > to subtract the amount of each date by the amount of the previous date.
> > > >
> > > > For example:
> > > > -----------03/01/2011-----04/01/2011-----07/01/2011
> > > > LD-110203----12000-----------12000---------14515
> > > > XA-110331-----1587------------1587---------1587
> > > > BI-110602------2112-------------0-------------0
> > > >
> > > > Should turn out as:
> > > >
> > > > -----------03/01/2011-----04/01/2011-----07/01/2011
> > > > LD-110203----12000------------0-------------2515
> > > > XA-110331-----1587------------0--------------0
> > > > BI-110602------2112----------2112-----------0
> > > >
> > > > How can I accomplish this? I've tried a lot of things, and looked around on the
> > > > internet but I have only found solutions to monthly net changes, since just
> > > > assigning custom names to the columns like FebDiff: [Feb]-[Jan] is simple for
> > > > that little number of columns.
> > > >
> > > > (I'd like to note that the columns do not include all days in a year, only
> > > > working days. So manually creating custom column headers is not really an
> > > > option.. And if it helps in any way, I have a separate table in which I write
> > > > the next date to be added to that list of dates.)
> > > >
> > > > Thanks in advance for any help.
>
> [Non-text portions of this message have been removed]
>
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar