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
>
Tidak ada komentar:
Posting Komentar