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 2740
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: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |