Complex SQL Statement

[HK]

I need to view all hosts and their child records, (called date records)
who have more than a given number of unique date records. I've been
working on this all day and still haven't figured it out. Here is the
basics of how it should work:

Query the child table, DateRecords, to return unique records. Here I
exclude the DateID, otherwise all of them would be unique. I'm trying
to exclude Date records that have the same date on them.

Next I need to count how many records returned for each host account
number.

If the amount of records returned is = varUserInput, then I need to
query the database for all host and child records that have that
account number.

How can I do this?

I've tried nested SQL statements with the count feature but I have had
no luck. Here is an example:

SELECT tblHosts.*, tblHDate.*
FROM tblHosts INNER JOIN tblHDate ON tblHosts.HostID = tblHDate.HostID
WHERE tblHosts.HostID in (select a.HostID from (SELECT DISTINCT
tblHosts.HostID
FROM tblHosts INNER JOIN tblHDate ON tblHosts.HostID = tblHDate.HostID)
a group by a.HostID having count(a.HostID)= " & intUserInput & ")

This returns no matching records.

I know how to query for the unique records but I don't know how to
count them by account number and return the account number to the next
query if the count = varUserInput.

Any suggestions?

 

[Wolfgang Kais]

Hello HK.

I need to view all hosts and their child records, (called date
records) who have more than a given number of unique date
records. I've been working on this all day and still haven't
figured it out. Here is the basics of how it should work:

Query the child table, DateRecords, to return unique records.
Here I exclude the DateID, otherwise all of them would be unique.
I'm trying to exclude Date records that have the same date on them.

Next I need to count how many records returned for each host
account number.

If the amount of records returned is = varUserInput, then I need
to query the database for all host and child records that have that
account number.

How can I do this?

I've tried nested SQL statements with the count feature but I have
had no luck. Here is an example:

SELECT tblHosts.*, tblHDate.*
FROM tblHosts INNER JOIN tblHDate
ON tblHosts.HostID = tblHDate.HostID
WHERE tblHosts.HostID in (select a.HostID
from (SELECT DISTINCT tblHosts.HostID
FROM tblHosts INNER JOIN tblHDate ON tblHosts.HostID = tblHDate.HostID)
a group by a.HostID having count(a.HostID)= " & intUserInput & ")

This returns no matching records.

I know how to query for the unique records but I don't know how to
count them by account number and return the account number to the
next query if the count = varUserInput.

Any suggestions?

Your "select distinct" subquery returns only 1 record, because the fieldlist
contains only 1 field.
Also: You don't need tblHosts in that subquery because HostID is also
contained in tblHDates. So tha query should like this:

SELECT tblHosts.*, tblHDate.* FROM tblHosts INNER JOIN tblHDate
ON tblHosts.HostID = tblHDate.HostID WHERE tblHosts.HostID in
(select a.HostID from (SELECT DISTINCT HostID, Date FROM tblHDate) AS a
group by a.HostID having count(a.HostID)=" & intUserInput & ")