Complex Query and Compound Primary Key

[zashaikh]

Hello.



I am having a problem with a query. Basically, I have two tables. One table, lets call it tblDate, supplies me with the [Date] and a [CaseID]. [CaseID] is the primary key for tblDate.

I have another table called tblMain. tblMain is comprised of [CaseID], [Characteristic], [City1], [City2], and [City3]. [CaseID] and [Characteristic] are a combined primary key. The values for [Characteristic] can be "Alpha", "Beta", or "Gamma".

[tblDate].[CaseID] = [tblMain].[CaseID].

Okay, so that is the setup of my tables.

I am trying to make a query that will show me how often a city is linked to a case. I do not care if it is [City1] [City2] or [City3]. Additionally,

"Alpha" also supercedes "Beta" and "Gamma." For example, if I have CASEID#33 that is characterised as BOTH "Alpha" and "Beta", I will have two records with [CaseID] = #33. When I run my query, I want to ignore EVERYTHING having to do with the record that contains "Beta".

I am having difficulty with this query however.

[zashaikh]

I have made a query, using "Total-First" for [City 1] [City2] and [City3] and "Total-Min" for [Characteristic]. I then enter criteria "New York" under each city. Using OR (so its not on the same line, but different lines).

However, One incident is set up as such on tblMain
CaseID 55 Characteristic=Alpha City1 = Seattle City2= Hong Kong City3=Richmond
CaseID 55 Characteristic=Beta City1 = Bankog City2= New York City3=London

The problem is that my query returns CaseID55 WHEN IT SHOULD NOT. The query replaces "City2 = HongKong" with "City2 = New York". But since "New York" is characterized under "Beta" it should be ignored completed due to the "Total-MinOfCharacteristic".

[ranman256]

you cant join the 2 tables, and under the field set criteria: <> "beta" ?

[zashaikh]

There is a lot more information in those tables. No. I am not able to make design changes to them.

[aytee111]

First, get the records that you want before setting criteria:
- query 1, all records with Alpha
- query 2, all records on query 1 plus records on tblMain with Beta that don't exist on query 1
- query 3, all records on query 2 plus records on tblMain with Gamma that don't exist on query 2

Use query 3 as your main list of records to begin selecting the city.

[zashaikh]

Thank you aytee111. That seems simple enough. I will give that a try.

On Query2, would I enter "Where [Query1].[Characteristic] <> Beta" and [tblMain] = Beta?

[aytee111]

Not so simple after all!

Query1 - Alpha:
SELECT tblMain.CaseID, tblMain.Characteristic FROM tblMain WHERE (((tblMain.Characteristic)="alpha"));

Query 2 - Beta:
SELECT tblMain.CaseID, tblMain.Characteristic FROM tblMain LEFT JOIN Query1 ON tblMain.CaseID = Query1.CaseID WHERE (((tblMain.Characteristic)="beta") AND ((Query1.CaseID) Is Null));

Query 3 - Gamma:
SELECT tblMain.CaseID, tblMain.Characteristic FROM (tblMain LEFT JOIN Query1 ON tblMain.CaseID = Query1.CaseID) LEFT JOIN Query2 ON tblMain.CaseID = Query2.CaseID WHERE (((tblMain.Characteristic)="gamma") AND ((Query1.CaseID) Is Null) AND ((Query2.CaseID) Is Null));

Query 4 - the full list, one record per CaseID:
SELECT * FROM Query1
UNION SELECT * FROM Query2
UNION SELECT * FROM query3;

Now use query 4 to check for cities.

[zashaikh]

Thanks. I added a "Date" criteria to Query1. I want to pull only data from 2016. This query works correctly.

But for some reason, Query2 is ignoring the fact that there is a Date parameter in Query1.

[aytee111]

Query1 are Alpha records, query2 are Beta records - where does the date come in for Beta?

[zashaikh]

I have alpha, beta, and gamma records going back to 1992. I only want to look at 2016 for all three characteristics

[aytee111]

Each time you read the tblMain to select records you will need to add the date criteria.