Queries :: Union Within Subquery - Specified Expression As Part Of Aggregate Function Not Included
May 27, 2014
I've created the following but it keeps coming up with the error message You tried to execute a query that does not include the specified expression 'ICE Team' as part of an aggregate function.
SELECT ztSub.[Master Sheet].[ICE Team], ztSub.[date], Count(ztSub.[Count])
FROM (SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count]
FROM [Master Sheet]
UNION
SELECT [Master Sheet].[ICE Team],[Master Sheet].[Date retasked to?] AS [date], Count ([Master Sheet]![Date retasked to?]) AS [Count]
FROM [Master Sheet] ) AS ztSub
GROUP BY ztSub.[Master Sheet].[ICE Team];
View Replies
ADVERTISEMENT
Oct 10, 2013
I have a query that will be assisting me find pricing based upon quantity ranges of specific equipment for a given FY.
I'm no access expert, and I keep getting "You tried to execute a query that does not include the specified expression...as part of an aggregate function".
I have tried several things, but cannot seem to figure this one out.
The SQL of my query is as follows:
SELECT IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])
Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz(Sum([Current Orders]![Quantity]))+
Nz([forms]![04c Test Query for ROM Support]![Quantity])
Between 5 And 10,[04b Pricing Products]![05-10],
IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])
[Code] .....
View 14 Replies
View Related
Mar 25, 2015
I have a list of product sales for various regions.How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;
I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)
View 3 Replies
View Related
Mar 18, 2013
I am trying to aggregate IIF functions to give me the total in separate columns (fields) according to the criteria applied however I am getting an error message "You tried to execute a query that does not include the specific expression
'IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0)'
as part of an aggregate function, and I cannot find why, The query is as follows:
SELECT
Tbl_Advisor_raw.Month,
Sum(Tbl_Advisor_raw.ValuePay) AS ValuePay,
Sum(Tbl_Advisor_raw.Salary) AS Salary,
Sum(Tbl_Advisor_raw.NetRevenue) AS NetRevenue,
IIf(ValuePay>0,(ValuePay/Salary),0) AS pcSpend,
[Code] .....
View 9 Replies
View Related
Dec 10, 2013
I have a SQL query to gather data from a number of tables (balances, accounts, currencies)
Quote:
SELECT [tblBalances].[BalanceDate], [tblAccounts].[AccountNumber], [tblCurrencies].[Ccy], [tblBalances].[Amount], ([tblBalances].[Amount]*[tblRates].[FXRate]) AS AmountUSD
FROM (([tblBalances] INNER JOIN [tblAccounts] ON [tblBalances].[AccountID]=[tblAccounts].[AccountID]) INNER JOIN [tblCurrencies] ON [tblBalances].[CcyID]=[tblCurrencies].[CcyID]) INNER JOIN [tblRates] ON ([tblBalances].[BalanceDate]=[tblRates].[RateDate]) AND ([tblBalances].[CcyID]=[tblRates].[CcyID])
WHERE BalanceDate = #12/10/2013#
How do I add 'AmountUSD' to the WHERE clause (such that I can only return records above or below a certain value, for example)
Along the lines of :
Quote:
WHERE BalanceDate = #12/10/2013# AND AmountUSD>1000
I know it's an issue with referring to aggregated functions in the WHERE clause and you're supposed to use HAVING instead
View 12 Replies
View Related
Feb 27, 2014
how to query data in my database based on a number of different criteria.I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors.In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.
I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.
I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.
View 4 Replies
View Related
Dec 3, 2013
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code:
SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE,
(SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL]
WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID
ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
[code]....
View 6 Replies
View Related
Aug 29, 2013
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA
View 9 Replies
View Related
Oct 7, 2013
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
View 7 Replies
View Related
Dec 12, 2013
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel Here is what i am trying to do The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID: aaaa
Date
01012013
02012013
03012013
CustID: bbbb
01012013
02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for
CustID: aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID: bbbb : bbbb
01012013 : bbbb
02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF( B6<>"",B6,C5)))but in access i am stucked.
View 14 Replies
View Related
Jun 16, 2005
Hi All,
I am using the following query to retrieve sum of gross total and nettotal :
SELECT BOOKINGNO, AGENT, CLIENT, DATE, ARRIVAL_DATE, NUMOFDAYS, GROSSTOTAL, TOTAL, SUM(GROSSTOTAL) AS SUMGROSS, SUM(TOTAL) AS SUMTOTAL FROM BOOKINGS
MONTH(ARRIVAL_DATE) = PICKMONTH
AND
YEAR(ARRIVAL_DATE) = PICKYEAR
where PICKMONTH and PICKYEAR are the pickup month and year of the travel.
The above query doesn't execute and give error as following
You tried to execute a query that does not include the specified expression 'ACH_REF' as part of an aggregate function.
Please help me
Jigs :confused:
View 7 Replies
View Related
Jun 26, 2006
Hi,
Can anyone please help me out with the IIF concept of MS-ACCESS.
I have a table with the following data:
Name Age Place Dept
AAA 13 Mumbai Accounting
BBB 12 Pune Banking
CCC 15 Delhi Finance
DDD 30 Mumbai Accounting
EEE 25 Delhi Finance
Now i need a query output like
Expected output:
---------------
Dept Total-count Mumbai Pune Delhi
Accounting 2 2 0 0
Banking 1 0 1 0
Finance 2 1 0 1
Now i m using the query:
*******************
SELECT [Dept], sum(test-table.dept) AS ['total count'], Sum(IIf([place]="Mumbai",1,0)) AS Mumbai, Sum(IIf([place]="Pune",1,0)) AS Pune, Sum(IIf([place]="Delhi",1,0)) AS Delhi
FROM [Test-Table]
GROUP BY [Dept];
*******************
and the output which i am getting is:
Dept Total-count Mumbai Pune Delhi
Accounting 2 0 0
Banking 0 1 0
Finance 1 0 1
can anyone let me know as to what change should be there in the query so that i get the correct values in Total-count field with the sum of horizontal counts?
Thanks,
SK.
View 4 Replies
View Related
Mar 21, 2013
I am receiving this error when i try to run a query:
"You tried to execute a query that doesn't include specified expression as an aggregated function"
There are two images attached which show the SQL view, the error and the design view.
View 11 Replies
View Related
Jun 25, 2014
I have this linked table query from a OBDC and I need to be able to filter out specific dates in that query. The dates in the table were in text format and I converted the dates using the CDate function. I wanted to filter the query to a single date and always I get the Datatype mismatch in criteria expression error.
However, filtering dates does work only when there are other specifications in the criteria fields (e.g. if I specify a date and and name). My SQL code in error looks like this:
SELECT
purch_hist.PUITM AS ITEM,
purch_hist.PUPO AS PO,
purch_hist.PUQTY AS QTY_RECEIVED,
CDate([purch_hist.PURDT]) AS RECEIPT_DATE,
itmcnt.ITBYR AS BUYER,
purch_hist.PUCST AS UNIT_COST,
vendor.NVNO AS VENDOR NO,
[code]...
This query works fine with a non converted date field, however the dates I need are in text format and need to be converted since I do not have permissions to edit the tables.
View 14 Replies
View Related
May 18, 2006
Hey guys, can anybody help
here is what I have:
Job
JobId-----PK
Name
Position
PositionId----PK
Name
Activity
ActivityId-----PK
Name
JobTask as you can see the relationship between Job and Task is M-M
JobId-----PK
TaskId-----PK
PositionTask as you can see the relationship between Position and Task is M-M
PositionId-----PK
TaskId-----PK
ActivityTask as you can see the relationship between Activity and Task is M-M
ActivityId-----PK
TaskId-----PK
Task
TaskId-----PK
TaskName
TaskCompetency as you can see the relationship between Task and Competency is M-M
TaskId-----PK
CompetencyId-----PK
Competency
CompetencyId-----PK
CompetencyName
Keyword
Priority
I have a query like this:
The user will input the JobId, PositionId, ActivityId.
I want to return all the TaskName ,CompetencyId, CompetencyName
Where the following:
The highest priority within the same keyword
Basically group by keyword then pick the highest priority I have done this:
SELECT Max(Competency.Priority), Competency. Keyword
FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID
WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity]))
GROUP BY Competency.CompetencyKeyword;
It works fine, it returns the all the keyword, and its highest priority.
But I can’t return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max
View 2 Replies
View Related
Mar 10, 2007
I have a table in MS Access named Invoices containing the following values:
NameInvoice
Jim101
Jim102
Jane103
John104
Jane105
John106
John107
Jim108
Jim109
Jim110
The following query gives me the results you see below:
SELECT Name, Count(Invoice) AS CountOfInvoice
FROM Invoices
GROUP BY Name;
NameCountOfInvoice
Jane2
Jim5
John3
Ultimately, I want to design a SINGLE query that returns ONLY the Max(CountOfInvoice). In this case: 5.
Can anyone help me?
View 4 Replies
View Related
Feb 17, 2008
Hi All,
I have a table namely, leave_details wherein there is a column called DAYS and TDAYS,
DAYS columns represent number of leaves an employee has taken in a single leave application and
TDAYS represents Total number of leaves an employee has availled till date.
To calculate the TDAYS i need to SUM the DAYS column for every employee.
When an employee again log in the application he should be aware of the total number of leaves which he has already availed.
Please help me for the same as I am unable to do so.
Kindly revert back for any queries or any further information.
With Warm Regards,
Sanjay
View 3 Replies
View Related
Apr 3, 2008
Hi,
I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.
In tblListInfo the fields I am using are DropDate, ListName and Marketcode.
In tblResults the fields I am using are JoinDate and Marketcode.
I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.
I tried writing the code for this in pieces starting with:
SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[Marketcode] = tblListInfo.[Marketcode]
This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:
Listname Drop Date
List - 1 1/1/08
List - 1 1/15/08
List - 2 1/1/08
List - 2 2/5/08
From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:
Listname Drop Date Week 1
List - 1 1/1/08 15
List - 1 1/15/08 8
List - 2 1/1/08 32
List - 2 2/5/08 12
My attempt at getting to this result is below:
SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1]
FROM tblResults
INNER JOIN tblListInfo
ON tblResults.[MarketCode] = tblListInfo.[MarketCode]
WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)
I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."
Can anyone offer suggestions/corrections to my approach and logic?
View 2 Replies
View Related
Dec 15, 2004
Hi all,
I need some help in sorting out this query.
My Table looks like this..
Manifest Weight StopNo State
------- ------- ------- ------
71545 1000 10 VA
71545 1000 20 TN
71545 500 30 AL
Some Manifests are repeating
There are multiple stops within each manifest (eg.: 10, 20, 30) and each stops has itsown weight info and State.
What I am trying to figure out is the number of manifests, total weight within manifest, and the last stop and state of the manifest
Query:
SELECT ManifestNo, Count(ManifestNo), Sum(Weight), Last(Stop No), Last(State)
FROM RBP
WHERE ShipmentType="ROAD"
GROUP BY ManifestNo;
My Problem: Everything works fine, but the state of the last stop is not displaying properly..
My result is - 71545 3 2500 30 TN
Correct output should be - 71545 3 2500 30 AL
I tried to remove the "last" from State but its gives me the aggregate function error. Any suggession ?
Thanks in advance..
binjos
View 2 Replies
View Related
May 1, 2007
Hello,
I have the following query:
SELECT
tblTempProject.project_number,
tblTempProject.Project_name,
tblTempProject.subsidiary,
tblTempProject.division,
tblTempProject.unit_number,
tblTempProject.client_name,
tblTempProject.resourcing_contact,
tblTempProject.project_cat_type_code,
tblTempJob.headline,
tblTempJob.contact_name,
tblTempJob.contact_staff_number,
tblTempJob.contact_unit_number,
tblTempJob.resourcing_team_email,
tblTempJob.creation_date,
tblTempJob.nominatio_date,
tblTempJob.decision_date,
tblTempJob.sar_status,
tblTempJob.publication_level,
tblTempJob.sar_number,
tblTempJob.last_modified_timestamp,
tblTempJob.last_updated_by,
tblTempJob.requirements,
tblTempJob.positions_filled,
tblTempJob.closure_description,
tblTempJob.other_inf,
tblTempJob.description,
tblTempJob.qual_exp,
tblTempJob.Tag1,
tblTempJob.Tag2,
tblTempJob.Tag3,
tblTempJob.Tag4
FROM tblTempJob, tblTempProject
WHERE tblTempJob.project_number = tblTempProject.project_number
AND tblTempJob.project_category_type_code = tblTempProject.project_cat_type_code
AND tblTempJob.project_company_code = tblTempProject.subsidiary
AND tblTempProject.subsidiary='NL'
AND (tblTempJob.sar_status='FIR' Or tblTempJob.sar_status='HOL' Or tblTempJob.sar_status='PRO25' Or tblTempJob.sar_status='PRO50' Or tblTempJob.sar_status='PRO75')
AND tblTempJob.project_company_code=[tblTempProject].[subsidiary]
AND tblTempJob.project_category_type_code=[tblTempProject].[project_cat_type_code]
AND tblTempJob.project_number=[tblTempProject].[project_number]
AND tblTempJob.external_resourcing=-1
GROUP BY tblTempProject.project_number
ORDER BY tblTempJob.sar_status;
When execute this query then I get the following error:
"You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"
When delete the line: GROUP BY tblTempProject.project_number, then it works.
I don't know what I must do to get this right so that I have every project one time in the result.
Thanks in advance,
Nico
View 8 Replies
View Related
Dec 14, 2004
I'm trying to do a simple sum in a query to add up 3 costs to give me an overall cost. I've put the sum in as follows:
Total:sum(nz([Cost1],0)+nz([Cost2],0)+nz([Cost3],0)).
But I'm coming up with an error as follows:
"You tried to execute a query that does not include the specified expression 'AccountNum' as part of an aggregate function".
Where am I going wrong?
Cheers,
Recall.
Find attached an example.
View 1 Replies
View Related
Feb 25, 2005
Good Afternoon,
I am trying to run a query with a field called "Age". The field Age is
supposed to determine the age of the record based on what's in the remaining amount field and the cash date,
the disp date, or the run date of the download (typically the last day of the month).
So my expression for the Age column is as follows:
AGE: IIf(Min([REMAINING AMT])=0,[CASH DATE]-[DISP DATE 1],[CASH DATE]-#1/31/2005#),
which is saying of the remaining amount = 0, then calculate the age based on the difference
between the cash date and the disp date, otherwise, calculate the age based on the cash date
and the run date of the download, right?
Well, when I run the query, I get the following error message:
You tried to execute a query that does not include the specified expression 'IIf(Min[REMAINING
AMT])=0,[tablename].[CASH DATE]-[DISP DATE 1],[CASH DATE]-#1/31/2005#)' as part of an
aggregate function.
What does that mean and how can I get this to work? My "Total" line is set to "Expression". I even tried
it with "Group By" and it gave me another error message. Please help me!
Thank you
-sdc
View 4 Replies
View Related
Nov 19, 2012
My query includes a few new columns I have added and I need to use GROUP BY so each new column is grouped per store.
When I try to use: GROUP BY newColumn1, newColumn2, newColumn3;
I get the following error: 'You tried to execute a query that does not include the specified expression 'Store Nbr' as part of an aggregate function'
All I want to do is have each new columns distinct value show up once for each particular store, not repeated multiple times. When it is repeated multiple times, it also shows for each store department which messes up the metrics of the report.
View 5 Replies
View Related
Sep 15, 2006
Hi guys,
I am completely at a lose here. I have a LARGE sql query that was written before my time and how have the task of customizing it a little. It will run perfectly in Advisor Office (the program it was written for which uses access as the back end db). But if i try and create a query in access directly i get the following error:
You tried to execute a query that does not include the specified expression 'IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type =' as part of an aggregate function
the full IIF statement that is causing it to fail is:
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' ')))))) AS CommissionType
The WHOLE SQL query spans over 4 pages of A4 so i will only put it at the bottom (hoping its not needed to see the error. There are a few unions so i will put the first one in here to show what it is doing.
SELECT IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames) AS Name,
Policies.PolicyNumber,
Schemes.SchemeName,
Managers.FullName AS Manager,
CommnPremiums.CommnPremiumDate,
IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) +
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' ') AS PremiumType,
CommnEntries.DueFrom,
CommnEntries.DueTo,
SUM (Matches.Amount) AS TotalMatched,
CommissionCredits.CreditPC AS Credit,
Consultants.FullName AS Consultant,
ROUND ((CommissionCredits.CreditPC * SUM (Matches.Amount) / 100), 2) AS CommissionMatched,
Cheques.Amount AS ChequeAmount,
Cheques.ChequeDate,
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' ')))))) AS CommissionType
FROM ((((((((Clients LEFT JOIN Policies ON Clients.ClientRef = Policies.ClientRef)
LEFT JOIN Schemes ON Policies.SchemeRef = Schemes.SchemeRef)
LEFT JOIN CommnPremiums ON Policies.PolicyRef = CommnPremiums.PolicyRef)
LEFT JOIN CommnEntries ON CommnPremiums.CommnPremRef = CommnEntries.CommnPremRef)
LEFT JOIN Matches ON CommnEntries.CommnEntryRef = Matches.FKeyRef)
LEFT JOIN Cheques ON Matches.ChequeRef = Cheques.ChequeRef)
LEFT JOIN Managers ON Cheques.FKeyRef = Managers.ManagerRef)
LEFT JOIN CommissionCredits ON CommnEntries.CommnEntryRef = CommissionCredits.FKeyRef)
LEFT JOIN Consultants ON CommissionCredits.ConsultantRef = Consultants.ConsultantRef
WHERE Matches.FKeyType = 1
AND Cheques.FKeyType = 1
AND Cheques.ChequeDate BETWEEN #01-Jan-2004# AND #31-Dec-2004#
AND CommissionCredits.FKeyType = 1
AND CommissionCredits.Type = 1
GROUP BY IIF (Policies.Owner = 0, clients.partnersurname + ", " + clients.partnerforenames, clients.surname + ", " + clients.forenames),
Policies.PolicyNumber,
Schemes.SchemeName,
Managers.FullName,
CommnPremiums.CommnPremiumDate,
IIF (CommnPremiums.Type = 1, 'Initial Premium', IIF (CommnPremiums.Type = 2, 'Increment', IIF (CommnPremiums.Type = 3, 'Single Premium', IIF (CommnPremiums.Type = 4, 'Annual Renewal', IIF (CommnPremiums.Type = 5, 'Value Based', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, 'Transfer', ' '))))))) +
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type = 1, ' - Non Indemnity', IIF (CommnEntries.Type = 2, ' - Indemnity', IIF (CommnEntries.Type = 3, ' - Renewal', IIF (CommnEntries.Type = 4, ' - Level', IIF (CommnEntries.Type = 5, ' - Fund Based Trail', IIF (CommnEntries.Type = 6, ' - Level Indemnity', ' ')))))), ' '),
CommnEntries.DueFrom,
CommnEntries.DueTo,
Cheques.Amount,
Cheques.ChequeDate,
IIF (CommnPremiums.Type IN (1, 2), IIF (CommnEntries.Type IN (1, 4), 'Non Indemnity', IIF (CommnEntries.Type IN (2, 6), 'Indemnity', IIF (CommnEntries.Type = 3, 'Renewal', IIF (CommnEntries.Type = 5, 'Trail', ' ')))),
IIF (CommnPremiums.Type = 3, 'Initial', IIF (CommnPremiums.Type = 4, 'Renewal', IIF (CommnPremiums.Type = 5, 'Trail', IIF (CommnPremiums.Type = 6, 'Protected Rights', IIF (CommnPremiums.Type = 7, ‘Transfer’, ' ')))))), Consultants.FullName, CommissionCredits.CreditPC
If I changed the IIF statement to:
'X' as commisiontype
it all works great but i need this information and its driving me mad. I can not see why it doesnt work within Access.
Any and All help would be great.
thx
Twiggy
I have attached the FULL sql query if you want to look at it all.. it hurts my head to read it all ;)
View 1 Replies
View Related
Dec 31, 2007
Hi ALL,
I have 2 tables, trying to update one table filed by using max(onefiled) from other table.
my qyery is as follows:
my tables are tblSubFlowForecast and tblPickCalendar
I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.
UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");
I am getting error saying that "Operation must be an updateable query" :(
I have also tried in other way:
UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry AND tblPickCalendar.Type="Replen" ) FROM
tblSubFlowForecast,tblPickCalendar
This is giving me syntax error (missing operator) :(
Can any body tell me why it is giving error?
Thanks in advance
View 3 Replies
View Related
Sep 26, 2011
Is there some way to do this:
SELECT (sum(d.cartonsshipped)/m.ctnperskid) AS PalletCount, itemnumber, shipdate
FROM orderdata d
inner join itemmaster m
on d.itemnumber = m.itemnumber
GROUP BY d.itemnumber, d.shipdate
HAVING (sum(d.cartonsshipped)/m.ctnperskid) > 0
ORDER BY d.shipdate, d.itemnumber;
and not get the aggregate error (3122). I am guessing it's giving me the error because i try to do a function in the select area.
View 2 Replies
View Related
