(Noob) Need help with a query please

[kmccallsdsu]

I have a Classes table with the Class ID as the primary key.



I have a Homework table with Class ID as the foreign key as well as an attribute titled Due Date.

I want to implement a field in Classes called Next Assignment Due.

I need a query to find the next closest due date for that particular Class ID.

[kmccallsdsu]

Did I do something wrong? 50 views no replies...Just to clarify:

I am attempting to make a query that automatically fills out a field called [Next Assignment Due] in a table called [Classes]. In order to do this the query needs to go through a field called [Due Date] in a table called [Homework] and find the next closest date. The query also needs to make sure that the [Class ID] for that homework matches the [Class ID] for that class to make sure it is not finding the next assignment due for another class. Please and Thank you to anyone who helps.

[orange]

Please show us what you have tried.

Your second post indicates you have your logic sorted out, so what exactly is the issue?

Don't store calculated fields.

[kmccallsdsu]

Thanks for responding Orange I appreciate it. The point of this database is to keep track of my homework (and hopefully get me some Extra Credit). I input homework into a homework table. These are the fields:

Assignment ID-" An Autonumber for primary key"
Class ID-"Shows which class homework belongs to"
Name-"The name of the assignment"
Grade-"My grade for the homework in a number, not required"
Points Possible-"Maximum points in a number"
Due Date-"Date assignment is due"
Graded-"Yes/No field marking whether assignment is completed"

Example:
1
ECON102
Chapter 9 Homework
0
50
3/16/2011
Not Graded

Obviously just one table of all my homeworks is messy so I made a table called Classes. These are the fields:

Class ID-"Unique to each class this is also used as relationship with homework"
Title-"Name of the class"
Days-"text field just showing what days class is"
Time-"text field just showing what time of day class is"
Next Assignment due-"Here is the field I am working on. I want this field to show the title of the next homework due for this class."

Example:
ECON102
Microeconomics
Tuesday and Thursday
9:30am-10:45am
Chapter 9 Homework

So far, I created the field called Next Assignment Due in the Classes table using Lookup & Relationship. I selected a query I made called Next Homework Due Query. The Query in design view looks like this:

Field: Due Date
Table:Homework
Sort:------
Show:Yes
Criteria:>Date()
or:------

Field:Name
Table:Homework
Sort:------
Show:Yes
Criteria:------
or:------

What this does is shows the Due Date for every homework after today. To only return the next closest Due Date I made sure the values were Ascending and I changed the Top Values in properties to 1 so only one result is returned. So the query returns the Due Date and Name for the next closest Due Date. I don' think this works how I want it to though. Homework is not entered in according to ascending due date so I am pretty sure the query is just returning the first due date it finds that is greater than todays. I believe this part needs to be written by finding the smallest value of {todays date minus the due date} and returning that assignment. Another problem is the query is not making sure that the next closest Due Date is for the correct Class ID. Without this every class will show the same assignment, the one with the closest Due Date. I am not sure how to make this work and I feel like I am doing it all wrong haha.

A couple added notes:
---I don't care if the query only returns the due date for the next homework or just the name for the homework I can always create another field once i figure out how to write this query.
---At the moment when I go to fill in the fields under Next Assignment Due in the Classes table dropdowns appear for each class showing the result of the query. If possible I don't want to have to enter the value I want the query to automatically update the table. I think this is possible by changing the query type to update but I am not sure how to make this work either.

[kmccallsdsu]

This forum sucks.

[Buakaw]

Here's one newbie to another:

In query design view, add a new column called [TimeToMug]:[HomeworkTable].[AssignmentDueDate] - Now()

Then sort it as Ascending, and sort your entire query according to this column.

You can also elect not to display this query by unchecking the tick in Design view.

See if this does what you want.

[Buakaw]

In future, taking some screenshots of your tables and your Query design view screen might help in getting more responses.