Help with Database

[chinita18]

Hello,

I am new to Access and has decided to use it for my work as an assistant for an online learning institution. I have used the template from the Office for student database and I've tried everything with how I understood Access but I really needed help.

I have attached as a picture the screenshot of what I need help.

I need to be able to generate an inactive list, where a student has not submitted any tasks for a certain period of time (approximately 2 months). Can someone help? Appreciate it very much.

C.

[alansidman]

Suggest you attach a copy of your database. I doubt anyone has the time to rebuild your db from the information shown to figure out your issues.

Alan

[chinita18]

Hi Alan,

Thanks for your reply. Below is the link to download the file (it will not attach because it is 5MB size), I got this from the template of MS Access - Student Database and just modified some tabs to suit what I needed.

Fortress Learning_Sample.accdb

I only need to be able to generate a report that will give me a list of students who were inactive for a particular period of time, the picture I have attached is for the attendance so that's where I will be getting the information to know that a student has been inactive for 2 months...

Thanks for your help. I'm new to access to I just try what I read in the tutorials..

Ces

[alansidman]

When I tried to download, Norton blocked it as it says the site is unsafe. Suggest you find an alternative or reduce the size of the db by only having a small sample data and then running a compact and repair before uploading.

Alan

[chinita18]

Hi again Alan,

Sorry about the, can you try if this works? I have uploaded this to Google docs so hopefully this is a safe site now..

https://docs.google.com/leaf?id=0B1W...yZTEw&hl=en_US

Thanks a lot.

Ces

[alansidman]

Downloaded but unable to open. Running AC2007 and apparantly AC2010 file is unrecognizable. Either convert to an earlier version or hope someone else can jump in here.

Alan

[chinita18]

Thanks Alan for your response, I have converted the file but I guess there are some features missing here from the Access 2010 so I wasn't sure if this will work. Let me know, please I really need some help.

Here is the link to the converted file - https://docs.google.com/leaf?id=0B1W...2Zjg0&hl=en_US

Thanks.

Ces

[alansidman]

This SQL statement shows the last date that a student submitted a task more than sixty days ago.

SELECT [Student Attendance].Student, Max([Student Attendance].[Attendance Date]) AS [MaxOfAttendance Date]
FROM [Student Attendance] INNER JOIN Students ON [Student Attendance].Student = Students.ID
GROUP BY [Student Attendance].Student
HAVING (((Max([Student Attendance].[Attendance Date]))<Date()-60));

Alan

[chinita18]

Hi Alan,

Thanks for this. Sorry to ask, where do I put these statements? Do I make a query and the enter these statements?

Also, am I not able to just provide a date or like a date range to be able to generate the list?

Ces

[alansidman]

Create a query and select SQL from the drop down. Insert this SQL statement. Open the query in design view to see what the QBE looks like. Your questions lead me to believe that perhaps you need to take a tutorial on databases. A good starting point is here.

http://www.databasedev.co.uk/general.html

Often times you are better off in building a db from scratch and not taking someone else's template and trying to modify it.

[chinita18]

Thank you for this, I will read the tutorial.. I also downloaded some articles about Access and I just need to really read and focus to be able to really understand them.

I think I know what you were talking about now. I tried this and it worked, but it gave me all the list of students who haven't submitted anything even since last year. I was wondering if I am able to set a specific date to generate the list? Like I want to get the list of students who have been inactive for 60 days from today Sept 8, 2011. Is there a way to do this?

Ces

[alansidman]

In the criteria for attendance try: Between Date()-60 and Date()
In the tasks criteria try: Is Null