Using Min & Max in combined SQL statements

[Llyllyll]

Afternoon all,

Please could someone help. I have two queries (as set out below) and would
like to use these to create one dynamic SQL statement. I've been browsing
the group for a while but can't seem to get me head around the syntax. I'm
using Access 97 and the results of this query will be passed to a report in
Excel. What I'm trying to achieve is to get the minimum and maximum
consecutive number that's been assigned to each company.

Query1
SELECT tblConsec.RecordID, tblConsec.ConsecNo, tblConsec.Company,
tblConsec.Years, tblConsec.ReportP380
FROM tblConsec
WHERE (((tblConsec.ReportP380)=False))
ORDER BY tblConsec.RecordID;

Query2
SELECT Query1.Company, Min(Query1.ConsecNo) AS MinOfConsecNo,
Max(Query1.ConsecNo) AS MaxOfConsecNo
FROM Query1
GROUP BY Query1.Company
ORDER BY Min(Query1.ConsecNo);

Grateful for all help and advice.

Thanks

 

[Guest]

I don't see anything wrong with your query based upon your explanation. What
is it doing wrong?

--Grey

 

[Llyllyll]

There's nothing wrong with the queries themselves, but I'd like to merge the
two into one SQL statement and am unsure on the syntax and construction of
the statement.

Thanks

 

[PC Datasheet]

I can't see what you mean by "dynamic SQL statement". Nothing here indicates
that your queries are dynamic.

Drop what you have. Create a new query based on tblConsec. Include the
fields, ReportP380,
Company, ConsecNo and ConsecNo (Yes, ConsecNo is used twice!). Click on the
Sigma (looks like E) button on the toolbar at the top of the screen. Set
the croteria for ReportP380 to False. Under the first ConsecNo, change
GroupBy to Max. Under the second ConsecNo, change GroupBy to Min. When you
run this query, you will get the Max and Min ConcecNos for all companies
where ReportP380 is false.

If you really need the SQL statement, open the query to SQL View and copy
the SQL.

You can get the Max and Min values for any company in one of two ways:
1. Use the DLookup function
2. Create a recordset from the query and use the findfirst method

 

[Guest]

That's fairly simple...

Take the first query...

SELECT tblConsec.RecordID, tblConsec.ConsecNo, tblConsec.Company,
tblConsec.Years, tblConsec.ReportP380
FROM tblConsec
WHERE (((tblConsec.ReportP380)=False))
ORDER BY tblConsec.RecordID

.... and cut-and-paste it into the the second query ...

SELECT Query1.Company, Min(Query1.ConsecNo) AS MinOfConsecNo,
Max(Query1.ConsecNo) AS MaxOfConsecNo
FROM Query1
GROUP BY Query1.Company
ORDER BY Min(Query1.ConsecNo);

.... at the spot where you list it in the from clause ...

FROM (put the query here in parenthesis like this) Query1

.... to yield something like ...

SELECT Query1.Company, Min(Query1.ConsecNo) AS MinOfConsecNo,
Max(Query1.ConsecNo) AS MaxOfConsecNo
FROM (SELECT tblConsec.RecordID, tblConsec.ConsecNo, tblConsec.Company,
tblConsec.Years, tblConsec.ReportP380
FROM tblConsec
WHERE (((tblConsec.ReportP380)=False))
ORDER BY tblConsec.RecordID) Query1
GROUP BY Query1.Company
ORDER BY Min(Query1.ConsecNo);

.... and you'll probably notice Jet will reformat it to something like
(notice the "[" and "]." that Jet replaces your parentheses with) ...

SELECT Query1.Company, Min(Query1.ConsecNo) AS MinOfConsecNo,
Max(Query1.ConsecNo) AS MaxOfConsecNo
FROM [SELECT tblConsec.RecordID, tblConsec.ConsecNo, tblConsec.Company,
tblConsec.Years, tblConsec.ReportP380
FROM tblConsec
WHERE (((tblConsec.ReportP380)=False))
ORDER BY tblConsec.RecordID]. Query1
GROUP BY Query1.Company
ORDER BY Min(Query1.ConsecNo);

.... and there you have it in one query.

--Grey