GroupBy / Max ? (newbie Alert! :-) )
Dec 29, 2006
I have what is probably a simple question for regular Access users. Before I ask the question, here is a simplified version of the tables involved:
Structure of EMPLOYEE
=====================
EmplID
Name
Structure of TRAINING
=====================
EmplID
CourseID
CourseName
DateTaken
Data in EMPLOYEE
================
EmplID----Name
1234------John Smith
5678------Mike Smith
Data in TRAINING
================
EmplID------CourseID----Course------DateTaken
1234--------NS01--------HeatStreet--06/15/2000
1234--------NS01--------HeatStreet--07/11/2001
1234--------NS01--------HeatStreet--02/07/2002
1234--------NS01--------HeatStreet--08/22/2004
1234--------NS01--------HeatStreet--01/28/2006
1234--------NS27--------Lockout-----01/06/2002
1234--------NS27--------Lockout-----01/27/2004
5678--------NS01--------HeatStreet--12/27/2002
5678--------NS01--------HeatStreet--08/11/2004
Its easy enough to join the two tables in the query and return ALL 9 training records, but I would like to find a way to display only the most recent occurance of each employee taking a course. ie, the max date for each unique EmplID-Name-CourseID-Course combination:
EmplID------Name--------CourseID----Course------DateTaken
1234--------John Smith--NS01--------HeatStreet--01/28/2006
1234--------John Smith--NS27--------Lockout-----01/27/2004
5678--------Mike Smith--NS01--------HeatStreet--08/11/2004
I thought maybe "GroupBy" on EmplID, Name, CourseID and Course and "Max" on DateTaken would do the trick, but apparently not. Is there a way to do this without code?
Thanks in advance........ :)
-SD
View Replies
ADVERTISEMENT
Jan 5, 2007
Hi
I wanna do something similar to what Stephanie.D tried to do before..I read the suggestion she recieved but doesn't seem to work for me. I have one table with the following
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
I'd like to create a query that only returns the rows of the table where the Price of the Name is the smallest. Here's an example...
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
1 drinks wine Porto Store1 5
2 drinks wine Porto Store2 6
3 food bread Baguette Store3 1
4 food bread Integral Store3 2
5 drinks wine Bordeaux Store3 3
It should return
==Id==TypeofProd==Name==SpecNameofProd==From==Pric e==
3 food bread Baguette Store3 1
5 drinks wine Bordeaux Store3 3
I tried doing a GroupBy but it only works when for the query I only use the SpecNameOfProd and Price fields. I tried using Where like it was suggested to Stephanie but I need the other fields to show...
Thx! for any help and suggestions
View 4 Replies
View Related
Jul 11, 2013
Im trying to run the following calculated query:
Total Value on order: Sum([Purchase Price])
I want to sum the total of all the purchase prices together so I know the total value on order. I keep getting an error: "You tried to execute a query that does not include the specified expression Purchase Order' as part of an aggregate function
View 2 Replies
View Related
Oct 22, 2013
I built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate.
Code:
SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID
FROM qryGroupbyWeek
WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end]))
ORDER BY qryGroupbyWeek.ID;
View 2 Replies
View Related
Sep 28, 2005
I am developing a database for our fleet of company vehicles. What I'd like to know is is it possible to set up some kind of alert where a dialog box appears (like the sort of thing in Outlook with reminders).
What I'd like is, for example, if a vehicle requires a service a pop up would appear, a couple of weeks before, when the database is opened to alert the user that it will be required soon.
If anyone knows how to do this it would be greatly appreciated.
View 1 Replies
View Related
May 17, 2005
When I start my database, I would like a field to be checked for todays date. If the condition is true, I would like it to use an alert to tell me that the condition is met. Any ideas?
View 1 Replies
View Related
Apr 15, 2008
Hi all, wondering if anyone can help me.
I have an access database, with a form called "Stock Maintenance", the controls on the form are QTY & Min QTY.
What i would like if possible, is once the Min QTY is below (10) an alert message/flag appears, saying QTY LOW Re-order Stock!!
I hope I have expressed what im trying to do.
any help on this matter would be greatly appricated.
View 14 Replies
View Related
Mar 17, 2006
Hope you guys may be able to help
I have a form with a sub form. On the sub form I have #Ordered, #Supplied & #Used. These fields calculate to give the user the # in stock. This figure is displayed on the form. On the form I also have the Reorder level which is set by the user.
I would like to be able to make a message alert pop up when the database is opened or when any of these records show the # in stock is lower than the Reorder level, ensuring that we do not run out of stock.
Many thanks for any help you can give.
View 1 Replies
View Related
Sep 30, 2004
My knowledge of access is pretty limited. Can anyone help me with this problem.
I have some due dates entered into the database via a form. Is it possible to send an email alert say 1 month and then 1 week before the due date?
View 4 Replies
View Related
Dec 5, 2004
I have a screen that has 8 different buttons and clicking on any of these buttons generates an appropriate letter and once the button was clicked in also inserts a date, today's date.
Private Sub btnSecondReport_Click()
On Error GoTo Err_btnSecondReport_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'* Call the standard letter printing routine for this form.
Call StandardLetterVariables("Bd$50paid.doc")
btnFirstReport.SetFocus
[DTSecondLetterSent] = Date
Exit_btnSecondReport_Click:
Exit Sub
Err_btnSecondReport_Click:
MsgBox Err.Description
Resume Exit_btnSecondReport_Click
So I would like to create an alert that looks at this date and 14 days from that date will alert, remind whatever I want to do.
I am thinking it wouldn't be too hard to create a reminder that pops up when the record is opened but I am sure users will want to be reminded without having to go into the record.
My question: can I and is it hard to create a reminder that will take care of paying attention when and who needs to be reminded or should I just create a report and let them run this.
I hope this makes sense and sombody has done this.
View 1 Replies
View Related
Jun 28, 2006
In my form I have a combo box. The combo box has several selections. I want a message box to appear after any but one of the selections within the combo box are selected. Thanks.
View 3 Replies
View Related
Jul 28, 2004
I need to know how to send an email to myself when someone adds a new record
to my database. If someone can help me it would be greatly appreciated.
Amanda
View 8 Replies
View Related
Oct 6, 2005
HI all,
I've been searching the threads, but to no avail! (be gentle - a curious beginner in a crazy world of demands!)
My database (table) has a date field, when this date is 'now()' I need to send a reminder email to a 1 collegue and 'cc' 2 others. And automatically change the background colour of the date field; say to red.
A step by step guide would be great, or a nudge in the direction of a good example would help,
Thanks for your time,
Id
View 1 Replies
View Related
Aug 25, 2006
I am hoping someone out there will be able to help me figure this one out! I have a database I just created which houses approximately data on about 500 physicians/practitioners - from their personal info to information regarding their licensures, liability coverage, and DEA. In order for us to be compliant with administration and government criteria we have to monitor expirations of their license, liability, DEA, and other things. We do this by sending letters/e-mails out 30 days in advance of expiration dates (which is a field in my database). What would really be great if when I bring up my database it would automatically give me a pop-up saying these licensures are expiring in 30 days and send out reminders. I am a rather new user to Access but I do recall a few years ago hearing something about databases that have crystal reports??? I am not sure if that is the same thing or not.
I do have parameter queries set up from date to date but it sure would be impressive if I could get it to do the above. Or maybe another way to do this. Any suggestions????????? CMElady in :confused: distress!!!
Thanks.
View 4 Replies
View Related
Sep 12, 2007
Hi everyone, I quite new to access, so I would like to ask for a little help. Is it possible to create an alert in a database related to date and time. Shortly the user needs to be alerted when a certificate expires on a specific date. Is it possible to do that in Access?
Thank you anticipated,
Bye,
Fehermaci
View 5 Replies
View Related
Feb 13, 2008
Hi
I am trying to create an alert function so that when a user open up a form and displays a record, it will also display any alerts that have been created for that record. e.g. missed payments etc.
I have created a table to store the alert data along with the job number to which the alert relates so in essence I can have more that one alert per job record. I amtrying get one of the alerts to display ok using the following code
alerts = DLookup("[Job _Number]", "Alerts", "[Job _Number]='" & Me.Job_Number & "'" And "[Start_Date]" >= Date)
You will see that I am trying to only display alerts that are within the valid date range (I have not yet included [End_Date] as I cannot get the code above to work.
Can anyone help me correct this code please?
The other thing is that because I am using a dlookup, I am assuming it will only display the first alert it finds in the table and that's it? If so, how do it get it to display all 'active' alerts with a matching job number. By active I mean where the Start date is >= Date() and End date is <= Date()
I would really appreciate any help on this..
Thank you all for reading.
View 4 Replies
View Related
Oct 24, 2005
I have an Access database keeping track of the number of cars registered under the company. The cars have details like type, make, license plate number, road permit dates, etc.
I would like to get alerts 3 days before the expiry date of the road permit. which is the best way to go about it?
Thanx in advance
View 2 Replies
View Related
Nov 9, 2006
ok.. i need to add an input textbox to my message alert..
i have this right now..
Option Compare Database
Private Sub Form_Open(Cancel As Integer)
MsgBox ("Enter the date:")
End Sub
i also need the value they enter to be saved as 'formdate'
then i will assign my date field to that value using something like
datefield = formdate
View 14 Replies
View Related
Aug 23, 2005
Hi,
I have a Data Access Page linked to an access database (of course!) and I would like to have a script in the submit button, so that it inputs the current time onto a particular field, and also throw up an alert box...could someone please help? I am not good at this, but it looks like it is javascript - thats currently on the button for the save action
Regards
Sunny
View 3 Replies
View Related
Sep 11, 2012
have a table with a list of employees with their badge numbers and the expiry date of their badges. I want to be able to run a query that will list/alert any badges that are due to expire with one months notice. How do I do this?
View 4 Replies
View Related
Aug 8, 2015
I have a continuous form that is belong to values of three unbound fields.
[TestName]
[FromDate]
[ToDate]
and a commandbutton that makes requery for this continuous form.
when i click on this command button , if any field is empty ; what's the appropriate code that i can use to alerts me the name of the forgotten field ?
View 1 Replies
View Related
Apr 27, 2015
Is there a way to have Access send an alert message when a particular date is getting close? I have a database that tracks automatic step increases to pay and when the dates occur. I would like to create a pop up that could alert me when a date is getting close so that I can inform the budget person that a certain employee will be getting a bump in their pay starting on a particular date? Is that possible in 2010?
View 3 Replies
View Related
May 18, 2015
I created the .accde to distribute only the compiled file. But I have a problem. When you open the file .accde there is this problem of security: You can not determine whether the content is from a trusted source. Leave off the contents, unless they provide essential capabilities and the source is considered reliable. Open the file or cancel the operation? How Can I avoid the visualization of this security alert?
View 11 Replies
View Related
Jul 16, 2014
Any way to send auto alert without opening Ms. Access program.
I have created a program which requires to send auto alert on certain dates. Due to some travel plans manager might not be able to open the program every day (might not open the program on certain dates where I set the alerts) and the chances are he will miss those alerts if he won't open the program.
View 2 Replies
View Related
Jun 18, 2013
I need to create a guest database for a small hotel. And I would like to create a pop-up alert that appears when I open MS Access to remind me birthdays of the guests.
View 1 Replies
View Related
Sep 20, 2005
Hi,
I have a form with some bound fields and some unbound fields. If I make any change to the bound fields I can use the "Before update" event to perform some actions when a user tries to move onto a new record.
What I now need is if a user doesn't make any change to the bound fields but makes a change to an unbound field - how can I check this before the next record is displayed. Is there any event I can use? Each time a user tries to move to a new record I want to check first if they have added anything to an unbound field.
Users can move onto the next record using buttons or the record navigator at the end of the form.
Many Thanks,
View 8 Replies
View Related