you tried to execute a query that does not include the specified expression as part of an aggregate 1

Triacona · May 1, 2018

Hi all!
Thanks for a great forum!

I have an issue that crops up when I try use a VBA function with a query.
It works fine UNITIL I add the Totals Row ( in design view ).

I had GroupBy and this gave me the titled ( above error ).

SQL error said:
you tried to execute a query that does not include the specified expression as part of an aggregate

So instead of GroupBy I used Expression in the Totals values for all fields, same error.
I also tried using a mixture of GroupBy and Expression ( on the VBA modified field ).
This gave an error:

data mismatch error said:
Data mismatch

Below is the first iteration of my SQL:

Code:

SELECT 
UNI7LIVE_LIPARTY.FULLNAME, 
Carriage([UNI7LIVE_LIPARTY].[ADDRESS]) AS LiPAddressCr, 
UNI7LIVE_LICASE.ACTCOMND AS DateGranted, 
UNI7LIVE_LICASE.LICDETAILS AS LicenceDetails, 
UNI7LIVE_LICASE.LICNTYPE AS LicenceType, 
UNI7LIVE_LICASE.REFVAL AS LicenseNo, 
OneLineReplace([UNI7LIVE_LICASE].[ADDRESS]) AS SiteAddressOL, 
UNI7LIVE_PR_LPI.ADDRESS, 
UNI7LIVE_PR_LPI.POSTCODE, 
First(UNI7LIVE_LIACTTIM.LOCATION) AS Location, 
UNI7LIVE_LIACTTIM.LIPERMIT, 
UNI7LIVE_LICASE.LISTAT, 
UNI7LIVE_LIPARTY.LIPTYTYPE, 
UNI7LIVE_CNAPPLLOG.REFVAL, 
UNI7LIVE_CNAPPLLOG.STATUS, 
UNI7LIVE_CNAPPLLOG.OPENDD, 
UNI7LIVE_PR_LPI.LOGICAL_STATUS AS PrLpiLogicalStat, 
UNI7LIVE_PR_BLPU.LOGICAL_STATUS AS BlpuLogicalStat

FROM 
((((UNI7LIVE_LICASE 
LEFT JOIN UNI7LIVE_LIPARTY 
ON UNI7LIVE_LICASE.KEYVAL = UNI7LIVE_LIPARTY.PKEYVAL) 
LEFT JOIN UNI7LIVE_CNAPPLLOG 
ON UNI7LIVE_LICASE.LIKEYVAL = UNI7LIVE_CNAPPLLOG.KEYVAL) 
LEFT JOIN UNI7LIVE_LIACTTIM 
ON UNI7LIVE_LICASE.KEYVAL = UNI7LIVE_LIACTTIM.PKEYVAL) 
LEFT JOIN UNI7LIVE_PR_BLPU 
ON UNI7LIVE_LICASE.PRKEYVAL = UNI7LIVE_PR_BLPU.KEYVAL) 
LEFT JOIN UNI7LIVE_PR_LPI 
ON UNI7LIVE_PR_BLPU.KEYVAL = UNI7LIVE_PR_LPI.PKEYVAL

GROUP BY 
UNI7LIVE_LIPARTY.FULLNAME, 
UNI7LIVE_LICASE.ACTCOMND, 
UNI7LIVE_LICASE.LICDETAILS, 
UNI7LIVE_LICASE.LICNTYPE, 
UNI7LIVE_LICASE.REFVAL, 
UNI7LIVE_PR_LPI.ADDRESS, 
UNI7LIVE_PR_LPI.POSTCODE, 
UNI7LIVE_LIACTTIM.LIPERMIT, 
UNI7LIVE_LICASE.LISTAT, 
UNI7LIVE_LIPARTY.LIPTYTYPE, 
UNI7LIVE_CNAPPLLOG.REFVAL, 
UNI7LIVE_CNAPPLLOG.STATUS, 
UNI7LIVE_CNAPPLLOG.OPENDD, 
UNI7LIVE_PR_LPI.LOGICAL_STATUS, 
UNI7LIVE_PR_BLPU.LOGICAL_STATUS
HAVING 
(((UNI7LIVE_LICASE.LICNTYPE) In ("PREMIS","CLUB")) 
AND ((UNI7LIVE_LIACTTIM.LIPERMIT)="ALCRET") 
AND ((UNI7LIVE_LICASE.LISTAT)="5_ISS") 
AND ((UNI7LIVE_LIPARTY.LIPTYTYPE) Not In ("DPS","LIAGNT","LIREP")) 
AND ((UNI7LIVE_PR_LPI.LOGICAL_STATUS) In ("1")));

Here is my second attempt:

Code:

SELECT 
UNI7LIVE_LIPARTY.FULLNAME, 
Carriage([UNI7LIVE_LIPARTY].[ADDRESS]) AS LiPAddressCr, 
UNI7LIVE_LICASE.ACTCOMND AS DateGranted, 
UNI7LIVE_LICASE.LICDETAILS AS LicenceDetails, 
UNI7LIVE_LICASE.LICNTYPE AS LicenceType, 
UNI7LIVE_LICASE.REFVAL AS LicenseNo, 
OneLineReplace([UNI7LIVE_LICASE].[ADDRESS]) AS SiteAddressOL, 
UNI7LIVE_PR_LPI.ADDRESS, 
UNI7LIVE_PR_LPI.POSTCODE, 
UNI7LIVE_LIACTTIM.LOCATION AS Location, 
UNI7LIVE_LICASE.CPTRADEAS AS TradingAs, 
UNI7LIVE_LIACTTIM.LIPERMIT, 
UNI7LIVE_LICASE.LISTAT, 
UNI7LIVE_LIPARTY.LIPTYTYPE, 
UNI7LIVE_CNAPPLLOG.REFVAL, 
UNI7LIVE_CNAPPLLOG.STATUS, 
UNI7LIVE_CNAPPLLOG.OPENDD, 
UNI7LIVE_PR_LPI.LOGICAL_STATUS AS PrLpiLogicalStat, 
UNI7LIVE_PR_BLPU.LOGICAL_STATUS AS BlpuLogicalStat

FROM 
((((UNI7LIVE_LICASE 
LEFT JOIN UNI7LIVE_LIPARTY 
ON UNI7LIVE_LICASE.KEYVAL = UNI7LIVE_LIPARTY.PKEYVAL) 
LEFT JOIN UNI7LIVE_CNAPPLLOG 
ON UNI7LIVE_LICASE.LIKEYVAL = UNI7LIVE_CNAPPLLOG.KEYVAL) 
LEFT JOIN UNI7LIVE_LIACTTIM 
ON UNI7LIVE_LICASE.KEYVAL = UNI7LIVE_LIACTTIM.PKEYVAL) 
LEFT JOIN UNI7LIVE_PR_BLPU 
ON UNI7LIVE_LICASE.PRKEYVAL = UNI7LIVE_PR_BLPU.KEYVAL) 
LEFT JOIN UNI7LIVE_PR_LPI 
ON UNI7LIVE_PR_BLPU.KEYVAL = UNI7LIVE_PR_LPI.PKEYVAL

GROUP BY
UNI7LIVE_LIPARTY.FULLNAME, 
UNI7LIVE_LICASE.ACTCOMND, 
UNI7LIVE_LICASE.LICDETAILS, 
UNI7LIVE_LICASE.LICNTYPE, 
UNI7LIVE_LICASE.REFVAL, 
OneLineReplace([UNI7LIVE_LICASE].[ADDRESS]), 
UNI7LIVE_PR_LPI.ADDRESS, 
UNI7LIVE_PR_LPI.POSTCODE, 
UNI7LIVE_LIACTTIM.LOCATION, 
UNI7LIVE_LICASE.CPTRADEAS, 
UNI7LIVE_LIACTTIM.LIPERMIT, 
UNI7LIVE_LICASE.LISTAT, 
UNI7LIVE_LIPARTY.LIPTYTYPE, 
UNI7LIVE_CNAPPLLOG.REFVAL, 
UNI7LIVE_CNAPPLLOG.STATUS, 
UNI7LIVE_CNAPPLLOG.OPENDD, 
UNI7LIVE_PR_LPI.LOGICAL_STATUS, 
UNI7LIVE_PR_BLPU.LOGICAL_STATUS

HAVING 
(((UNI7LIVE_LICASE.LICNTYPE) In ("PREMIS","CLUB")) 
AND ((UNI7LIVE_LIACTTIM.LIPERMIT)="ALCRET") 
AND ((UNI7LIVE_LICASE.LISTAT)="5_ISS") 
AND ((UNI7LIVE_LIPARTY.LIPTYTYPE) Not In ("DPS","LIAGNT","LIREP")) 
AND ((UNI7LIVE_PR_LPI.LOGICAL_STATUS) In ("1")));

Both gave the same errors.
I have trawled the internet and none come close. [sadeyes]

What am I doing wrong?
Thanks for our forthcoming help [smile]

Thank you,

Kind regards

Triacona

Andrzejek · May 1, 2018

What is [tt]Carriage, OneLineReplace[/tt], and [tt]First[/tt]? Are those UDF (User Defined Function)?
If so, could you show the code?

---- Andy

There is a great need for a sarcasm font.

MajP · May 1, 2018

What are you trying to accomplish with your aggregate query? That does not look like a viable aggregate query, it looks just like a big select query.
Normally if you want to have a bunch of details and then some aggregate date you would make the detail query and then link it to a smaller aggregate query. Whatever, you are trying to do should likely be done in multiple queries or a subquery, but I cannot figure it out from what you show.

Triacona · May 2, 2018

Hi all,

Thanks for your replies [bigsmile]

I am trying to output licenses in a query that outputs to an excel spreadsheet ( got the buttons that does that) .
It is a list of licences from a db ( oracle ), there are multiple details associated with these licences that I want to output, hence the multiple linked tables.
I am trying ( as a test ) just to run the query within Access.

There are 2 user defined functions ( OneLineReplace and Carriage ) and the First is a built in Access Query Function.

Here is the VBA for the OneLineReplace code and Carriage code:

Code:

Function OneLineReplace(strText As Variant) As String
' Replace % with the character you want to substitute.
    If Trim(strText & "") = "" Then
        OneLineReplace = ""
    Else
        OneLineReplace = Replace(strText, Chr(13), ", ")
    End If
End Function

Code:

    Function Carriage(strText As String) As String
         ' Replace % with the character you want to substitute.
        If Trim(strText & "") = "" Then
            Carriage = "THERE IS A BLANK ADDRESS"
        Else
         Carriage = Replace(strText, vbCr, vbCrLf)
        End If
      End Function

Thank you,

Kind regards

Triacona

Andrzejek · May 2, 2018

So you don't really have any aggregate in your Select statement, like Sum, Count, Max, Min, etc. Your statement:

Code:

SELECT 
UNI7LIVE_LIPARTY.FULLNAME, [blue]
Carriage([UNI7LIVE_LIPARTY].[ADDRESS]) AS LiPAddressCr, [/blue]
UNI7LIVE_LICASE.ACTCOMND AS DateGranted, 
UNI7LIVE_LICASE.LICDETAILS AS LicenceDetails, 
UNI7LIVE_LICASE.LICNTYPE AS LicenceType, 
UNI7LIVE_LICASE.REFVAL AS LicenseNo, [blue]
OneLineReplace([UNI7LIVE_LICASE].[ADDRESS]) AS SiteAddressOL, [/blue]
UNI7LIVE_PR_LPI.ADDRESS,

could be simply written as:

Code:

SELECT 
UNI7LIVE_LIPARTY.FULLNAME, [blue]
Replace([UNI7LIVE_LIPARTY].[ADDRESS], vbCr, vbCrLf) AS LiPAddressCr, [/blue]
UNI7LIVE_LICASE.ACTCOMND AS DateGranted, 
UNI7LIVE_LICASE.LICDETAILS AS LicenceDetails, 
UNI7LIVE_LICASE.LICNTYPE AS LicenceType, 
UNI7LIVE_LICASE.REFVAL AS LicenseNo, [blue]
Replace([UNI7LIVE_LICASE].[ADDRESS], Chr(13), ", ") AS SiteAddressOL, [/blue]
UNI7LIVE_PR_LPI.ADDRESS,

maybe with a simple IIF in the second Replace to accommodate "THERE IS A BLANK ADDRESS" case.

So no need to GROUP BY because there is nothing to group by....

---- Andy

There is a great need for a sarcasm font.

MajP · May 2, 2018

I concur with Andy on that I see no purpose for the aggregate. The only thing I see is you trying to get the first location, but that does not seem to make sense.
Your first error message is exactly what it says because your aggregate is not correct. The second is because this function will fail if the field is empty

Code:

Function Carriage(strText As String) As String
         ' Replace % with the character you want to substitute.
        If Trim(strText & "") = "" Then
            Carriage = "THERE IS A BLANK ADDRESS"
        Else
         Carriage = Replace(strText, vbCr, vbCrLf)
        End If
 End Function

The function is trying to check for a null, but you cannot pass one to it since the argument is a string. Thus a data type mismatch. It will error out before the check. Should read

Code:

Function Carriage(varText As Variant) As String
         ' Replace % with the character you want to substitute.
        If Trim(varText & " ") = "" Then
            Carriage = "THERE IS A BLANK ADDRESS"
        Else
         Carriage = Replace(varText, vbCr, vbCrLf)
        End If
 End Function

Andrzejek · May 2, 2018

With all that said, you may just try your original query (with a little modifications/eliminations):

Code:

SELECT 
UNI7LIVE_LIPARTY.FULLNAME, 
Carriage([UNI7LIVE_LIPARTY].[ADDRESS]) AS LiPAddressCr, 
UNI7LIVE_LICASE.ACTCOMND AS DateGranted, 
UNI7LIVE_LICASE.LICDETAILS AS LicenceDetails, 
UNI7LIVE_LICASE.LICNTYPE AS LicenceType, 
UNI7LIVE_LICASE.REFVAL AS LicenseNo, 
OneLineReplace([UNI7LIVE_LICASE].[ADDRESS]) AS SiteAddressOL, 
UNI7LIVE_PR_LPI.ADDRESS, 
UNI7LIVE_PR_LPI.POSTCODE, 
First(UNI7LIVE_LIACTTIM.LOCATION) AS Location, 
UNI7LIVE_LIACTTIM.LIPERMIT, 
UNI7LIVE_LICASE.LISTAT, 
UNI7LIVE_LIPARTY.LIPTYTYPE, 
UNI7LIVE_CNAPPLLOG.REFVAL, 
UNI7LIVE_CNAPPLLOG.STATUS, 
UNI7LIVE_CNAPPLLOG.OPENDD, 
UNI7LIVE_PR_LPI.LOGICAL_STATUS AS PrLpiLogicalStat, 
UNI7LIVE_PR_BLPU.LOGICAL_STATUS AS BlpuLogicalStat
FROM UNI7LIVE_LICASE 
LEFT JOIN UNI7LIVE_LIPARTY 
ON UNI7LIVE_LICASE.KEYVAL = UNI7LIVE_LIPARTY.PKEYVAL
LEFT JOIN UNI7LIVE_CNAPPLLOG 
ON UNI7LIVE_LICASE.LIKEYVAL = UNI7LIVE_CNAPPLLOG.KEYVAL
LEFT JOIN UNI7LIVE_LIACTTIM 
ON UNI7LIVE_LICASE.KEYVAL = UNI7LIVE_LIACTTIM.PKEYVAL
LEFT JOIN UNI7LIVE_PR_BLPU 
ON UNI7LIVE_LICASE.PRKEYVAL = UNI7LIVE_PR_BLPU.KEYVAL
LEFT JOIN UNI7LIVE_PR_LPI 
ON UNI7LIVE_PR_BLPU.KEYVAL = UNI7LIVE_PR_LPI.PKEYVAL
[s]GROUP BY 
UNI7LIVE_LIPARTY.FULLNAME, 
UNI7LIVE_LICASE.ACTCOMND, 
UNI7LIVE_LICASE.LICDETAILS, 
UNI7LIVE_LICASE.LICNTYPE, 
UNI7LIVE_LICASE.REFVAL, 
UNI7LIVE_PR_LPI.ADDRESS, 
UNI7LIVE_PR_LPI.POSTCODE, 
UNI7LIVE_LIACTTIM.LIPERMIT, 
UNI7LIVE_LICASE.LISTAT, 
UNI7LIVE_LIPARTY.LIPTYTYPE, 
UNI7LIVE_CNAPPLLOG.REFVAL, 
UNI7LIVE_CNAPPLLOG.STATUS, 
UNI7LIVE_CNAPPLLOG.OPENDD, 
UNI7LIVE_PR_LPI.LOGICAL_STATUS, 
UNI7LIVE_PR_BLPU.LOGICAL_STATUS
HAVING [/s] WHERE
    UNI7LIVE_LICASE.LICNTYPE In ("PREMIS","CLUB")
AND UNI7LIVE_LIACTTIM.LIPERMIT = "ALCRET"
AND UNI7LIVE_LICASE.LISTAT     = "5_ISS"
AND UNI7LIVE_LIPARTY.LIPTYTYPE Not In ("DPS","LIAGNT","LIREP")
AND UNI7LIVE_PR_LPI.LOGICAL_STATUS In ("1");

---- Andy

There is a great need for a sarcasm font.

Triacona · May 2, 2018

Thank you both so much! [thumbsup]

MajP as per usual you are very perceptive !

I fixed the functions and it runs.

The only issue now is that I want to run the query and limit the records to the single Licence Number, so remove duplicates.
I have tried the First() but this seems to not work...
If I add it to the LicenseNo, it does nothing...
If I add it to one of the fields that links to another table ( one to many relationship ) it then curbs the number drastically ( 472 to 240 )
It should be 300 without duplicates.
So I only want the main tables records ( LICASE ) number to be shown with the other tables' fields being used to filter results, not important for display.

How would I go about doing this?

Thanks again folks for all your help [2thumbsup]

Thank you,

Kind regards

Triacona

Andrzejek · May 2, 2018

To "remove duplicates" use Select DISTINCT, when ALL fields are duplicated.

---- Andy

There is a great need for a sarcasm font.

MajP · May 2, 2018

If I understand, you have duplicated data in your main table: UNI7LIVE_LICASE.
If that is the case I would get the distinct records first in its own query, and then join to the details afterwards. This would be a more efficient.

qryLicences

Code:

SELECT DISTINCT
 UNI7LIVE_LICASE.ACTCOMND AS DateGranted, 
 UNI7LIVE_LICASE.LICDETAILS AS LicenceDetails, 
 UNI7LIVE_LICASE.LICNTYPE AS LicenceType, 
 UNI7LIVE_LICASE.REFVAL AS LicenseNo, 
 OneLineReplace([UNI7LIVE_LICASE].[ADDRESS]) AS SiteAddressOL, 
 UNI7LIVE_LICASE.LISTAT, 
FROM 
 UNI7LIVE_LICASE

Check the count on this table to see if you have the expected number
Then use qryLicenses in another query to bring in the related fields. Why do you have duplicated data for a license?

Triacona · May 3, 2018

Dear All,

Thanks again for all your help [bigsmile]

The licences are duplicating as a result of the table joins, so for example, UNI7LIVE_LIACTTIM has a list of locations for each Licence, so has many records .'. duplicates the Licence entry in more rows displaying more than one location for each licence.

I hope that makes sense.

I have tried the select distinct, and still get the same no. that's why I tried the FIRST command on the Location field...

Thanks for all the forthcoming help.

Thank you,

Kind regards

Triacona

MajP · May 3, 2018

Okay this makes more sense. There are no duplicate information being stored, your query is just showing a record for every license and license location.

So the location table UNI7LIVE_LIACTTIM is a child table where multiple locations relate back to a license. I am guessing it looks someting like

Code:

[tt]PKeyVal	LIPERMIT LOCATION
1	A	 MD
1	A	 VA
2	A	 SC
2	B	 NC[/tt]

By that I mean the field PKeyval is a foreign key relating back to the license table KeyVal field. So License 1 has two locations (MD and VA) and maybe two permits. You want to return just one of the records for each location. I guess a license can have multiple locations, but you only care about 1. So which location do you care about? If you say the first, what does that mean? if you apply FIRST against a text field it will return the alphabetic first record. If you mean the first location entered then there is no way to return that unless there is a field such as a date field or an autoID to sort on. So which record for a PKEYVAL do you want? You may have to show other fields depending on what you want. If you can determine that, then you will need to build a query on LIACTTIM first. Then join this query to your bigger select query, and thus not creating multiple records per license.

MajP · May 3, 2018

Here would be an example of getting the first location entered per license. This assumes that the location table has an autonumber field or a date time stamp or some other way to order the records

Code:

[tt]
LocationID	PKeyVal	LIPERMIT LOCATION
1	        1	A	 MD
2	        1	A	 VA
3	        2	A	 SC
4	        2	B	 NC[/tt]

Code:

SELECT 
 LocationID, 
 PKeyVal, 
 LIPERMIT, 
 LOCATION
FROM 
 UNI7LIVE_LIACTTIM
WHERE 
 LocationID In (SELECT First(LocationID) AS FirstEntered FROM UNI7LIVE_LIACTTIM GROUP BY PKeyVal)

Code:

[tt]
LocationID	PKeyVal	LIPERMIT LOCATION
1	         1	A	 MD
3	         2	A	 SC[/tt]

Triacona · May 3, 2018

Awesome thanks! MajP, what you have looks right, I will give it a go! [2thumbsup]

Thank you,

Kind regards

Triacona

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

you tried to execute a query that does not include the specified expression as part of an aggregate 1

Triacona

Technical User

Andrzejek

Programmer

MajP

Technical User

Triacona

Technical User

Andrzejek

Programmer

MajP

Technical User

Andrzejek

Programmer

Triacona

Technical User

Andrzejek

Programmer

MajP

Technical User

Triacona

Technical User

MajP

Technical User

MajP

Technical User

Triacona

Technical User

Similar threads

Part and Inventory Search

Sponsor