John-
Indeed, we're getting closer!
The first code you suggested just turns out as qryFinancialsDiffs, so yeah that's not really the result we want.
The second one looks more like the one I need, but it's adding for each instrument, and what I want is to add for each date.
You summed all dates, for each instrument.
I want to sum all instruments, for each date.
Do you still have the original "ADDITIONAL" code I uploaded?
That's basically what I need, the sum for each date... but in that qrySUMS outcome, the results are transposed.
Change the code of qrySUMS to the following SQL:
-----------
SELECT "31/12/2010" AS Date, Sum([31/12/2010Diff]) As Total
FROM qryFinancialsDiff;
-----------
And that's an example of how the first row of what I need looks like.
The second and third rows would be:
03/01/2011, 29390648
04/01/2011, -30151149
...and so on til' the end. But I have no idea how to achieve it.
Maybe there's a way to part from the qrySUMS I created, and just transpose the info somehow? It would make the process longer though, I guess.
Thanks again,
Josh Z.
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Josh-
>
> OK, I changed your code to look like this:
>
> Public Sub UpdateHoldings()
>
> Dim db As DAO.Database, qd As DAO.QueryDef, qd2 As DAO.QueryDef
> Dim intI As Integer, strSelect As String
>
> '/******* ADDITIONAL
> Dim qd3 As DAO.QueryDef, qd4 As DAO.QueryDef, strSelect2 As String, intI2 As
> Integer
> '*******/
>
> Set db = CurrentDb
> Set qd = db.QueryDefs("qxtbFinancials")
> Set qd2 = db.QueryDefs("qryFinancialsDiff")
>
>
> strSelect = "[" & qd.Fields(0).Name & "], [" & _
> qd.Fields(1).Name & "] As [" & qd.Fields(1).Name & "Diff]"
>
> 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
>
> qd2.SQL = "SELECT" & strSelect & " FROM qxtbFinancials;"
>
> Set qd2 = Nothing
> Set qd = Nothing
> Set db = Nothing
>
> DoCmd.OpenQuery "qryFinancialsDiff"
>
> ' /******************************** ADDITIONAL
> Set db = CurrentDb
> Set qd3 = db.QueryDefs("qryFinancialsDiff")
> Set qd4 = db.QueryDefs("qrySUMS")
>
> strSelect2 = "[" & qd3.Fields(0).Name & _
> "] As Instrument, Sum([" & qd3.Fields(1).Name & "]) As [Sum of " &
> qd3.Fields(1).Name & "]"
> For intI2 = 2 To qd3.Fields.Count - 1
> strSelect2 = strSelect2 & ", Sum([" & qd3.Fields(intI2).Name & _
> "]) As [Sum of " & qd3.Fields(intI2).Name & "]"
> Next intI2
>
> qd4.SQL = "SELECT " & strSelect2 & _
> "FROM qryFinancialsDiff GROUP BY [" & qd3.Fields(0).Name & "];"
> DoCmd.OpenQuery "qrySUMS"
> '**********************************/
>
> End Sub
>
> That gives you a total by day and instrument. Are we getting closer?
>
> But the output looks just like qryFinancialsDiff, so I don't think that's what
> you want. Are you wanting to SUM across all the dates? If so, you cannot do
> that with a Totals query. Maybe what you really want is:
>
> ' /******************************** ADDITIONAL
> Set db = CurrentDb
> Set qd3 = db.QueryDefs("qryFinancialsDiff")
> Set qd4 = db.QueryDefs("qrySUMS")
>
> strSelect2 = "[" & qd3.Fields(0).Name & _
> "] As Instrument, ([" & qd3.Fields(1).Name & "] "
> For intI2 = 2 To qd3.Fields.Count - 1
> strSelect2 = strSelect2 & "+ [" & qd3.Fields(intI2).Name & "] "
> Next intI2
>
> qd4.SQL = "SELECT " & strSelect2 & ") As InstrumentTot FROM qryFinancialsDiff;"
> DoCmd.OpenQuery "qrySUMS"
> '**********************************/
>
> 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: Friday, June 24, 2011 9:30 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Crosstab Query - Column Differences Conflict
>
> John-
>
> I uploaded my sample database to Files > 2_AssistanceNeeded; the file is called
> qxtbFinancials_Josh.zip.
>
> I left tables and queries with the names you gave them and I haven't modified
> your code, though, as I mentioned, there is additional code for calculating the
> sum of amounts per date. I marked it with a comment so you can see it. The query
> this additional code generates is called qrySUMS. (You can run the code to see
> how they turn out, right now they have some random SQL I placed so that they
> would take less space).
>
> Using qrySUMS, what I want is to make a query where I have two columns: 'Date'
> and 'Total Amount'.
> My attempt to do it is called 'MakeTable TEST', but as you can see, the table it
> generates is just a replica of qrySUMS.
>
> Thanks, :)
> Josh Z.
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Josh-
> >
> > Please post the code you're using. If you modified my code, I need to see
> what
> > you're attempting to do.
> >
> > 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: Friday, June 24, 2011 7:24 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Crosstab Query - Column Differences Conflict
> >
> > John-
> >
> > Thanks for that, but that still doesn't fix the problem, since our
> VBA-generated
> > query is not "crosstab", it's a simple query with lots of fields named after
> > each date. So there's no way I can create a Make-Table query that generates a
> > table with just two columns from it.
> > All dates are independent instead of belonging to a same Field.
> >
> > Josh Z.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> > >
> > > Josh-
> > >
> > > Small bug in my code. I should include the "instrument" column like this:
> > >
> > > strSelect = "[" & qd.Fields(0).Name & "], [" & _
> > > qd.Fields(1).Name & "] As [" & qd.Fields(1).Name & "Diff]"
> > >
> > >
> > > 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: Friday, June 24, 2011 6:49 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Re: Crosstab Query - Column Differences Conflict
> > >
> > > John-
> > >
> > > Sorry I think I didn't explain myself well.
> > > I do not have a problem with "Diff" being there, I understand why you added
> > it;
> > > what I meant with losing the date "format" was that, when I generate my
> > original
> > > crosstab query, it automatically assigns the dates to the columns, knowing
> > they
> > > are indeed dates (since they're all coming from that SAME date field).
> > >
> > > If I created a make-table query with this original query as source, and ran
> > it,
> > > my results would become the following table:
> > > Instrument - Date - Sum[]
> > > LD-120404 03/01/2011 19196
> > > LD-120404 04/01/2011 19681
> > > (...)
> > > and so on, naming every instrument for each date, with its corresponding
> sum.
> > >
> > > When I run the VBA code over my original query (like you suggested), the
> > > resulting query does not write the Instrument serial numbers anywhere, so my
> > > query just has columns with dates, and the subtractions below each. The
> > > "instrument" column has disappeared.
> > >
> > > This isn't worrying, since in the end I just plan to sum all values for each
> > > column, like I described on my previous message.
> > >
> > > But, if I try to run a Make-Table query which has as source this newly
> > > constructed query through VBA (yours with a row for each instrument, or mine
> > > with just the totals, the outcome is the same for both), that part where
> every
> > > column belonged to the SAME "Date" field, has been lost, and that's why the
> > > Make-Table query generates a table that just looks exactly like it (columns
> > with
> > > dates and subtractions below each), when I would like it to generate a table
> > > just like the Make-Table query I described before (just two columns, and not
> > one
> > > for each date):
> > >
> > > Date - Sum
> > >
> > > Where all the date columns we had are now values, part of the same "Date"
> > field.
> > > I know it might sound a bit complicated, and it's probably easier to achieve
> > > from my new query (the one I created by adding up ALL values for each date),
> > > since it has a lot less information.
> > > Like I said, I don't really know how to write my dates (now columns), as
> rows
> > > which are members of the same "Date" field, each having its corresponding
> Sum,
> > > in another "Sum" field.
> > >
> > > Thanks for your time, I really appreciate it.
> > >
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > >
> > > > Josh-
> > > >
> > > > I added "Diff" to the end of the field names so that the query wouldn't
> get
> > > > confused about, for example, [03/01/2011] in the source query and the
> output
> > > > field name. You could convert the name to a date by doing something like:
> > > >
> > > > Cdate(Mid([Diff Date], 2, 10))
> > > >
> > > > 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 9:16 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: Crosstab Query - Column Differences Conflict
> > > >
> > > >
> > > >
> > > > John, thanks a lot for your suggestion.
> > > > That's exactly the type of solution I was looking for, since it
> dynamically
> > > > builds the query I need.
> > > >
> > > > Your code actually helped me write my own that builds a query with the
> > totals
> > > of
> > > > all "Diff"s on each date; just like this:
> > > > Sum of [03/01/2011Diff], Sum of [04/01/2011Diff], Sum of
> [05/01/2011Diff]
> >
> > > > -15049941, 35881474, -572457
> > > >
> > > > There's an issue afterwards though; after calculating these through the
> > code,
> > > I
> > > > lose the "Date" property the fields had, therefore, not being able to
> > > construct
> > > > a Make Table query from this last query, where I can again have all dates
> in
> > a
> > > > column, with the total of Values on each, in another column. Like this:
> > > >
> > > > Diff Date Diff
> > > > [03/01/2011Diff] -15049941
> > > > [04/01/2011Diff] 35881474
> > > > [05/01/2011Diff] -572457
> > > >
> > > > (...)
> > > >
> > > >
> > > > It's basically transposing the data somehow, where now my fields are
> > actually
> > > > rows of a new Field called "Diff Date" (or whichever name). Can you help
> me
> > > with
> > > > that?
> > > >
> > > > My database is about done once I accomplish that!
> > > > Thanks again, I thought I'd be stuck forever trying to calculate those
> > > > differences.
> > > >
> > > > Greetings. :)
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> > wrote:
> > > > >
> > > > > 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.
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar