Domain Aggregate Functions In Query Expressions
Apr 3, 2007
Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:
tbTickets
TicketID - Autonumber - Primary Key
Issue - Text - I will be using this later as a criteria to limit with, but not neccessary
tblTicketNotes
NoteID - Autonumber - Primary Key
TicketID - Long Integer - This is the foreign key of the relationship
DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one
Content - Memo -This is the note information I want
So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.
View Replies
ADVERTISEMENT
Jun 7, 2007
Is there any way to lookup items using DLookup or other functions that will return an array of values not just the first value found. Example: I have three employees in a complaint query. I am using Dlookup to identify them on my employee form. When I get to the same employee/employee id on my employees form, I have a label that flashes red to flag me that this employee appears on that complaint query. The problem that I am having is that it only flashes for the first employee found in the query, and the label only flashes for that one person. I would like the label to flash for the other two employees because the function should see that they are in the query results as well. Help please?!!!
View 2 Replies
View Related
Jul 8, 2012
i need domain aggregate functions to count the occurrences of specific value in a field, and when it exceeds 1, display null. or 'group by' subqueries with count() to see if you should populate the field or not.
View 3 Replies
View Related
Aug 4, 2005
Hi there,
I have a list (listA) which has a record source of a query object (queryA).
The form (formA) which has listA in it, also has a text box for some of the fields requested in queryA. The goal is to provide a flexible filter for the records in the list, so the user can just type in 'K' in text box filter_name, and they will only see records matching the entry.
N0w, the scene is set! I can easily go into the query object, and in criteria type something like this:
Like "*" & [Forms]![formA]![filter_name] & "*"
This works! It flexible filters out the records matching the text box value..
But, if the record has NULL for the name, then it won't even match "**", which is the minimum criteria. So therefore I get exclusion of records, which I dont want.
So I thought perhaps there might be some control structure I can use in expressions... some if, else, end if statement? Does anyone know how to do this?
Alternatively I thought, I could write a function like this..
Function criteria(ByVal val As Variant) As Variant
If (IsNull(val) Or IsEmpty(val) Or val = "") Then
'do nothing
Else
criteria = "Like *" & val & "*"
End If
End Function
And then in the queryA object for criteria you just use:
=criteria(Forms!formA!filter_name)
But this doesn't seem to work.. and I dont know how to debug this...
Any ideas? I thought the function was a neat solution, but it seems not!
Thanks in advance!
Hiero
View 2 Replies
View Related
Oct 14, 2005
I have a subform (in continuous record view) which displays products on an order items form. Each record has a DMin control to find the lowest price for its product (each product can have many suppliers and therefore many prices). I need to put a control in the subform footer containing the sum of the minimum prices.
Access doesn't seem to want to sum calculated controls, so could someone please point me in the direction of how to go about doing this or achieving the same result through a different method?
View 10 Replies
View Related
Oct 10, 2005
i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...
switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])
basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...
Thanks,
View 2 Replies
View Related
Aug 29, 2005
I was wondering if anyone can help me: I have for example Pupils that are being entered into a table (called log). A query then counts the number of entries for each pupil I then want it ONLY to show the pupils that have been entered in 10 times on todays date (using DATE()).
I know this doesnt work but this is what I want to do:
SELECT log.pupilNo, log.Forename, log.Surname, Count(log.pupilNo) AS Demerits
FROM log, Tally
WHERE (log.Date)=Date() AND COUNT(log.pupilNo)>9
GROUP BY log.pupilNo, log.Forename, log.Surname, log.pupilNo;
Apparenty you cannot have an Aggregate function in a where clause.
Does anyone know a solution, any help would be appreciated
Cheers
Bikeboardsurf
View 2 Replies
View Related
Oct 16, 2006
Hi,
Is it possible to add a calcualted field to a query that already uses 'sum'?
I want to take the value returned by sum and add the value of another field to it.
Any help will be very much appreciated.
B
View 12 Replies
View Related
Jun 21, 2005
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:
Entries
---------
IDTitleContent (memo field)
1CatsSomething about cats here
2DogsSomething else about dogs here
3RodentsMy pet rat runs in a wheel
etc.
Comments
--------
IDPostIDComments
11My cat's name is mittens
21I taught my cat how to throw a curve ball
33Rats like squeak toys
42Nobody likes dogs
53Bunnies make good pets
61Cats 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:
Entries (with #ofcomments joined)
----------
IDTitleContent (memo field)#ofcomments
1CatsSomething about cats here3
2DogsSomething else about dogs here1
3RodentsMy pet rat runs in a wheel2
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 :)
View 4 Replies
View Related
Mar 22, 2013
I have the following query. I got an error when I ran it.
Code:
SELECT COUNT([encounter_number] WHERE status = 'Death')/ COUNT([encounter_number]) AS Death Ratio, tbl_test.facility_type AS Type,
FROM tbl_test
GROUP BY tbl_test.facility_type;
I think the problem is that first WHERE clause inside.
View 2 Replies
View Related
Feb 4, 2014
I have a problem with a nested arrangement of Right, DLookup and DMax functions.
The function is for a default value in a text control
=Right(DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))+1000001,6)
I am trying to retrieve the last record from a field SampleNumber which is alphanumeric (e.g. "AK005434") and then add a 1 to it as the next SampleNumber. I had previously used a default value in the txtSampleNumber control as
="AK" & Right(1000000+DMax("[Clip]","[tbldat14A_MasterSampleList]")+1,6)
where [Clip] was a field I have calculated in the table to chop off the preceding characters. Adding 1,000,000 and taking the 6 right hand values and concatenating with "AK" gave me the answer, though it is a but primitive.
This all worked until the SampleNumber value got out of order and blocks of SampleNumber values came in that were then followed by blocks of numbers with lower values (say AK005001-AK005050 followed by AK002001-AK002050).
Now I figure if I just recall the latest entry by DLookup and criteria of DMax on the SampleID (Autonumber Primary key) I could get at the value. I have done this to some success using default values in a series of unbound controls like
=DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))
to get the SampleNumber I require, then a Right function to trim in another unbound box and then use that last unbound box as the default value for the txtSampleNumber control that is the entry for the data table. However, the unbound control box is only valid for the first record and does not update. So, I added a macro that closes the form and reopens it. This all works but is a bit agricultural. I would like to do a single nested function to the default value of the txtSampleNumber control box. Is it possible to nest Right, DLoopkup and DMax into one statement?
View 14 Replies
View Related
Jun 26, 2007
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).
View 2 Replies
View Related
Oct 15, 2007
I need to build an expression: calculate the total number of years worked given the start date and end date.
I put this as the expression in Field: Years Worked: [End Date]-[Start Date]
However, I keep getting #Error in datasheet view.
Can someone help? thank you.
View 1 Replies
View Related
Sep 9, 2014
I intended to get a Value from a Query for a textbox, so I tried to use the Domainfunction DFirst.
Code:
=DFirst("[SAPANLAGE]";"[Query_Anlage_42]")
but now the box just shows #error and blinks like crazy.
View 13 Replies
View Related
Jul 6, 2005
The situation is I have two queries, one uses the other as a source, and the other uses DSUM with the source query as it's domain.
For example: I have one query called RetailCDState, this prompts the user for the name of a state (i.e. Texas), and then prompts for the name of the CD (i.e. Joshua Tree). The query returns the number of CD's sold for each retail store entered for that state.
I have another query called, TotalRetailState. This query uses RetailCDState as it's source. I pull some fields down into the QBE so that the CDname, state, artist name, etc will be shown. This works.
HERE IS THE PROBLEM. I have created 3 expression fields: TotalCDSOLD, TotalCDSHIP, and TotalCDRETURN. In these expression fields I have used DSUM, I want to use the query RetailCDState as the domain, which the help files said I can. However, it's giving me an expression error saying that it can't find the 'state' or 'CD Name', which is what should prompt the user when the whole thing is executed.
I get 3 error messages, one for each expression field, and then it prompts the user for the correct input, but then doesn't display the information. Here is an example of my DSUM line TotalCDSHIP: DSUM("[NumofCDSold]","RetailCDState")
I shouldn't need any criteria, because the results of the RetailCDState is what I want to total.
PLEASE HELP! :(
View 4 Replies
View Related
Feb 29, 2008
Hi all!
I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as:
tblFunctionCodes.FunctionCode,
tblAccounts.AccountNumber,
Monday (hours),
Tuesday (hours),
Wednesday (Hours),
Thursday (hours),
Friday (hours),
Saturday (hours),
Sunday (hours)
Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables.
The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.
I have this query to get the data, but with no expressions (can't get it to work):
SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay
FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID
WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));
I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??
Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!
Thanks in advance, jon.
View 10 Replies
View Related
Jul 17, 2007
Hello,
I have a table as such:
NAME RANK WIN_PERCENT DRAW_PERCENT
The table can have the same people in it multipe times, such as:
NAME LEVEL WIN_PERCENT DRAW_PERCENT
Bob 4 80% 4%
Bob 5 90% 5%
Bob 8 60% 3%
What I would like to do is return only the row in which Bob's Level is the Highest (MAX).
I wrote an aggregate query as such:
SELECT NAME, MAX(LEVEL) AS MAX_LEVEL, WIN_PERCENT, DRAW_PERCENT
FROM MYTable
GROUP BY NAME, WIN_PERCENT, DRAW_PERCENT
The problem is, as you'd expect, this query returns all rows, since I am grouping by WIN_PERCENT and DRAW_PERCENT as well.
Any ideas on what query to use to just return the row with the highest (MAX) LEVEL? i.e.
NAME LEVEL WIN_PERCENT DRAW_PERCENT
Bob 8 60% 3%
Thanks,
Scott
View 6 Replies
View Related
Nov 28, 2005
Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.
Thanks.
View 1 Replies
View Related
Feb 6, 2006
I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.
However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.
View 8 Replies
View Related
Mar 22, 2007
I want to be able to update stock levels by clicking a button on a form. The button executes code to run the updatequery. Details of Items are listed on a sales form. The fields include item code and quantity sold. Details of the items are stored in a table called 'Items' - fields include item code and quantity in stock. I want the clicking of the button to compare the item code in the form with the item codes in the Items table, and then reduce the quantity in stock by the quantity sold in the form.
Any help given would be much appreciated.
thanks
View 1 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
Jun 16, 2005
Hello! .... I think I posted this in the wrong section of the forum, so here it is again and hope someone's kind enough to help. Basically, I've been asked to show (in a form as well as a report) results on a Monthly, Quarterly and Year-to-Date basis. The table structure I currently have is as follows (additional fields and/or tables can be added if it would help get the results desired):
ID Date Volume/Value
A1 Jun-05 10,000
A2 May-05 15,000
A3 Apr-05 12,000
A4 ------ ------
An May-04 11,000
B1 Jun-05 10,000
B2 May-05 15,000
B3 Apr-05 12,000
B4 ------ ------
Bn May-04 11,000
Part of the trick with this is to be able to show these aggregate values (quarterly, YTD, and on a 13-month rolling basis) as users enter new data for whatever the current month is. Any assistance in helping me get this accomplished would be enourmeously appreciated.
View 1 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
Jul 19, 2005
I have an aggregate query that creates a report of "A" by "B". Each "A" can have many "B"s under it. The report works fine. I need to count and display on the report the number of "B"s for each "A". I can't seem to manipulate the "count" property of the query to generate the correct number. Little help??? Thanks....
Mike T
View 5 Replies
View Related
Jan 19, 2007
Hi Everyone,
I am working on a Sales Analysis db, which pulls from:
tSales - Sales Figures (40k records)
tBuildUnit - Bom Build Units (8k records - one per product)
tBom - Bills of Material - All Components (71k records)
tUom - Units of Measure for Products (80k records - 10 per product)
It is quite basic in theory, it looks at a product sold, goes to tBom to get the cost of the raw materials.
I have a query that sums the components by their group, distinguishing between material and labour cost by the component group (cGrp) which is 'WORK' for labour and <>"WORK for materials. I can then sum the cost of the materials for a given product.
Anyway to cut to the issue in hand.
I export from our system (LM4 on btrieve on novell 3.2) into CSVs then manipulate those
And basically i want to have:
Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Ma t06
Genesis 400 350 420 230 190 250
Exodus 0 100 0 0 60 0
Revelation 200 300 140 200
we have 220 customers in this recordset. so i would like just 220 rows.
My data that i export shows every transaction (so i have 40k records)
So i used a aggregate query, grouping by customer account and summing the value, material, and labour fields, however this gives me each customer three times, one per year e.g.:
Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Ma t06
Genesis 400 0 0 230 0 0
Genesis 0 350 0 0 190 0
Genesis 0 0 420 0 0 250
How do i get one what i describe above?
After the query runs i want to export to excel with:
DoCmd.OutputTo acOutputQuery, "qGrouping", acFormatXLS, "d:Output.xls", True
Thanks
Graham
(Carpenter by trade, now in construction acoustics and really shouldn't be doing anything with access!)
View 1 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
