Query to show non-queried results?

[driver7408]

I'm still rather new to this. I am working on a database to track reading files. There are many names, and many reading files read by many, thus a many to many relationship. I have 3 tables: [Names], [FileRead], and [ReadingFile]. [Names] is a many to one with [FileRead] and [ReadingFile] is on a many to one with [FileRead]. I can query my tables and find the [ReadingFile] that [Names] already has been filed as read, but I cant seem to get the right query to show the [ReadingFile](s) that [Names] has not yet read. I need this info to show on the [Names2] form when the reader looks up his reading file info, so he can be directed to the unread reading file. Not sure if its my table structure, or an SQL statement I need to use. Still pretty new at this. Any help will be greatly appreciated. File is attached.

 

[vbaInet]

You will need to change the Join Type of [ReadingFile] to [FileRead] to display "All results from [ReadingFile]". Right click the join (in the Relationships view) and have a look therer. Then you will query using Is Null.

 

[driver7408]

Thanks, that works, but also need a list of the names that have not read the files, and the names of the unread reading files displayed next to those names. This is tough to describe in writing. I think it has to do with an SQL statement.

 

[vbaInet]

Then you are talking about creating two subreports. The first subreport will contain those with read files. For that one, don't change the join, leave it as you had it before.

The second one will then use the query I described above. Just put the two subreports side-by-side. You will need to watch out for layout.

 

[driver7408]

Is there any way to somehow put that information into an actual form and subform, so the user can go to that form and scroll through his own unread files from a list in his form, based on his name?

That way when he clicks on the hyperlink for his unread file, it will send him to the file, and then transfer that file name into the read file table under his name.

 

[vbaInet]

Same idea applies yes. Subforms. Do a quick search on how to create those.

 

[driver7408]

Thanks for your time. I'll try that out and report back.

 

[vbaInet]

Goodie. You're welcome.

 

[driver7408]

I think I am getting to the bottom of things, after much research and trial, with some recent corrections to my database. At this point I have two queries. In my current database, Query2 lists every name and reading file combination possible. Query1 lists only the reading files with the names of people who have already read them. How do I make a query to omit the results of query1 from query2 ? This will show all of the names next to all of the remaining reading files that they (names) haven't read yet, which is a vital piece of information for this database. Seems like it should be so easy to get this result, yet I'm completely stumped on how to do so. This is the last real piece of information I need in order to go on and finish this.

Please help.

 

[driver7408]

Nevrmind. After 27 hours I think my brain just melted and it finally clicked.

 

[vbaInet]

So all sorted now?

 

[driver7408]

Yeah, have the query figured out and it works perfectly, thanks. I was making things more complicated then they really were.

 

[vbaInet]

Glad you're happy with your solution

 

[Rabbie]

Yeah, have the query figured out and it works perfectly, thanks. I was making things more complicated then they really were.

Perhaps you could post your solution to help other people who are searching the forums

 

[driver7408]

The best I can do is include that portion of my database, showing what I did. I had to do 3 seperate queries to get the results. Query 1 and Query 2 were created, and than the 3rd query was run from those. Query 2 had all the people and all the files. Query 1 had just the people and the files they were associated with. In essence I "subtracted" the 1st Query's data from the 2nd one through the use of one sided joins. The best advice I can give is to be particular about making sure you get your joins right. The easiest way for me was to start by showing all the fields, and not use the "Is Null" feature. That way I could compare and see which fields were actually blank, to be sure I was getting the right information in my queries. Also I find it much easier to test queries with as little data in the database as possible (makes it less confusing.)