Limit query results to one instance of a company

[Bobp3114]

I have a need to limit the number of times a company comes up in a query result.
I have two tables:
tblStationData,

and tblSerialNumbers

I have tried
SELECT DISTINCT tblStationData.StationID, tblStationData.[Station Name], tblSerialNumbers.SerialNumber
FROM tblStationData INNER JOIN tblSerialNumbers ON tblStationData.StationID = tblSerialNumbers.StationID
WHERE (((tblSerialNumbers.SerialNumber) Is Not Null))
ORDER BY tblStationData.StationID;

But I get:

I only want to see each company (eg 2AIR ) once only regardless of how many records are associated with it.
Any help would be appreciated
Bob

 

[jdraw]

Which one do you want? If you only want the name, why include serialnumber?
Perhaps you ould decribe exactly what you want as a result -mock up some data if necessary.

 

[The_Doc_Man]

If you can have multiple serial numbers for the same company AND DON'T CARE, then leave out Serial number from the DISTINCT query altogether. If you have to see a serial number and they are not unique, you have to give Access some other criteria for which record to select. Otherwise it is going to give you ALL of the records. This is a case of "cant have your cake and eat it too."

 

[Bobp3114]

Which one do you want? If you only want the name, why include serialnumber?
Perhaps you ould decribe exactly what you want as a result -mock up some data if necessary.

Hi
I want any all companies that have a serial number associated with it, but only one instance of each of these companies

 

[Bobp3114]

If you can have multiple serial numbers for the same company AND DON'T CARE, then leave out Serial number from the DISTINCT query altogether. If you have to see a serial number and they are not unique, you have to give Access some other criteria for which record to select. Otherwise it is going to give you ALL of the records. This is a case of "cant have your cake and eat it too."

Hi
I want any all companies that have a serial number associated with it, but only one instance of each of these companies

 

[The_Doc_Man]

I repeat: If you want the serial number to exist but don't care what it is, then remove the serial number from your SQL SELECT clause. The SQL you showed us should work OK if you remove the serial number field from the SELECT statement. Since the serial numbers are not unique (which is the point of your question, after all), what good is it to show ANY serial number? Your query has that WHERE clause to reject records that lack a serial number, so any answer you get should match your requirements as you expressed them in words.

Is there some other part of the question you have not revealed?

 

[arnelgp]

SELECT tblStationData.StationID, tblStationData.[Station Name], T.SN As SerialNumber
FROM tblStationData INNER JOIN (SELECT StationID, First(SerialNumber) As SN From tblSerialNumbers
WHERE ((tblSerialNumbers.SerialNumber) Is Not Null) Group By StationID) AS T ON
Table2.StationID = T.StationID

 

[Bobp3114]

I repeat: If you want the serial number to exist but don't care what it is, then remove the serial number from your SQL SELECT clause. The SQL you showed us should work OK if you remove the serial number field from the SELECT statement. Since the serial numbers are not unique (which is the point of your question, after all), what good is it to show ANY serial number? Your query has that WHERE clause to reject records that lack a serial number, so any answer you get should match your requirements as you expressed them in words.

Is there some other part of the question you have not revealed?

Thanks.. works well..Excuse my misunderstandig of your previous comment

 

[The_Doc_Man]

No problem. Sometimes I am accused of providing unclear comments, so if I was a bit unclear, please excuse my communication skills.

Glad I could help.

 

[Pat Hartman]

You don't need the subselect. You just need to eliminate the serial number from the select clause as Doc said several times..

SELECT DISTINCT tblStationData.StationID, tblStationData.[Station Name]
FROM tblStationData INNER JOIN tblSerialNumbers ON tblStationData.StationID = tblSerialNumbers.StationID
WHERE (((tblSerialNumbers.SerialNumber) Is Not Null))
ORDER BY tblStationData.StationID;

 

[Bobp3114]

You don't need the subselect. You just need to eliminate the serial number from the select clause as Doc said several times..

SELECT DISTINCT tblStationData.StationID, tblStationData.[Station Name]
FROM tblStationData INNER JOIN tblSerialNumbers ON tblStationData.StationID = tblSerialNumbers.StationID
WHERE (((tblSerialNumbers.SerialNumber) Is Not Null))
ORDER BY tblStationData.StationID;

Thanks Pat I have done as advised and all s well