Query works from Excel but not within Access

[fboehlandt]

Hi everyone,
I have a problem with one of my queries. I have two normalized tables (PerformanceA, PerformanceB) containing the performance of various investment funds. Both tables have the same fields:
[Fundname] [Fundcode] [Date] [Return]
Obviously, one fund may have many return observations. In order to extract funds that have continuous track records between two dates I have come up with the following search query:

SELECT PerformanceA.*
FROM PerformanceA
WHERE PerformanceA.Code IN (
SELECT PerformanceA.Code
FROM PerformanceA
WHERE PerformanceA.Code IN (
SELECT PerformanceA.Code
FROM PerformanceA
WHERE PerformanceA.MM_DD_YYYY=#7/1/2000#)
AND PerformanceA.MM_DD_YYYY=#6/1/2005#);

Note that the dates are passed on from a userform in Excel. Thus, I have to use this rather complicated query for an otherwise simple search. Also, I have greatly simplified the actual query. Suffices to say, the above query works fine for both tables 'PerformanceA' and 'PerfromanceB'. However, if I run a UNION query to join the two tables first and then rerun the above query on the resulting table, the following happens: The query appears to be solving quickly (according to the query running bar) and then suddenly stops about 2/3 of the way through. It appears as if the query starts running over and over again. Additionally, if I trigger the query from Excel as part of a data connection (query in command text), I get the results that I want. I'm not sure but I think this query used ti run in the past - I have made some minor changes since, though. What could be the reason for this? Maybe some tables are corrupt? I really don't understand, any help is appreciated!
​

 

[jdraw]

Take a look at this link and look for Alias in subquery.

http://allenbrowne.com/subquery-02.html

 

[fboehlandt]

Dropping the table prefix 'PerformanceA' from the outer query actually makes the whole query faster (don't really understand why). However, no luck with the query when used in context of the union table. It keeps running and running as if stuck in a loop (that is, I can see the query being calculated, then it stops and starts over again. The cursor keeps changing from hour glass to arrow and back). Also, I double-checked and the query definitely works when using data connection in Excel and importing the query results in to a pivottable. I have no idea what's going on. Below is the query at the moment:

SELECT *
FROM UnionTable
WHERE Code IN (
SELECT UnionTable.Code
FROM UnionTable
WHERE UnionTable.Code IN (
SELECT UnionTable.Code
FROM UnionTable
WHERE UnionTable.MM_DD_YYYY=#7/1/2000#)
AND UnionTable.MM_DD_YYYY=#6/1/2005#);

 

[boblarson]

I have two normalized tables (PerformanceA, PerformanceB) containing the performance of various investment funds. Both tables have the same fields:​

Umm, your design is definitely NOT NORMALIZED. What led you to believe it was? If it was normalized you would not have two tables with the same fields.

 

[fboehlandt]

Originally, there were to access databases. Each database contains the same tables with identical fields but different (unique) records. I created a new database containing all tables from the two previous databases. Lastly, I used a simple Union query to combine the two 'Performance' tables (which are normalized). Is there a better way?

 

[boblarson]

I used a simple Union query to combine the two 'Performance' tables (which are normalized).

I think you need to study normalization a bit more to find out that it is not taking union queries to get to a certain output. But it is the TABLE design and structure that determines whether or not a database is normalized. You immediately violate the rules of normalization when you have duplicate tables with the same structure storing the same type of information but one refers to what should be an attribute of the data and not making them into separate tables.

Is there a better way?

[/quote]
Yes, do it right. I am only talking in terms of your performance tables but they should not be separate tables. There should be an identifier in THE performance table which makes the distinction that would determine whether the data should be classified as A or B. But the rest of your table structure would be good to see as it is possible that you THINK you have a normalized structure (outside of the performance tables) but in actuality do not. But we can't tell because we don't see your table structure. An uploaded database file with just the tables and the relationships would give us a clue to go on.

 

[vbaInet]

To this forward, -->

I think you need to study normalization a bit more to find out that it is not taking union queries to get to a certain output.

... I present:

http://support.microsoft.com/kb/100139
http://r937.com/relational.html

 

[fboehlandt]

@Bob
you are absolutely right, of course. However, as usual the choice of database design does not reside with me. I get the (two) database snapshots from an external database provider. The databases are idenitcal with respect to the number of tables and the fields within. I would like to combine the two databases. Database A looks as follows:

Table 'InformationA'
[Fundname] [Fundcode] [Description]

Table 'PerformanceA'
[Fundname] [Fundcode] [Date] [Return]

Fundcode is unique serial number and thus suited to be used as a primary key in table 'InformationA'. 'Fundname' may be removed from 'PerformanceA' as the information is redundant but it doesn't really bother me. a Of course, the relationship between Fundcode and Date is 1 : n (i.e. several performance observation per fund). Database B looks exactly as Database A. Let the table names be 'InformationB' and PerformanceB'. All I would like to do is to combine the two databases into one.

I provide the design of 'PerformanceA' and 'PerfromanceB' below. You can judge for yourself whether or not the tables are normalized. If that doesn't help I will post the tables on my website:

[Fund1] [Code1] [Date1] [Return1]

[Fund1] [Code1] [Date2] [Return2]

[Fund1] [Code1] [Date3] [Return3]

...

[Fund1] [Code1] [DateN1] [ReturnN1]

[Fund2] [Code2] [Date1] [Return1]

[Fund2] [Code2] [Date2] [Return2]

[Fund2] [Code2] [Date3] [Return3]

...

[Fund2] [Code2] [DateN2] [ReturnN2]

...

[FundM] [CodeM] [Date1] [Return1]

[FundM] [CodeM] [Date2] [Return2]

[FundM] [CodeM] [Date3] [Return3]

...

[FundM] [CodeM] [DateNM] [ReturnNM]


where M is the total number of funds and Nk denotes the number of observations per fund, [Fund] is the fund's name and

 some  arbitrary serial number unique to each fund. The new combined database could contain some unique identifier for the database:

[Fundname] [Fundcode] [Description] [dbtype]