Solved

Historically Showing time that isn't there

Posted on 2008-10-08
5
208 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:mikesExpertExchange
  • 2
  • 2
5 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 22673434
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.
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 22674460
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22674603
Whenever you use a WHERE clause in a LEFT or RIGHT JOIN, it reverts to an INNER JOIN.  That's why.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22675047
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!
0
 
LVL 1

Accepted Solution

by:
mikesExpertExchange earned 0 total points
ID: 22675287
GRayL: ok so how do i make my LEFT JOIN remain a LEFT JOIN?

hnasr: Can I see the query that you wrote? did you alter the query in the database that i sent? if so can i see what you changed. I don't completely understand your suggestion. I added
"AND io.StudentID IS NOT NULL" to my main WHERE clause at the bottom but got the same results...
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now