Can't edit results of query 3

[debbieg]

The result that I want is to create a form that lists students that have attended a class during this year. I want a checkbox next to each name so the user can choose which students to create an address label for. But first I have to get the right data.

Here’s my setup:

Students table
SSN
LastNM
FirstNM
etc.

OtherInfo table
SSN
PrintCheck (Yes/No field)
etc.

SumAcad table
SumID
SumBeginDT
etc.

SumAcadStudents table
SSN
SumID

There is a 1-to-1 relationship between Students and Other Info.
There is a 1-to-many relationship between SumAcad and SumAcadStudents.
Students can attend many classes in one year.

I set up these 2 queries:

qselSumAcadLabels (Unique Values = Yes)

SELECT DISTINCT SumAcadStudents.SSN, Year([SumBeginDT]) AS CurrYear
FROM SumAcad INNER JOIN SumAcadStudents ON SumAcad.SumID = SumAcadStudents.SumID
WHERE Year([SumBeginDT])=Year(Now());

qselSumAcadLabels2

SELECT OtherInfo.PrintCheck, [LastNM] & ", " & [FirstNM] & " " & [MI] AS Name, Students.LastNM, Students.FirstNM, Students.MI, Students.SSN
FROM (Students INNER JOIN qselSumAcadLabels2 ON Students.SSN = qselSumAcadLabels2.SSN) INNER JOIN OtherInfo ON Students.SSN = OtherInfo.SSN
ORDER BY Students.LastNM, Students.FirstNM, Students.MI;

The first query gets me the students that attended for the year. By attaching the first query to the second one I get the info for only those names. Well, the results are correct BUT I can’t edit the PrintCheck field.

What am I missing?

Thanks,
Debbie

 

[cghoga]

Make sure you have set the referential integrity (joined your tables) in the Tools - Relationships section.

 

[debbieg]

I have.

 

[dhookom]

You can't edit the query as long as you have "DISTINCT" in the first query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[debbieg]

So how can I make it so that the students who attended a class this year appear only once on my form so that I can use my checkbox?

Thanks for your help.

 

[cheerio]

I would try something like

SELECT fields 
FROM Students JOIN OtherInfo
ON Students.SSN= OtherInfo.SSN
WHERE Students.SSN IN
(SELECT  ....)

where the bracketed select gives you a list of the student SSNs that you are interested in. You could use a DISTINCT in the bracketed SELECT.

 

[debbieg]

cheerio,

I'm sorry to be so dense but I don't have a clue how to do what you suggest. How would I display students once that have attended a class this year and be able to edit the checkbox?

Is there a way to display the record for each student with the most current date in sumBeginDT that is in the current year?

Thanks.
Debbie

 

[Olaf4]

I did it. Perfect!!!
What dhookom and cheerio said it is worth a star!
Do this.
1. Create a new query. Put the Student table and create the field for the Name. (It will look like your qsel#2)
2. Now see this. It is your query a little modified: In ((SELECT DISTINCT SumAcadStudents.SSN
FROM SumAcad INNER JOIN SumAcadStudents ON SumAcad.SumID = SumAcadStudents.SumID
WHERE (((Year([SumBeginDT]))=Year(Now())))))
3. In the SSN criteria field of the new query put a copy of the above statement.
4. Run the query.
Thanks to dhookom and cheerio

 

[debbieg]

Olaf4,

That works!!!!!

I can't begin to thank you enough!!!!!

What would I do without this group!

I'm back in business!

Debbie

 

[debbieg]

Olaf4,

Just another question. What kind of query is this? I have several Access manuals and I want to read up more on what was accomplished. I also want to understand why this worked so if I need it again in the future. And so I don't have to bug people like you.

Thanks so much again,
Debbie

 

[dhookom]

Any time you see extra "SELECT..." in a query, this is [blue]subquery[/blue] (except union queries).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Olaf4]

You did well. I'm glad you're happy now.
Your two first queries were correct. But the trick (suggested by dhookom & cheerio) was not to join them (like you did) but nest them (put one inside the other one).
Join them is like compare two tables and you cannot make changes. Nest them acts like a filter, then you can make changes.

 

[debbieg]

Olaf4,

Because of how my database is structured, I have used this code in several places now. You have saved me so much time and effort!

Thanks again!
Debbie