I hope this make sense...
When students leave school (dropout, medical reasons, etc), there is a date field that admission use to record this event. For program evaluation, I want to know what program (Electronics, Nursing, Drafting, etc) they were in or assigned at the time when they went on leave. My problem is that some times students switch programs several times and I only want the closest assignment program to the Leave date range.
So for instance:
I have the fields Leave_Begin_dat e and Assignment_begi n_date
I enter a date range between 2/5/07 and 2/15/07 for the leave_begin_dat e, the query should return 17 cases; however because some students switch departments I get 42 cases with some “duplicates and triplicates. Because I don’t care any assignments greater than the Leave_begin_dat e I use Assignment_begi n_date<= to the leave_begin_dat e so this gets rid of anything higher than the leave date range 32 cases now. So here is where I need assistance
For instance:
John Doe, Assigned to Electronics 1/1/07 (don’t need it)
John Doe, Assigned to Drafting 1/15/07 (don’t need it)
John Doe, Assigned to Nursing 2/1/07 In this case I only want this
John Doe, Assigned to Manufacturing 2/20/07 (this will be eliminated with <= Leave date)
Please let me know if additional information is needed it such as sql or meta data.
Jose
2  2751  Rabbit 12,516
Recognized Expert Moderator MVP
You're probably looking at a subquery or DLookup(DMax()) . Meta data would be helpful.
 ADezii 8,834
Recognized Expert Expert
I hope this make sense...
When students leave school (dropout, medical reasons, etc), there is a date field that admission use to record this event. For program evaluation, I want to know what program (Electronics, Nursing, Drafting, etc) they were in or assigned at the time when they went on leave. My problem is that some times students switch programs several times and I only want the closest assignment program to the Leave date range.
So for instance:
I have the fields Leave_Begin_dat e and Assignment_begi n_date
I enter a date range between 2/5/07 and 2/15/07 for the leave_begin_dat e, the query should return 17 cases; however because some students switch departments I get 42 cases with some “duplicates and triplicates. Because I don’t care any assignments greater than the Leave_begin_dat e I use Assignment_begi n_date<= to the leave_begin_dat e so this gets rid of anything higher than the leave date range 32 cases now. So here is where I need assistance
For instance:
John Doe, Assigned to Electronics 1/1/07 (don’t need it)
John Doe, Assigned to Drafting 1/15/07 (don’t need it)
John Doe, Assigned to Nursing 2/1/07 In this case I only want this
John Doe, Assigned to Manufacturing 2/20/07 (this will be eliminated with <= Leave date)
Please let me know if additional information is needed it such as sql or meta data.
Jose
Assuming your Table Name is tblDROPOuts, this SQL Statement will work but I'm sure there is a better solution. SQL is not my strong point. - SELECT TOP 1 tblDROPOuts.Name, tblDROPOuts.Leave_Begin_Date, tblDROPOuts.Assignment_Begin_Date, tblDROPOuts.Program
-
FROM tblDROPOuts
-
WHERE tblDROPOuts.Assignment_Begin_Date<=[Leave_Begin_Date]
-
ORDER BY tblDROPOuts.Assignment_Begin_Date DESC;
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Russell |
last post by:
I'm having a fit with a query for a range of dates. The dates are
being returned from a view. The table/field that they are being
selected from stores them as varchar and that same field also stores
other fields from our dynamic forms. The field is called
'FormItemAnswer' and stores text, integer, date, float, etc. Anything
the user can type into one of our web forms. The query looks like,
select distinct from...
|
by: Douglas |
last post by:
I have a Vehicle MOT field in my table which i have as a Date field
I dont really want to hold the year, just 'dd mmm' as MOTs are the
same date every year.
I have the field on my form as a listbox holding all the dates of the
year, except 29 Feb which i dont need anyway.
When i select a value from the list i.e. 14 May and it stores it in
the table i believe it stores the current year as well i.e 14 May 2004
The problem now is how do i...
|
by: BlackFireNova |
last post by:
I have an Access 2003 mdb which contains software records. I need to
sort on a particular type of software, and then identify and count how
many copies there are per each group of that type purchased on the same
date.
I have no trouble doing a query to extract the type (say MS Excel 2002,
for example). The trouble is, there could be 50 copies purchased on
date "x", 80 copies purchased on date "y", and 250 copies purchased on
date "z",...
|
by: Kenneth |
last post by:
I have a Query that consist of a lot of different sales data, and one of the
colums are different date. The date goes from 1jan2003 til 31jan2003. in
this Query I only want the salesdata for 1jan2003. How do I remove the dates
, 2jan2003 til 31jan2003 without removing them from the table, from the
Query? (Because I want to use the data for 2jan2003 etc later in other
queries)
-kenneth
|
by: Kissi5559 |
last post by:
Hello,
Could anyone help me with a query? I have a date field and want to display only
records for a specified month.
Example:
Date:5/12/2004
Query for all records in the month of May, 2004.
Thanks.
| | |
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date ranges, ie between 24/09/2004
and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?
If I enter the "Between" criteria on different lines it returns no data.
|
by: MostlyH2O |
last post by:
Hi Folks,
I have a query that joins 3 tables. One of the tables (SalaryData) has data
where there may be duplicate records with different dates. Of those
duplicate records, I want the query to show me only the one with the latest
date. The query below shows me all the records - including the duplicates
with older dates. I have tried various ways of using the MAX(DateField)
But can't get it to work. I need to keep the joins the way...
|
by: www.ttdown.com |
last post by:
Each day a user is supposed to import a list of information into a
database table. The user only has 30 days to import this information.
After 30 days the information is lost. I need to be able to create a
form or a report or something that a user would be able to review and
show that a day was missed doing the import (indicating they forgot to
import that days information).
The table the information is being imported into is called...
|
by: Luvin lunch |
last post by:
Hi,
I'm new to access and am very wary of dates as I have limited
experience in their manipulation and I know if they're not done
properly things can turn ugly quickly.
I would like to use a calendar control to allow my users to enter a
date but I need them to enter a time as well. It doesn't look like the
calendar control will allow times to be entered so I was thinking of
having two text boxes. One text box would contain the date...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
