Multiple records from a sub query

[Mr Banan Qry]

Hi!

I've got a problem that I'm to frustrate to solve or let go. Was hoping someone here could help me with it. I do hope you'll have some patience with me if this is very basic stuff that I shouldn't be bothering you with (I've just recently begun to work with Access and SQL).

Anyway I'm trying to use a sub query within a query (they all stem from one and the same table). It works fine when I save the first query and then query on it using an inner join (between the query and the original table). However I want to write everything within one SQL statement and then it fails (or rather I fail). I don't want to refer to the query with just the name of the saved query.
I'm thinking this effort would be appreciated by any developer having to manage my VBA code (which is outside of this database).

The table consists of these values:
Facility ID
Measuring Device1 ID
Measuring Device2 ID
All of these fields may have a value that occurs several times. I want to check the number of occurrences for Measuring Device1 ID and the number of occurrences for Measuring Device2 ID (provided that Facility ID is the same).
In the result I want Facility ID(grouped by) and the highest values for these two counts.

I'm stuck at getting the highest value of any measuring device for each unique Facility ID.
This (nr 2) is the query that actually does work but I've saved the first one and I do refer to that one in the second one:

1) Query Saved as "Example Qry"
SELECT DEVICE_INFO.Facility_ID, Count(DEVICE_INFO.Mea_Dev1_ID) AS CountOfMea_Dev1_ID, DEVICE_INFO.Mea_Dev1_ID
FROM DEVICE_INFO
GROUP BY DEVICE_INFO.Facility_ID, DEVICE_INFO.Mea_Dev1_ID
ORDER BY DEVICE_INFO.Mea_Dev1_ID DESC;
2) Query
SELECT DEVICE_INFO.Facility_ID, Max([Example Qry].CountOfMea_Dev1_ID) AS MaxOfCountOfMea_Dev1_ID
FROM DEVICE_INFO INNER JOIN [Example Qry] ON DEVICE_INFO.Facility_ID = [Example Qry].Facility_ID
GROUP BY DEVICE_INFO.Facility_ID;

This is was my first effort to get it to work:
SELECT DEVICE_INFO.Facility_ID, (SELECT Count(DEVICE_INFO.Mea_Dev1_ID) AS CountOfMea_Dev1_ID
FROM DEVICE_INFO AS DEVICE_INFO_ALIAS
GROUP BY DEVICE_INFO_ALIAS.Facility_ID, DEVICE_INFO_ALIAS.Mea_Dev1_ID
HAVING (([DEVICE_INFO].[Facility_ID]=[DEVICE_INFO_ALIAS].[Facility_ID]))
ORDER BY Count(DEVICE_INFO.Mea_Dev1_ID) DESC) as NumOfOccDev1
FROM DEVICE_INFO

When i run this query i end up with a runtime error (3354). I do understand what I’m doing wrong (trying to insert several records into one, as there might be several different counts for one Facility_ID)

I then tried to add Max() on Count() which would make Facility_ID unique, but it didn't work. I'm guessing I'm only allowed to use one of these functions per query. Even if would use Max() on a nested version of the first query i would still get multiple records from the first query. If anyone could translate the first two queries into one it would be greatly appreciated

 

[Pat Hartman]

Welcome Aboard
You have to use a correlated subselect. That means that the subquery needs to reference a field in the main query so that a value is returned for each main form record. You seem to want a count of devices by facility but your main query only selects facility. It needs to also select device so the subquery can count separately for each device.

I should add that Jet/ACE do not optimize subqueries efficiently and you will find this method to be significantly slower than joining to the totals query. Bite the bullet, go with the flow, create the second query, and move on.

 

[Mr Banan Qry]

Thanks Pat!

The overall aim with the query is to find out if the first device ID (which is the device label) is unique or if there are several devices (the second ID is uniqe for every device) which use the same label.

Pending on the outcome i'll use different scripts for uploading some processed data.