Solved

Historically Showing time that isn't there

Posted on 2008-10-08
5
207 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

772 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

16 Experts available now in Live!

Get 1:1 Help Now