I've inherited a number of databases where each db had all of the required data in one LONG table. I've pretty much got them normalized and need to be able to calculate charges per student based on our Fiscal Year which runs 7/1/ to 6/30 of any year. I've tried the BETWEEN statement but I either get an error message stating a Data Type mismatch (using short date) or no data in my table (using the date as text).
I've had 2 basic classes in Access and I've tried a variety of queries in SQL view without much luck. I've attached just the table as I have a lot of personal info in the other tables.

Any suggestions or help would be greatly appreciated.

You should not have any trouble doing this. I created a query against the table you posted to return records between 7/1/2005 and 6/30/2006:

SELECT [Anselm Award amounts].Amount, [Anselm Award amounts].Semesterdate
FROM [Anselm Award amounts]
WHERE [Anselm Award amounts].Semesterdate Between #7/1/2005# And #6/30/2006#;


Because your Semesterdate field is a date field, you can only filter it by specifying the dates on which you want to filter, as shown above.

If you want to be able to vary the start and end dates, the way to do that dynamically would be to create a form with two controls on it, one for the start date and the other for the end date. in the query, set the parameters to refer to those controls.

SELECT [Anselm Award amounts].Amount, [Anselm Award amounts].Semesterdate
FROM [Anselm Award amounts]
WHERE [Anselm Award amounts].Semesterdate Between Forms!frmSemesterChoice.txtStartDate And Between Forms!frmSemesterChoice.txtStopDate;


Each time you enter new start and stop dates in the controls on the form, the query will return a different set of records.

HTH

George

Thank you so much! It worked perfectly and I was able to get totals for each student based on different FY's, plus a count of courses they have taken. This makes my job so much easier!
I remember trying this at one point but for some reason an error message popped up so I discarded it and tried something else. I am so glad that I kept at it because I really need to be able to get this info for reports due at the end of June 2007.
You should have seen the original table--70 columns!
I will try your second suggestion too but bear with me as I may have to ask for more assistance. I'll let you know how it goes.
Thank you again,
Carolyn

No problem. That is why the UA forums exist.

George

Ok I found another problem.
I ran the following query:

SELECT Min([Anselm Award amounts].[ID#]) AS [MinOfID#], [Anselm Award amounts].Semesterdate, Sum([Anselm Award amounts].Amount) AS SumOfAmount, [Candidate Personal Information].First, [Candidate Personal Information].Last
FROM [Candidate Personal Information] INNER JOIN [Anselm Award amounts] ON [Candidate Personal Information].[ID#] = [Anselm Award amounts].[ID#]
GROUP BY [Anselm Award amounts].Semesterdate, [Candidate Personal Information].First, [Candidate Personal Information].Last
HAVING ((([Anselm Award amounts].Semesterdate) Between #7/1/2005# And #6/30/2006#));


I keep getting all of the records for each student between those dates but I really want just a total count of the dates and a total amount for the fiscal year. I've tried DISTINCT, COUNT, MIN, and MAX but I keep getting the same data no matter what I try.
I haven't tried your report suggestion but I wonder if that may solve my problem?
Eventually I want to design a data entry form that will mimic our Financial aid application with controls so that I can avoid data entry errors, but first things first I have to get these queries run (correctly) and the rest of the db's in order.

Thank you in advance!
Carolyn

You can't do a SUM and a MIN in the same query. SUM has to include all records to calucate the sum. MIN would only work with a single record. That's inherently contradictory.

You'll need to create two queries, I believe, one for the MIN calculation and one for the SUM calculation.

George

Actually my biggest concern is getting a total per student for any FY without duplicates or separated records. What I am finding is that Student A may have taken 3 courses in an FY, 2 in one semester, one in another semester. I want a total per year, not per semester which it seems I am getting when I use SUM and the FY as an actual date. If I use simply the year as my criteria then I am able to get totals per student per FY. But that means I have to be really careful when the data is put into the tables. I am leaning towards this as my solution unless someone sees another better way.

To complicate things I have different FY's based on where the students go to school, one is 7/1-6/30, the other is 9/1-8/31. I can have scenarios where one student may complete a certificate program at one school
(FY 7/1-6/30), then go on and do grad work at another (FY 9/1-8/31) but I have to track the grant money up until a certain amount so I need to find a way to combine these two databases.

Any suggestions?

George,

I'm pretty sure you can do both in the same Totals query. The Min column will return the minimum value for the selected field from the recordset while the Sum will sum the field for the entire recordset.

Thanks, yes. I stand corrected. In the case of one field for min date and one field for Count or Sum, the OP would get the result they wanted. I broke my long-standing rule never to leap to conclusions without testing.

George

OK I changed my FY to a text field and simply use the year as my search criteria (2003, 2004, etc.). I base the FY on the actual semester (another column) when the student took the course. This returns what I am looking for with the correct FY totals and without duplicates.
It is unfortunately, duplicate data albeit in another form and column in the same table, but that is what I will need to do for now until I can find a better way. Unless I split the table and have one table just list the courses and semesters, while the other lists the FY and the amounts, but no matter how you slice it, it is still dup data.

I have tried doing MIN and SUM as well as MAX and SUM but if I use MAX, even though my criteria specifies a certain year, I get the total for a student for ALL years; I'm not sure what MIN is giving me, but it isn't the FY total.

Carolyn

MIN gives the minimum value for the field out of all the records returned by the criteria.

You need something like:

SELECT [Candidate Personal Information].First, [Candidate Personal Information].Last, Count([Anselm Award amounts].[ID#]) AS AwardCount, Sum([Anselm Award amounts].Amount) AS SumOfAmount
FROM [Candidate Personal Information] INNER JOIN [Anselm Award amounts] ON [Candidate Personal Information].[ID#] = [Anselm Award amounts].[ID#]
GROUP BY [Candidate Personal Information].First, [Candidate Personal Information].Last
WHERE [Anselm Award amounts].Semesterdate Between #7/1/2005# And #6/30/2006#

I strongly recommend you do not use the octothorpe (#) or spaces, or any other non-alphameric characters in the names of any field/object/control. They will cause problems, sooner or later.