Josh-
Ah, you would have to build a very big UNION query that might not compile. It
would look like:
SELECT "03/01/2011" AS Date, Sum([03/01/2010Diff]) As Total
FROM qryFinancialsDiff
UNION ALL
SELECT "04/01/2011" AS Date, Sum([04/01/2011Diff]) As Total
FROM qryFinancialsDiff
UNION ALL ...
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: Saturday, June 25, 2011 4:45 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Crosstab Query - Column Differences Conflict
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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar