Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Historically Showing time that isn't there

Posted on 2008-10-08
5
Medium Priority
?
216 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:Michael Sterling
  • 2
  • 2
5 Comments
 
LVL 31

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:Michael Sterling
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 31

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:
Michael Sterling 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

972 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