Combining queries in an adp

[Guest]

Sorry to post so soon with another question, but I'm getting desperate! lol.

In the same adp I mentioned earlier, I am trying to recreate a query used by
a former developer (without benefit of documentation) that counts instances
of production runs per date. What I wound up with is 2 queries, one
referencing the other. While it works ok, I'd rather combine the SQL into a
single query.

I know how to create count queries this way in an mdb, but the adp UI and
syntax are throwing me for a loop.

Here's what I have:

QUERY 1 (AuditFailsByESNLine_View)

SELECT TOP 100 PERCENT ESNShiftDate, LineID, Shift
FROM dbo.HourlyESNVolume
WHERE (Shift = 1)
GROUP BY ESNShiftDate, LineID, Shift
HAVING (ESNShiftDate IS NOT NULL)
ORDER BY ESNShiftDate


This creates a list of valid Dates for Shift 1 aggregated by Line # that was
run. Next I need to get the counts for each date, so I reference that first
query in another query (in essence I'm actually counting the number of unique
production lines):

QUERY 2 (AuditFailsByESNLineCount_View)

SELECT DISTINCT TOP 100 PERCENT COUNT(ESNShiftDate) AS Num, ESNShiftDate
FROM dev.AuditFailsByESNLine_View
GROUP BY ESNShiftDate


Now, as I said, this gives me the result I want, which is a row-by-row list
of the total number of dates per shift/line followed by the distinct date.

Any ideas of how I can combine this into a single query in an adp? That's
how I understand the original developer did it. The approach I use in an mdb
just does not work. If nothing else, I want to do this to expand my
understanding of SQL.

Thanks,

Randall Arnold

 

[Sylvain Lafontaine]

On SQL-Server, enclose the subquery with parenthesis and add a name after
it; something like:

SELECT DISTINCT TOP 100 PERCENT COUNT(SQ.ESNShiftDate) AS Num,
SQ.ESNShiftDate

FROM (
SELECT TOP 100 PERCENT ESNShiftDate, LineID, Shift
FROM dbo.HourlyESNVolume
WHERE (Shift = 1)
GROUP BY ESNShiftDate, LineID, Shift
HAVING (ESNShiftDate IS NOT NULL)
ORDER BY ESNShiftDate

) as SQ

GROUP BY SQ.ESNShiftDate

You must design your stored procedure or your view in Text mode with ADP
because its visual tools are unable to create this type of query.

Another possibility would be to create 2 separates Views on SQL-Server.

 

[Guest]

Thanks Sylvain. I currently do have it in 2 views but was curious on how to
make it one. Your explanation about the adp visual tools certainly explains
why I wasn't able to make it work.

Randall