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.
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?!!!
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.
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!
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?
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...
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
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.
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 :)
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.
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
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
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?
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).
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.
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!
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.
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.
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.
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
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):
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.
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
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....
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
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.:
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(???)
