'Query too complex' need help with Joins

  • Thread starter Thread starter andre
  • Start date Start date
A

andre

I need some serious help with a query I am working on.
Here are the two table structures I am running the query
on and some sample data to test.

Table #1: Call this table 'Static'.

There is not one primary key in the following table. Every
field can have duplicate values in other records. However,
the combination of PortfolioID, Cusip and Lot IS Unique.
Here are the field names and data types.

PortfolioID(int) Cusip(string) Lot(int) Par(long)

Here is some pipe-delimited sample data.
1001|DER5|1|50000
1001|JHY5|1|73457
4545|LJU8|1|23526
4545|NVB3|1|84588
4545|NVB3|2|43547


Table #2: Call this table 'Scenarios'.

There is not one primary key in the following table. Every
field can have duplicate values in other records. However,
the combination of PortfolioID, Cusip, Lot, AND Scenario
IS unique. Here are the field names and data types.

PortfolioID(int) Cusip(string) Lot(int) Scenario(Char)
Yield(double)

Here is some pipe-delimited sample data:
1001|DER5|1|A|2.4
1001|DER5|1|B|3.6
1001|DER5|1|C|5.4
1001|JHY5|1|A|2.3
1001|JHY5|1|B|3.7
1001|JHY5|1|C|3.8
4545|LJU8|1|A|3.2
4545|LJU8|1|B|4.6
4545|LJU8|1|C|5.3
4545|NVB3|1|A|4.1
4545|NVB3|1|B|5.7
4545|NVB3|1|C|6.3
4545|NVB3|2|A|1.3
4545|NVB3|2|B|2.4
4545|NVB3|2|C|6.4


Here is my delima. I am trying to query for all fields in
Static where Par > 0 AND in the scenarios table (when
scenario = A, Yield >= 3.0) And (when scenario = C, Yield
= 5.5). A query like this shouuld only return 1 record,
Click to expand...
that is 4545-NVB3-1-84588.

My query is something like:
SELECT * FROM Static
WHERE Static.Par > 0 AND
(WHEN IN 'Scenarios' TABLE)
Static.PortfolioID-Cusip-Lot = Scenarios.PortfolioID-Cusip-
Lot
AND (IN Scenario A, Yield >=3.0) AND (IN ScenarioB, Yield
= 5.5)
Click to expand...

I know this is not SQL syntax but this is the best way I
know how to explain what I am trying to do. I am sure this
problem is the result of poor database structure
development. But I was not apart of that process and
cannot change the way the tables are structured. The only
solution I can find is to query for all static data
requirements, with that group of PortfolioID-Cusip-Lot and
somehow split the Scenario data into three separate tables
one for each Scenario. However this will become very
inefficient Make-Table in Access from a SQL Server with
over 1 million records.

I really need this to be done in one query if at all
possible. Please help.

Thanks a lot.
brazilnut

HERE IS SOMETHING I TRIED TODAY THAT DIDNT WORK. Here I am
attemping to create one unique primary key... on the fly
and nest multiple selects together to get the result I
want. This however didnot work.:

SELECT CStr(PortfolioID) + Cusip +CStr(Lot) AS BondID, *
FROM Static
WHERE BondID
IN(SELECT CStr(PortfolioID) + Cusip + CStr(Lot) As BondID
FROM Shocked WHERE (Scenario = 'A' AND Yield > 4.5) AND
BondID
IN(SELECT CStr(PortfolioID) + Cusip +CStr(Lot) As BondID
FROM Shocked WHERE Scenario = 'B' AND Yield >= 5.5));
 
I need some serious help with a query I am working on.
Here are the two table structures I am running the query
on and some sample data to test.

Table #1: Call this table 'Static'.

There is not one primary key in the following table. Every
field can have duplicate values in other records. However,
the combination of PortfolioID, Cusip and Lot IS Unique.
Click to expand...

Well, a Primary Key can consist of up to TEN fields. I'd suggest
opening the table in design view, ctrl-clicking these three fields,
and clicking the Key icon to create a three-field primary key.
Table #2: Call this table 'Scenarios'.

There is not one primary key in the following table. Every
field can have duplicate values in other records. However,
the combination of PortfolioID, Cusip, Lot, AND Scenario
IS unique. Here are the field names and data types.
ditto.


Here is my delima. I am trying to query for all fields in
Static where Par > 0 AND in the scenarios table (when
scenario = A, Yield >= 3.0) And (when scenario = C, Yield
that is 4545-NVB3-1-84588.

My query is something like:
SELECT * FROM Static
WHERE Static.Par > 0 AND
(WHEN IN 'Scenarios' TABLE)
Static.PortfolioID-Cusip-Lot = Scenarios.PortfolioID-Cusip-
Lot
AND (IN Scenario A, Yield >=3.0) AND (IN ScenarioB, Yield
Click to expand...

Create a Query based on your two tables in the query design grid. Drag
*each* of the PortfolioID, Cusip, and Lot fields from Static to
Scenarios. Then add the Scenarios table AGAIN - Access will alias it
as Scenarios_1 - and do the same join.

You can then put a criterion on Scenarios.Scenario of "A", and on
Scenarios_1.Scenario of "B", and similarly for the two Yield fields.
 
Back
Top