DLookup/Aggregate Domain Functions
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 Replies
ADVERTISEMENT
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 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
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
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
Aug 8, 2005
We have a database created in access 2000 laying on a server that two clients connect to using access 2000 from WinXP Pro. We have just moved the database from our old win2000 server to a 2003 server. The clients now logon to a 2003 domain instead of the old 2000.
Since we moved to 2003 only one user can open the database at a time, when the second user tries to open nothing happens. There's no error message, there's just nothing happening! When the first user close access the other user can open the db.
What's going on????
View 6 Replies
View Related
Sep 21, 2005
All,
I tried my damnedest to find something about this elsewhere in the forums but drew a blank.. so i figured I'd see what asking the question direct yeilds.
I would like to initiate workgroup level security to my database. but have no password required because i want to tie it to the username that is logged into the computer when the user logs into the domain initially? any thoughts?
View 2 Replies
View Related
Jun 21, 2013
I have a form for my customers where i also fill in the email adresses, know i don't wan't to fill in the hole email adres each time. I have seen a acces database before that gives a list when you typed the @ sign in the email adres field.I know that the builder used a tbldomain for listing al the domains that the putin the database. and each time you tab to the field emailsadres you begin to type
example.. markrutten@ and when you type the @ there popup a list of the table [tbldomain] en you begin to type for example Hotmail.com but you can hit the entrebutton afther the letters hot because the match of Hotmail.com is already in the field.
View 6 Replies
View Related
Dec 10, 2013
I need to deploy an Access db to about 8 PC's in a domain.I use the "Linked Table Manager" to set the connection details to MS Sql Server using an ODBC data source.My problem is when I have it running on 1 PC in the domain I thought I could just copy it and put it onto other PC's and it would just connect to the database with no issues, but I get an ODBC error and I need to re-create the ODBC source again on that PC.
How can I deploy the Access DB with out having to do the ODBC settings again.
View 9 Replies
View Related
Jun 14, 2005
Might be a stupid question as I know a lot of access but it's the first time I'm doing this:
What's the easiest way to deploy an access database (mdb) on a domain network to clients?
View 3 Replies
View Related
Feb 7, 2007
:confused: Hi all,
I have a user that cannot open .mde files on a share
that he DOES have access permissions to. He gets this Access error on his desktop when he tries to hit the share:
Not a Valid Account name or Password
I can log onto the resource with his username, and password from my
desktop with no problem.
What am I forgetting ?
Thanks,
Scott
View 1 Replies
View Related
Oct 15, 2013
I am currently working on small access 2010 database where the database consists of a table and one input form. This input form will be mainly used by two persons. In the form there is a dropdown where one selects his name to keep record who entered the data, however I would like this to be according the logged in domain account. This will make sure none of them selects the wrong user by mistake.
Is there a way how get the current logged on domain account into the form ?
View 5 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
Jun 24, 2015
I am currently the administrator of a fairly large network and am trying to keep inventory of all my domain assets. I would like to be able to search for these assets by a number of different ways such as computer name, location name (ie: CEO office, accounts receiveable, or human resources), slot number on my switches (I would like to know whats connected to where), and also what switch it is located on. Right now I am using a combo box to populate these fields but would like to implement a search function instead to be able to search with whatever information I have.
View 1 Replies
View Related
Sep 2, 2013
I need to send email with attachment on click of a button. We use emails like name@basepost.biz which is assigned to gmail.I tried the following code but it throws transport to server error.
Dim cdoConfig
Dim msgOne
Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
[code]...
View 7 Replies
View Related
Oct 14, 2013
I have created a query with the parameter for the Domain field. however on the form the user enters this information via a drop down menu. i was just wondering could the parameter box be set to a drop down box as well to save the user entering in the full Domain field name?
View 6 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
Nov 21, 2005
Hi,
I have a tricky question. I have a table like this one:
ID ID2 Value
----------------
1 1 Apple
2 1 Pear
3 2 Apple
4 2 Banana
5 2 Pear
6 3 Apple
And I would like to get a query result like this one:
ID2 AggregateValue
-----------------------
1 Apple-Pear
2 Apple-Banana-Pear
3 Apple
In MySQL I would use the Concat() function with a group by (not tested tough). But in MSAccess?
Thanks in advance,
Massimo
View 1 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
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
