asked on

Historically Showing time that isn't there

i'm running a query on an access database with a few tables in it that i'm attaching to this.

i'm having trouble getting all of my students to show, for a previous week in the past (historical). I've attached a sample of the database along with a query (within the database queries) to demonstrate my problem. In this case "Missing Thisone" and "Missing Thisonetoo" do not show up as "NO TIME" and they should as they have no time entered for the period specified. I don't know why though....the database should open with no problem if it asks for a password use: "test"

see question "Showing time that isn't there" (response IDs: 23776811 - 22670921) for other information on this.
HistoricSample.mdb

Hamed Nasr

For a better feedback, I suggest, you limit the number of records to say 5 or so in each table, then list the result of the query and the expected reult.
Compact and repair the database to reduce the size and attach.

Michael Sterling

ASKER

i can reduce the size of two of the tables. one i can reduce to 1 (Sport) and the other to about 35 (AthleteInfo) but the table that contains all of the time entries (In_Out), should probably only be reduced to all the records for the week i'm looking in don't you think? I have done what you suggested as best as i can and re-attached the database. Thanks.
HistoricSampleI.mdb

GRayL

Whenever you use a WHERE clause in a LEFT or RIGHT JOIN, it reverts to an INNER JOIN. That's why.

Hamed Nasr

You have 10 students in ai table, who are not referenced in io table.
When you link ai with io to list all ai with those equal from io and set criteria for io.StudentID Is Not Null you get,

AthleteInfo.StudentID      In_Out.StudentID      FirstName      LastName
dpb55746            Test      Student
jpb58511            Test      Student
ipd34840            Missing      Thisone
gth90100            Test      Student
rmk08243            Test      Student
rll61161            Test      Student
mdn98035            Test      Student
mcp10539            Test      Student
tjr47781            Test      Student
jrs72578            Missing      Thisonetoo

Please check and report back!

ASKER CERTIFIED SOLUTION

Michael Sterling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial