Using 2 SQL statements in 1 Query

[Guest]

Hello,

I have a primary table populated by input data and update queries that
provide two numeric scores for ranking landslides. The first score is a
hazard score, and the second is a STIP score. I am using an SQL statement to
rank the scores according to severity. The SQL works for separate queries,
but I cannot get the Report to read the data from more than one query. When
I try to combine them in the following way, I get a syntax error:

SELECT HazScore,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.HazScore>T.HazScore)+1
AS HazRank
From PrimarySlideTable T
ORDER BY HazScore DESC,

SELECT STIP_Score,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.STIP_Score>T.STIP_Score)+1
AS STIPRank
From PrimarySlideTable T


I'm not sure if this is allowed in Access, or if there is another way to put
two sets of rankings in a report, or if I just don't know the proper syntax.

Thanks,

Curran Mohney

 

[Guest]

Have the first query as a "table" (data source) in the second one.

 

[Larry Koehler]

Create a SQL specific Union Query, paste this SQL into it and type UNION
between the two queries.

 

[Guest]

Thanks Larry:

This is what I did:

SELECT HazScore,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.HazScore>T.HazScore)+1
AS HazRank
From PrimarySlideTable T
ORDER BY HazScore
UNION ALL
SELECT STIP_Score
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.STIP_Score>T.STIP_Score)+1
AS STIPRank
From PrimarySlideTable T

I'm now getting an error that says the number of columns in the two queries
do not match (Error 3307). It seems that I have matching numbers of column.
Do you have any idea what's causing the error or how do I determine the
number of column I might be generating?

Thanks!

 

[Tom Ellison]

Dear Curran:

You're missing a comma between the two columns in the second SELECT
statement. Put the ORDER BY in the LAST QUERY of a UNION. So, it would
look like this:

SELECT HazScore,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.HazScore>T.HazScore) + 1
AS HazRank
FROM PrimarySlideTable T
UNION ALL
SELECT STIP_Score,
(SELECT COUNT(*)
FROM PrimarySlideTable T1
WHERE T1.STIP_Score>T.STIP_Score) + 1
AS HaxRank
FROM PrimarySlideTable T
ORDER BY HazScore

I have changed the alias of the second column in the second SELECT query.
It doesn't really matter, the column names will come from the first SELECT
query anyway. The ORDER BY must reference the column names from the first
SELECT even though it comes at the end.

Does this help?

Tom Ellison

 

[Guest]

Hi Tom, Thanks for your help again, I really appreciate it!

That does help. I was also able to add additional columns to the query also.
I also did this the hard way; I made two 'make table' queries, one based on
the HazScore, and another from the STIP_Score. I then made a one-to-many
relationship between the two based on the slide inventory number. From that
I can create a report with rankings for both the STIP Score and the Hazard
Score.

Once again, You have really helped me out!