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