How To Sum Domain Aggregate Controls?
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 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
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 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
Jul 7, 2015
I'm looking to return different average values for each GICS Sector using this query:
Code:
SELECT [GICS Sector], DAvg("GM","tbl0"," [GICS Sector] = [GICS Sector] AND GM Is Not Null") AS GmMedianByGicsSectorFROM tbl0
WHERE [GICS Sector] = [GICS Sector]
GROUP BY [GICS Sector];
However, I am getting the same value for each:
GICS Sector GmMedianByGicsSector
Consumer Discretionary 0.469556278179105
Consumer Staples 0.469556278179105
Energy 0.469556278179105
Financials 0.469556278179105
Health Care 0.469556278179105
Industrials 0.469556278179105
Information Technology 0.469556278179105
Materials 0.469556278179105
Telecommunication Services 0.469556278179105
Utilities 0.469556278179105
View 6 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
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
Mar 10, 2007
I have a table in MS Access named Invoices containing the following values:
NameInvoice
Jim101
Jim102
Jane103
John104
Jane105
John106
John107
Jim108
Jim109
Jim110
The following query gives me the results you see below:
SELECT Name, Count(Invoice) AS CountOfInvoice
FROM Invoices
GROUP BY Name;
NameCountOfInvoice
Jane2
Jim5
John3
Ultimately, I want to design a SINGLE query that returns ONLY the Max(CountOfInvoice). In this case: 5.
Can anyone help me?
View 4 Replies
View Related
Feb 17, 2008
Hi All,
I have a table namely, leave_details wherein there is a column called DAYS and TDAYS,
DAYS columns represent number of leaves an employee has taken in a single leave application and
TDAYS represents Total number of leaves an employee has availled till date.
To calculate the TDAYS i need to SUM the DAYS column for every employee.
When an employee again log in the application he should be aware of the total number of leaves which he has already availed.
Please help me for the same as I am unable to do so.
Kindly revert back for any queries or any further information.
With Warm Regards,
Sanjay
View 3 Replies
View Related
