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