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_date and Assignment_begin_date
I enter a date range between 2/5/07 and 2/15/07 for the leave_begin_date, 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_date I use Assignment_begin_date<= to the leave_begin_date 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 2737
You're probably looking at a subquery or DLookup(DMax()). Meta data would be helpful.
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_date and Assignment_begin_date
I enter a date range between 2/5/07 and 2/15/07 for the leave_begin_date, 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_date I use Assignment_begin_date<= to the leave_begin_date 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...
|
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |