Using the date to determine if something was late.

[lyrikkmashairi]

I am an extremely beginner Access user. I am not familiar with most terms or functions of Access. Please keep that in mind when answering this question. I want to create a query/report to show when someone submitted a report late. I have a field called "Submitted Date". I want to be able to automatically pull the names of the people that submitted late based on a specific date. For instance, if the deadline was 3/1/10, I want the query/report to show the name of the organization followed by either "On-Time" or "Late". Please tell me the simplest solution to this. I've read around and the answers are all too techy.

[NoellaG]

Hi,

I assume you have a table to store the reports, let's call it tblreports which has fields like:
repID (Primary key)
repName
repDueDate

a table with the names of the people, let's call it tblPersons, with fields as
prsID (primary key)
prsFirstName
prsLastName
...

and a table wich joins the two: let's call it tblPersonsReports, with fields as
pprID (primary key)
prReport (foreign key to tblreports.rptID)
prPerson (foreign key to tblPersons)
prSubmittedDate

Then you can create a query as:


SELECT tblReports.rptName,
tblPersons.prsFirstName,
tblPersons.prsLastName,
tblReports.rptDueDate,
tblPersonsreports.prSubmittedDate,
IIf([prSubmittedDate]<=[rptDueDate],"In time","To late") AS fldHandedIn
FROM tblPersons INNER JOIN (tblReports INNER JOIN tblPersonsreports ON tblReports.rptID = tblPersonsreports.prreport) ON tblPersons.prsID = tblPersonsreports.prperson;

[lyrikkmashairi]

I really, really am a very beginner user. So I need things to be described in excrutiating detail. Meanwhile, I don't have any tables linked to anything. I have one table with all of my information in it (2010Q1). The field for the organization name is "Allocatee". The field for the date they submitted is "Submitted".

I would, however, like to link the tables for each quarter (2010Q2, 2010Q3, etc.) on a report at the end to show who was late each quarter.

Thank you for any help you can give.

[NoellaG]

Hi,

I really think you should take some time to learn about database design and the basic things of working with Acces before you proceed. This really, really will save you a lot of time and trouble later on.

succes
NG