John-
Yeah that exactly! I'll have to build a code that runs that.
Why do you say it might not compile?
Thanks a lot for your assistance and patience, it really helped me look at Access down the VBA path. Thing I hadn't done before.
Greetings, :)
Josh Z.
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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