query with aggregate function

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


Post your answer or comment

comments powered by Disqus
I'm having a problem with queries, and I can't seem to find a solution in books - I looked through about ten of them and none of them addressed the problem. This may be because it has a painfully obvious solution...

A little background:
I am designing a database for a debt-collection law firm. One of the functions it must have is to keep track of various different sorts of financial transactions which can pertain to a given debtor (ie, a received payment, a cost expended, and a few other things).

The problem is that, in generating reports, I need to use queries to find several sums of only those transactions which fall into specific categories (for instance, to calculate the amount a debtor has paid against his balance, it needs to sum only those entries which are both linked to that debtor's ID number and whose type field reads "payment", and then subtract from that those entries whose type field reads "cost"). The problem is this: not all debtors may have "costs" entries, and when there are none, the report comes up blank with a single "#Error" written in the name field and nothing else present.

I believe the problem is that the Sum aggregate is returning a null value when the query finds nothing that meets the criteria. I have been unable to find a way around this; the Nz() and IIf() with IsNull() functions don't seem to be helping.

The query runs as intended when there are entries for every relevant type; however, it is undesired to have to enter a "payment" of $0, "cost" of $0 etc for every entry just so that this function works.

Is there anything I can do about this? Any input would be appreciated, as I'm fairly inexperienced with the use of Office Access. (If it matters, I am using Office 2003).

Given a simple table with historical info about Hotelroom occupancies such as name of the guest, date in, type of room, number of nights, ...
I want to make a query resulting in 1 output record that shows the highest number of nights someone has ever stayed in the hotel, as well as the corresponding detail information: who was this customer and which type of room was he in?
Using the 'group by' and 'max' function it's easy to solve the first question, but I am not able to show the corresponding detailed information in that record (person's name, type of room).
I've done some experiments with a nested query in the 'expression' field, also trying a selfjoin but always getting errors. I'm confident there must be a solution? Can anyone help me out? Thank you!

Hi, I hope for a little advice on an issue I'm having with an query, where the results of aggregate functions are clearly incorrect. I have a query which is supposed to take transactions (records which are used to create a pivot table which shows my cash-flow) with the status 'Sales Forecast' and combine them with values calculated from my sales forecast tables so that sales forecasts are included in my cash-flow.

To avoid writing too much about the design of the query, there is a screenshot of the offending query in design view, also here is the SQL code:


	Code:
	SELECT Transactions.[Transaction ID], Transactions.[Transaction Status], Transactions.[Forecast Date], [Transaction Serial
Months].[Forecast Serial Month], Sum(IIf(IsNull([Forecast Deposits]),0,[Forecast Deposits])) AS [Month Forecast Deposits],
Sum(IIf(IsNull([Forecast Balances]),0,[Forecast Balances])) AS [Month Forecast Balances]
FROM ((Transactions LEFT JOIN [Transaction Serial Months] ON Transactions.[Transaction ID] = [Transaction Serial
Months].[Transaction ID]) LEFT JOIN [Sales Figures - Forecast Balances] ON [Transaction Serial Months].[Forecast Serial
Month] = [Sales Figures - Forecast Balances].Month) LEFT JOIN [Sales Figures - Forecast Deposits] ON [Transaction Serial
Months].[Forecast Serial Month] = [Sales Figures - Forecast Deposits].Month
GROUP BY Transactions.[Transaction ID], Transactions.[Transaction Status], Transactions.[Forecast Date], [Transaction Serial
Months].[Forecast Serial Month]
HAVING (((Transactions.[Transaction Status])="Sales Forecast"));

I have highlighted the fields which will be aggregated, first I checked what the query returned without aggregating anything. Under month number 21 there are 3 items (forecasts for 3 different types of service which are to be added together so I get a single figure for the month to attach to the transaction representing the sales forecast for that month), the sum of the rightmost column should be £340 (screenshot below, datasheet - no aggregate)

However when I change the totals for the two rightmost columns from Group By to Sum, I see the following (screenshot below, datasheet - sum), the total is £1360, which is four times what it should be.

If I change the totals to Count, then I see the following (screenshot below, datasheet - count), which counts 12 items instead of 3 (I didn't bother changing the number formatting).

Does anyone know what's going on?

This ought to be really simple but I must be having a mental block. I want to run a query on a table with a field containing letter codes and I wish to count a only one of the various codes possible. It seems to me that I should be to set up the query using SQL aggregate functions (COUNT) and set the criteria to letter code of interest. When I do that, I get a data type mismatch error. Do I need to use the ASCII value of the letter code? That makes no sense. Help!

Hi, I'm trying to make a query with the Count function. Here is my code:

	Code:
	SELECT tblFunction.JobID, tblContractorFunction.FunctionID, Count([TrackingNumber]) AS Expr1
FROM tblProductionInput INNER JOIN (tblJob INNER JOIN (tblFunction INNER JOIN (tblContractorFunction INNER JOIN
tblProductionInputDetail ON tblContractorFunction.ContractorFunctionID = tblProductionInputDetail.ContractorFunctionID) ON
tblFunction.FunctionID = tblContractorFunction.FunctionID) ON tblJob.JobID = tblFunction.JobID) ON
tblProductionInput.ProductionID = tblProductionInputDetail.ProductionID;

My probles is that I keep getting the following error:
You tried to execute a query that does not include the specified expression 'JobID' as part of an aggregate function.

Here is my data structure:



Thanks,

Joe

Hello Everyone. I'm trying to create an update query and have been failing. Here's the scenario - I have 2 tables: Order & OrderBreakdown. In my Order table I have a field named TotalCharge, the field I want to update. The OrderBreakdown table has InventoryID foreign key in it as well as a OrderID foreign key. The OrderBreakdown table is going to have multiple records, all with matching OrderID keys. In the OrderBreakdown table is a Subtotal field with the totals amounts for each record, I just need these all summed together (based on their OrderID's). But when I try to do this, access says "You tried to execute a query that does not include TotalCharge as an aggregate function". What am I missing here? I know what an aggregate function is, but Access won't let me choose the Totals button when I'm on an update query. The SQL i'm trying to use is below. Please help!!

UPDATE tbl_Order INNER JOIN tbl_OrderBreakdown ON tbl_Order.OrderID = tbl_OrderBreakdown.OrderID SET tbl_Order.TotalCharge = Sum([tbl_OrderBreakdown].[Subtotal])
WHERE (((tbl_Order.OrderID)=[forms]![frm_OrderBreakdown].[orderid]));

Hello, I am having trouble with Access aggregate functions, as I would like to select a second, third, etc. record, similar to a First or Last function, but clearly such functions do not explicitly exist.

In a nutshell, what I am trying to do is the following: I have hourly climate records (for hours from 0 to 23) that I need to group into 6-hour periods (beginning at hours 0, 6, 12, and 18), as some of my data are only available in 6-hourly increments. I am using an aggregate function to do this, and have had no trouble with this method; for each 6-hour period, I have created a number of summary variables using different Count, Avg, Max, functions, etc.

However, I have realized that I also required additional information about the hourly weather conditions throughout each 6-hourly period, i.e., the conditions experienced in the first, second, third hour (of the 6-hour period), and so on. For the first and last hours I can do this, but am completely stuck on the others. I am working with massive amounts of data, so it is not feasible to simply go back to the hourly records and manually look up the conditions throughout an aggregated 6-hour period.

Perhaps someone out there has some ideas as to how I could go about this, perhaps by nesting another aggregate function within this one ? (I can't seem to wrap my head around that).

(Note that I have extracted the below code for example purposes from a much longer query that I have been successfully using, so if a few commas etc. are missing or my syntax looks to be incomplete, that should not likely be a problem – my issue is with the new functions I have added to produce the variables 1stHlyType, 2ndHlyType, ..., 6thHlyType. Note also that it works perfectly as expected for First and Last).

Thanks!
Derrick

SELECT
[6hly_E6-E9].[DateSerial] AS [Date],
[6hly_E6-E9].[6hPer],
[6hly_E6-E9].[6hlyPrecip],
[6hly_grouped].[6hlyType],
[6hly_grouped].[1stHrType],
[6hly_grouped].[2ndHrType],
[6hly_grouped].[3rdHrType],
[6hly_grouped].[4thHrType],
[6hly_grouped].[5thHrType],
[6hly_grouped].[6thHrType],
[6hly_grouped].[TotalHlyRainfall],
[6hly_grouped].[MaxHlyRainfall],
[6hly_grouped].[AvgHlyRainfall],
[6hly_grouped].[HOR],
[6hly_grouped].[AvgHlyDryTemp]
INTO [6hly_climate_summary-left]
FROM [6hly_E6-E9] LEFT JOIN (SELECT [Date],[6hPer],
IIf(Sum(IIf([HlyType]="M2",1,0))>0,"M2",IIf((Sum(IIf([HlyType]="M1",1,0))>0 Or (Sum(IIf([HlyType]="R",1,0))>0 And Sum(IIf([HlyType]="S",1,0))>0)),"M1",IIf(Sum(IIf([HlyType]="R",1,0))>0,"R",IIf(Sum(IIf([HlyType]="S",1,0))>0,"S","")))) AS 6hlyType,
First(IIf(([Hly_climate_summary].[Hour]=0) OR ([Hly_climate_summary].[Hour]=6) OR ([Hly_climate_summary].[Hour]=12) OR ([Hly_climate_summary].[Hour]=18),[Hly_climate_summary].[HlyWeather],"")) AS 1stHrType,
First(IIf(([Hly_climate_summary].[Hour]=1) OR ([Hly_climate_summary].[Hour]=7) OR ([Hly_climate_summary].[Hour]=13) OR ([Hly_climate_summary].[Hour]=19),[Hly_climate_summary].[HlyWeather],"")) AS 2ndHrType,
First(IIf(([Hly_climate_summary].[Hour]=2) OR ([Hly_climate_summary].[Hour]=8) OR ([Hly_climate_summary].[Hour]=14) OR ([Hly_climate_summary].[Hour]=20),[Hly_climate_summary].[HlyWeather],"")) AS 3rdHrType,
First(IIf(([Hly_climate_summary].[Hour]=3) OR ([Hly_climate_summary].[Hour]=9) OR ([Hly_climate_summary].[Hour]=15) OR ([Hly_climate_summary].[Hour]=21),[Hly_climate_summary].[HlyWeather],"")) AS 4thHrType,
First(IIf(([Hly_climate_summary].[Hour]=4) OR ([Hly_climate_summary].[Hour]=10) OR ([Hly_climate_summary].[Hour]=16) OR ([Hly_climate_summary].[Hour]=22),[Hly_climate_summary].[HlyWeather],"")) AS 5thHrType,
Last(IIf(([Hly_climate_summary].[Hour]=5) OR ([Hly_climate_summary].[Hour]=11) OR ([Hly_climate_summary].[Hour]=17) OR ([Hly_climate_summary].[Hour]=23),[Hly_climate_summary].[HlyWeather],"")) AS 6thHrType,
Sum([Hly_climate_summary].[HlyRainfall]) AS TotalHlyRainfall,
Max([Hly_climate_summary].[HlyRainfall]) AS MaxHlyRainfall,
(Sum([Hly_climate_summary].[HlyRainfall])/Count([Hly_climate_summary].[HlyRainfall])) AS AvgHlyRainfall,
Sum([Hly_climate_summary].ObsRain) AS HOR,
Avg ([Hly_climate_summary].[HlyDryTemp]) AS AvgHlyDryTemp
FROM [Hly_climate_summary] GROUP BY [Hly_climate_summary].[Date], [Hly_climate_summary].[6hPer]) AS 6hly_grouped ON ([6hly_E6-E9].[DateSerial]=[6hly_grouped].[Date]) AND ([6hly_E6-E9].[6hPer]=[6hly_grouped].[6hPer]);

Sorry that title is a tongue twister.

I have 3 different text boxes that have aggregate functions as there control source; they sum different fields and display the results in the text boxes. Easy enough. Now here is where things have got tricky I need to place these text boxes within a paragraph of text inside a text box and have it somehow formatted to be the corrected size. So it would look nearly seamless body of text with the 3 Aggregate text boxes inside it. O to throw another wrench into it of course the 3 text boxes change every time the query is changed so the size is not dependable.

Now I have just been tinkering with it trying to find the sweet spot but it is real hard. Is there anyway to like place these 3 text boxes within a body of text and have it just reformat the spacing each time?

I am trying to use MS Query to import queries from an Access database. All of the queries import fine except:
-One query uses 2 functions in a module to capture the beginning and ending dates in a form. The query works in Access, but when I attempt to import using MS Query, I get the error "Undefined function 'f_Beg_Date'(which is the name of my first function) in expression"
-Also, I have a query which uses 4 different subqueries to perform aggregates (Select Count(My_Field) WHERE blah, blah....) and when using MS Query to get it into Excel, I get the "Expected Parameter" error. Note that this isn't a parameter query.

Are either of these errors avoidable based on my needs or is MS Query just not robust enough and I need to start delving into Automation?

Hi,

I have records from one table that I would like to filter through a totals query. I would like to get ONE record for each FACTORYID for each STYLEID with the greatest EVENTID number and latest EVENTDATE.



Meaning:

1. In the resulting record set, no field would be a unique key

2. For each STYLE,
if there are multiples of any FACTORYID (long integer),
the greatest EVENTID will be chosen FOR EACH FACTORYID

3. If there are multiples of an EVENTID (long integer) for a FACTORYID,
the latest EVENTDATE will be chosen

4. If there are multiples of that EVENTDATE,
the smallest EVENTSTATUSID will be chosen

5. If there are multiples of that EVENTSTATUS (long integer),
it really doesn't matter which EVENTSTATUS
(or any of the other fields) is chosen

There are multiple other fields of all data types.

When I use more than one type of aggregate function in a totals query, the information in each of the columns seem to be mismatched, like a frankenstein record is built, as if each of the Columns' aggregate function is calculated independently of the other.

I'm really not clear on exactly how totals queries work with multiple aggregate functions. Do I have to do multiple queries to get this? How do I get the results am after?

I am been working in design mode.


My fields:
STYLE
FACTORYID
EVENTID
EVENTDATE
EVENTSTATUS
OTHER1
OTHER2
OTHER3
ETC...


Thank you for your help!

Hi guys,
I'm sorry I could not find the solution to my problem on this forum, though I searched, so I post here. But it should be pretty obvious, because what I'm trying to do sounds basic...

I have a table, named "ES_Detail", in which I want to update its field [Date created] (if empty) with the oldest date from the child field [Entry Date] in its linked table named "ES".

Such a thing should be easy to do but I managed only to do syntax errors with my limited SQL knowledge.

The SQL query I have so far, that is not working, is:


	Code:
	UPDATE ES_Detail INNER JOIN ES ON ES_Detail.[ES ID] = ES.[ES ID] SET ES_Detail.[Date created] = First([ES].[Entry Date])
WHERE (((ES_Detail.[Date created]) Is Null));

That tells me that I tried to execute a query that does not include [Date created] as part of an aggregate function.

Anyone having an idea of the simple change I should do to carry out this simple update query? Thanks in advance.

I keep getting the message "You tried to execute a query that does not include the specified expression 'months_open' as part of an aggregate function", yet clearly it is. The query is based on a single table with the following fields:

SITE_CODE, sales_date, gross, coupons, months_open

I'm trying to create a query that will show each site's gross sales by month grouped on months_open so that site 2's first month's sales will appear on the same row as site 1's first month's sales, second month same as second month, etc. My query reads like this:

SELECT tblDaily_Sales.[months_open],

(SELECT Sum(tblDaily_Sales.gross) FROM tblDaily_Sales as dup WHERE dup.months_open= tblDaily_Sales.[months_open] AND dup.SITE_CODE ='T_0001' GROUP By dup.months_open) AS [T_0001 Gross],
(SELECT Sum(tblDaily_Sales.coupons) FROM tblDaily_Sales as dup WHERE dup.months_open= tblDaily_Sales.[months_open] AND dup.SITE_CODE ='T_0001' GROUP By dup.months_open) AS [T_0001 Discounts],
(SELECT Format([sales_date],"mmm-yy") FROM tblDaily_Sales as dup WHERE dup.months_open= tblDaily_Sales.[months_open] AND dup.SITE_CODE ='T_0001' GROUP By dup.months_open) AS [T_0001 Month],

(SELECT Sum(tblDaily_Sales.gross) FROM tblDaily_Sales as dup WHERE dup.months_open= tblDaily_Sales.[months_open] AND dup.SITE_CODE ='T_0002' GROUP By dup.months_open) AS [T_0002 Gross],
(SELECT Sum(tblDaily_Sales.coupons) FROM tblDaily_Sales as dup WHERE dup.months_open= tblDaily_Sales.[months_open] AND dup.SITE_CODE ='T_0002' GROUP By dup.months_open) AS [T_0002 Discounts],
(SELECT Format([sales_date],"mmm-yy") FROM tblDaily_Sales as dup WHERE dup.months_open= tblDaily_Sales.[months_open] AND dup.SITE_CODE ='T_0002' GROUP By dup.months_open) AS [T_0002 Month]

FROM tblDaily_Sales
GROUP BY tblDaily_Sales.[months_open];

What am I missing?

Thanks,
Paul

I have a crosstab query based on a query. I click the show totals button on the wizard. It comes up with "You tried to execute a query that does not include the specified expression 'Surname' as part of an aggregate function.

The query shows titles by state, where the count is no of surnames, yet the design shows word Expression: Surname twice.

I can fix it and run it by changing expression to count and deleting the second option.

I only wish to know why the wizard plays up on this one.

Hi everyone,

I thought I would let you all know of a few things I like to remember when using the GROUP BY clause in queries that involve aggregate data...

1) Even though the GROUP BY clause is written after the selected fields in the query, Access performs the GROUPING first, in order to retrieve the basis on which to perform the aggregate expressions.

2) Fields that are selected, but are not part of any aggregate functions or expressions, must be included in the GROUP BY clause. Note: This requirement will restrict the data you receive based on the number of fields GROUPED, so it is important to select only fields that AFFECT the aggregate expressions.

As a supplement to #2, below are two examples of how the GROUP BY clause affects the output of data....

Say we want to view the total amount of revenue on all items that we sell; we would write...

	Code:
	SELECT [item], sum([total price]) as [Total Revenue]
FROM [orders]
GROUP BY [item];

Now, say we want to view the total amount of revenue (per item) we received from one customer; we would write...

	Code:
	SELECT [item], [customer], sum([total price]) as [Total Revenue]
FROM [orders]
GROUP BY [item], [customer];

It is important to remember that Expressions (e.g. [field]+[field]) and Functions (e.g. Sum([field]), Count(*)) are never grouped. They are simply actions to be performed on the GROUPED fields themselves.

I have attached a transaction log that I worked with (I turned it into a database to make things easier). It contains simple queries (and one fairly complex query) to illustrate the advantages of using the GROUP BY clause with aggregate data.

The "Groupings" example is for reference if anyone may need it.

Hi!

I'm working on a project where users are interacting with an access database through a collection of userforms in Excel.

I've got an issue with a query that I try to run from a sub routine in excel. The query works fine when i runnit i access but when i try to execute it through a database object in excel(through vba) i receive a runtime error. The error states that there is an undefined function in the expression.
The aim of this query is to append all customers along with a datevalue (selected from a userform) to a table (provided that the record doesn't exist already). The table receiving the records holds customer, date and price.

i've encountered this issue or similar issues several times and would like to find a better solution to it. I feel all my ideas are a bit retarded and imagine that someone here has a way more elegant solution.

The ideas that i have thought of this far is
1) Create a temp table, delete old values, load new values.
Then the date values are available to use in a table and i have no trouble to create the append query
2) Create a VBA procedure where i iterate through the records valid for the date and compare with all customers and then append the ones thar are missing.
3) Create a macro which runs the SQL expression and then create an access application object and run the macro (using DoCmd.RunMacro) through excel (however this would require that all the users has a license for ms access)

Btw i also feel my SQL expression is a bit retarded. First of i tried setting using only a string in the second join criteria but that didn't work. However I did manage to get to it work by concatenate the field with a string and erase the field value.

	Code:
	INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth ) 

SELECT CustomerToSAPID.CustumerName, #9/1/2010# AS DateForInput 

FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON
CustomerToSAPID.CustumerName 
= MontlyResultValues.CustomerName 
And
 Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') & '2010-09-01')  
= Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD')) 

WHERE (((MontlyResultValues.CustomerName) Is Null))


the vb code where i receive the runtime error

	Code:
	Private Sub ExampleSub()

Dim strSqlCommandText As String
Dim pubDatabaseForPfMng As Database
Set pubDatabaseForPfMng = OpenDatabase("C:Documents and Settings" & Get_User_Name & "DesktopExample.mdb", False, False, "MS
Access;PWD=" & strAccDbPassW & "")

strSqlCommandText = "INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth ) SELECT CustomerToSAPID.CustumerName,
#9/1/2010# AS DateForInput FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON CustomerToSAPID.CustumerName =
MontlyResultValues.CustomerName And Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') &
'2010-09-01')  = Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD')) WHERE (((MontlyResultValues.CustomerName) Is
Null))"
pubDatabaseForPfMng.Execute strSqlCommandText, dbFailOnError

end sub



hello I need to create an aggregate function that will give me the product of my results

for example
I have a table with the following info
State City Data
PA Philly .9
PA St College .8

I want to create a query grouped on the state that will give the product of .8 and .9

end result should be
State Product of Data
PA .72

I know how to create a function but how can I make it an aggregate function (like the SUM function

Thanks alot

I have a function that takes 4 inputs and creates a record in a table (for accounting). I wanted to process a batch of records at once, based on the results in a query. So I made the query to give me the records I wanted to make accounting entries. I then made a second query that has a field with the function in it, that feeds the inputs off the first query. Everything seems to be normal,the query returns 5 records, the function runs 5 times and creates the records. Problem is, it creates SIX records. The first one is duplicated for some reason. There are 5 results from the query, but 6 records created. Here is the code of the function:


	Code:
	Public Function AddRecord(tableopen As String, IDNum As Long, amnt As Currency, entdate As Date, descrip As String)

Dim dbnm As Database
Dim rstAccount As Recordset

Set dbnm = OpenDatabase("returnedchecks.mdb")
Set rstAccount = dbnm.OpenRecordset(tableopen)

    With rstAccount
        .AddNew
        !IDNo = IDNum
        !entrydate = entdate
        !amount = amnt
        !description = descrip
        .Update
    End With

AddRecord = "Yes"


End Function

Here's the SQL of my first query (creates the initial recordset):
SELECT tblChecks.*, tblChecks.IDNo AS IDNo2
FROM tblChecks
WHERE (((tblChecks.SentToDMV)=0) AND ((tblChecks.TagStatus)="2"));


SQL for second query (runs the function)

SELECT AddRecord("tblARDMV",qryDMV2!IDNo2,tblchecks!check amounttag,Date(),"Stage 2: etc") AS Expr1, qryDMV2.IDNo, tblChecks.CheckAmountTag
FROM tblChecks INNER JOIN qryDMV2 ON tblChecks.IDNo = qryDMV2.IDNo;



What is going on here? Why is it creating an extra record in 'tblARDMV'?

Thanks a lot!

Morning!

I have quite a few aggregate functions within a report I produced based on 2 queries. I need to do a mail merge with this information, but it is not all in one single query, because I cannot get an expression using totals in a field. How do you get these aggregate functions to be a part of the query, so it can eventually be part of the mail merge? It is unfortunate that you can't just use a report to mail merge, because all my equations are in the report.

Seemed appropriate to have a different thread for this issue, even though I have another thread for the original issue.

I have put together a nice little subquery that allows me to look at the flow of a gas over time and, by numerically differentiating time, allows me to calculate the amount of gas that has flowed over a period of time.

Here's the current query:


	Code:
	SELECT TestTable.PolyID,
 
 
([TestTable]![Time]-
Nz((SELECT TOP 1 Dupe.Time From TestTable AS Dupe 
WHERE dupe.Time < TestTable.Time 
ORDER BY Dupe.ID desc),0))
 
*[TestTable]![EthFlow] 

AS C2dT
 
FROM TestTable;

The query gives 2 fields, the PolyID (which are identical for all of these, they represent a single run), and the C2dT, which is the chunk where you subtract current time by previous time (the subquery) and multiply it by the EthFlow value.

From this I can create a second query where I sum the C2dT values, which is my ultimate goal. What I wanted to do was to just sum them in the same query, so I tried this:


	Code:
	SELECT TestTable.PolyID,
 
Sum(
([TestTable]![Time]-
Nz((select TOP 1 Dupe.Time
From TestTable AS Dupe 
WHERE dupe.Time < TestTable.Time 
Order by Dupe.ID desc),0))
 
*[TestTable]![EthFlow] 
)
AS C2dT
 
FROM TestTable
 
Group By PolyID;

But it won't let me do this because Time and Ethflow are not part of an aggregate function. I think I can get away with that if I mess with how I am grouping the subquery but I can't figure it out.

Any thoughts?

Hi all.

I want to create a query based on a table 'X' which is on the 'one' side of a one-to-many relationship with table 'Y'. The purpose of the query is to provide the recordset to a form to edit the records in table X but I want to include totals from the linked records in table Y for information purposes.

Table X is 'Items' (as in financial items to be put into my forecasts), table Y is 'Transactions'. The reason I've done it this way is that I can have an item, the cost of which is spread out over several transaction, for example the item 'Banking Charges (2012)' contains 12 transactions, one for each month, I feel this is neater than just having 12 items in the items table.

Basically, I want the form I use to manage my items (which contains a subform listing the transactions for that item), to also display for information purposes the total of all the transactions, the number of transactions and the date of the first transaction.

I know how to use the 'Totals' function within the query, but it seems that because of the way Access works, this prevents me from editing the underlying data in the Items table. I have looked into SQL Aggregate Functions as one possibility, another possibility is if I can create these totals within the form rather than having to create a query, and then just use the Items table as the source for the form. But my skill-level with Access is fairly basic so I'm unable to understand Microsoft's help files myself.

Hello



I like to make a query for extracting latest (max) rev of one list like below. But for catching this result I have made 3 queries

that are related
(one by one). So I have floated between queries with slow speed.



LIST:


	Code:
	DocumentNo    title    REV    DATE        TRANS
DW-1013        test    0    12-Dec-09    TT-3799
DW-1013        test    1    13-May-10    TT-5923
DW-1013        test    1    15-Apr-11    TT-5943
DW-1013        test    2    1-Jan-12    TT-6031

Result:


	Code:
	DocumentNo    TITLE    REV    DATE        TRANS
DW-1013        TEST    2    1-Jan-12        TT-6031

For this result if have made 3 queries that are related.

1- First query for finding max rev of the list.(group and aggregate function)

2- Second query for finding max date that has been made with relating first query and list with joining (doc no, rev)

3- Third query for finding max transmittal , that has been made with relating second query and list .

So slow of third query that has my result is slow (some times around 17 second) , please help how I can make better query.

Best regards.

I'm extremely new to Access, so if you need clarification on something I'd be happy to do that. I'm still learning the lingo.

I have a table with the following fields:
-ID number
-Function
-Date
-Start Time
-Stop Time
-Number Processed

I want to create a query that will calculate the hourly rate of units produced.

This is very easy to do when it's just one record at a time. I created a query to calculate the time spent and used that to determine the hourly rate.

But when I try to turn it into a totals query, it just doesn't work, and I get error messages about not including my functions as aggregate functions.

I want to be able to get the hourly rate for individuals, for specific functions, and on specific dates, but I can't seem to make the calculated fields work in a totals query.

Can someone see where I'm going wrong and assist?

Thanks in advance.

I am trying to make a query using the DateDiff function. I want to determine if the date of last customer contact is less than 365, between 366 and 730 or greater than 730, thus making three categories. My objective is to get the last contact date from a related table to get one of three variables, A, B or C which would represent whether the sales contact is prime, secondary or old.
I can calculate the number of days using DateDiff, by first using Max(tblSalesContactsFinal.DateofContact) AS LastDate to get me the last contact date. Then I used DateCount: DateDiff("d",[LastDate],Date()) to get the number of days. But when I use Iff(DateDiff("d",[LastDate],Date())730, "C", "B"))
I get the error Undefined Iff In expression. I tried it with only one variable, that is Iff (Value)

I know SQL well enough, but I cant seem to get my query to work. Basically, I am trying to display a series of posts, with the number of comments on the side.

I have two tables, Entries and Comments, which look like this:

	Code:
	Entries
---------
ID	Title	Content (memo field)
1	Cats	Something about cats here
2	Dogs	Something else about dogs here
3	Rodents	My pet rat runs in a wheel
etc.


Comments
--------
ID	PostID	Comments
1	1	My cat's name is mittens
2	1	I taught my cat how to throw a curve ball
3	3	Rats like squeak toys
4	2	Nobody likes dogs
5	3	Bunnies make good pets
6	1	Cats are witches in disguise

Comments.PostID corresponds to Entries.ID on a many-to-one relationship.

I want to run a query that will join the number of comments (which is a Count of each row in the Comments table where Comments.PostID = Entries.ID) to the existing table, so that it will look like this:

	Code:
	Entries (with #ofcomments joined)
----------
ID	Title	Content (memo field)		#ofcomments
1	Cats	Something about cats here	3
2	Dogs	Something else about dogs here	1
3	Rodents	My pet rat runs in a wheel	2
etc.

Usually, this would be a really simple SQL statement, but as I am using a Memo field, I am getting a "cannot use Memo field in aggregate function" error.

I'm not sure what to do, any help would be appreciated


Not finding an answer? Try a Google search.