Search results

Hi everyone, i would like ot eliminate duplicate entries from two tables. Both tables contain a field called name which is unique within each table but may have duplicate observations across the two tables. The other field entries may not be identical. Consequently, a UNION query does not...

Assuming the following: 1. I have a table with no date observations without performance observation (which I do). Let that table be called Performance2, and 2. the performance series of all funds start and end at different times but have no reporting gaps in between (which I think is the case)...

The key word here is a set of queries. In the OLE DB command text of the data import into Excel there can only be one query. I did actually use the If-else-end if construct rather then GoTo but used Goto intially just to test the macro. Alas, in the context of error handling an trapping I still...

Yip, that is worth a try. Although the backend is Access so I'm not too optimistic. I will keep you posted if anything comes up. Thanks for your help so far!

Yes, that would yield a table with no empty entries. Alas, that is not quite what I need. Also, it can be assumed that I have a table that is normalized and contains no data gaps (i.e. only dates with associated returns). The starting and ending dates come from an Excel Userfrom, are copied into...

Yes, I believe you are correct. However, the dates originate from a Userform in Excel (not an Access form), are then copied into the data import string (command statement) of the OLE DB query and must hence be the dates used to retrieve the data from Access. The second query SELECT...

Okay, changed the normalized table so that there are no gaps in the data (thus entry ommitted if no corresponding return value). Surely there must be a way now?

Thanks, ISNULL does indeed work :)

Hi everyone, I have the following (normalized) table 'mytable' containing 4 fields: [Fund] [Date] [Return] FundA 1001 Date1 Return1 FundA 1001 Date2 Return2 FundA 1001 Date3 Return3 ... FundA 1001 DateN ReturnN FundB 1002 Date1 Return1 FundB 1002 Date2 Return2 FundB 1002 Date3 Return3 ...

@vbaInet Hi there, I was just wondering if you could help me out again with the following request. In my now normalized table there are still fields that contain no values. More specifically, dates that have no performance enry (i.e. the fund did not report for that particular month). I shoud...

@Brian I thought so. Generally I am in agreement that there would be no gain to force it all into one query. However, there is a reason why I need to run one query before the other (or potentially jointly): I have a VBA script importing information from an Access database to an Excel pivotable...

p.s. the following was my effort so far. This does not work because table name 'Information' appears twice in FROM clause: SELECT Information.Code, Performance.MM_DD_YYYY, Performance.Return FROM Information INNER JOIN (Performance INNER JOIN Information ON Performance.Code =...

I can run the following query and call it 'dummy': SELECT Information.Code FROM Information INNER JOIN Performance ON Information.Code = Performance.Code WHERE Performance.MM_DD_YYYY>=#1/1/1998# And Performance.MM_DD_YYYY<=#1/1/2008# GROUP BY Information.Code HAVING...

@MSAccessRookie: No, all dates are included (thus in my example 121 dates for all funds). It is a potenially justified question whether dates with no corresponding performance value should be included in a nomalized table. Ideally, these observations should be dropped and I'm considering doing...

Hi everyone, I have the following table named 'information' that contains the names and codes of investment funds in two fields: <Name> <Code> Name1 Code1 Name2 Code2 ... NameN CodeN A separate normalized table 'performance' contains the performance of said funds: <Name> <Code>...

yes, that is exactly what I am doing now. Best way to handle this in the long-run. Thanks for your input, it is greatly appreciated :)

@vbaInet sorry, there was a typo. All sorted now, the macro works fine and is reasonably fast. Many thanks :) @namliam I must admit I did not consider circumvening the UNION statements when building the command text query. Let me play around with your code a little bit to see if it works for...

@jzwp22 Thanks very much for your quick reply. Here some additions to clarify: each fund can have only one strategy associated with it (1 to 1) each fund can either be leveraged or not (1 to 1) this information is contained in the table 'information' c) each fund can have several ROI (return...

@vbaInet seems you were right. The query proved to be a dead-end. I'm now willing to give the macro a run. I use VBA quite extensively in Excel but need some pointers for Access. I have created a table called 'mytable' and copied your code into a sub procedure. To test the macro I have...

Hi everyone, I have the following three tables: Table Assets Code AUM_Date1 AUM_Date2 ...AUM_Date200 1001 1002 ... 7000 this table contains the assets-under-management (AUM) for 6000 investment funds for 200 different months (i.e. 1200000 obs) Table Returns Code ROI_Date1 ROI_Date2 ...