Query based report showing wrong column/field... (1 Viewer)

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
Good morning All !
I am trying to setup a MS Access database (MS Access 2019) to facilitate lesson plans creation for my school.
So far, everything is working fine (Tables, Form, Query), but when I generate the report, based on the query, I have three textbox (Teaching_Materials, Homework_type, and Past_Paper_type) which are displaying the wrong column/field....
Instead of displaying, for example the "Short_Name_ field", the "ID" field is displayed in the report...When in the Query, it shows the text (multiple values), as expected....
Any hints?

lessonplan.jpg
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 28, 2001
Messages
26,999
Show us the text of the query - the SQL view. The exhibits you have posted tell us the "what" but the SQL will tell us "how."
 

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
Here is the SQL code for the query I use for the report!

SQL:
SELECT Lesson_PLans.*, Calendar.School_Year, Calendar.Week_Number, Calendar.Start_Date, Calendar.End_Date, Calendar.Term, Calendar.School_Days, Calendar.Status, Syllabus.Subject_reference, Syllabus.Chapter_Name, Syllabus.Topic_Category, Syllabus.Sub_Topic_Text, Syllabus.LO_Title, Syllabus.Coursebook_ref, Syllabus.Workbook_ref, Subjects.Full_Name, Grades.Full_Name, Syllabus.Subject_Code, Syllabus.Sub_topic_Code
FROM Grades INNER JOIN (Subjects INNER JOIN (Syllabus INNER JOIN (Calendar INNER JOIN Lesson_PLans ON Calendar.ID = Lesson_PLans.Week.Value) ON Syllabus.ID = Lesson_PLans.Subtopic.Value) ON Subjects.Code = Syllabus.Subject_reference) ON Grades.ID = Lesson_PLans.Grade.Value;

Thanks for your help!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:49
Joined
Feb 19, 2002
Messages
42,971
Sounds like you are using table level lookups. Go into the table in design view and remove the lookup. Then change your query used for the recordsource of the report and add a join to the lookup table so you can pick up the text value.
 

bastanu

AWF VIP
Local time
Today, 11:49
Joined
Apr 13, 2010
Messages
1,401
I think that is caused by a multivalue field (which is a type of lookup) so you would need to replace it with a join table (and a form\subform structure for adding and editing those 1-to-many relationships).

Here is some more info on this topic:
See Colin's link in this older thread:

Cheers,
 

June7

AWF VIP
Local time
Today, 10:49
Joined
Mar 9, 2014
Messages
5,423
Another option is to keep and deal with the table lookup (removing can be tricky, especially if built with Lookup wizard). If including lookup source table in query does not fix multi-value (I expect it won't), maybe instead build combobox same as on form. Should appear as textbox on report in Report or Print view. However, possibly will not grow like textbox. I don't use table lookups nor multi-value fields.

If you do eliminate the multi-value and instead go with normal dependent table, and you still want data to appear as CSV string in textbox, that would require VBA http://allenbrowne.com/func-concat.html
 
Last edited:

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
Thanks all for your advice, I start to understand the problem!
Now, I removed the Lookup from the original table, added the "Materials_type" table in the query, with the appropriate relationship!
It works perfectly when I have a single item, but I am stuck when I have multiple values, and my VBA skills are inexistant....

For example, when in the main table, I have "Teaching_Materials_Type" stored as "1, 3, 5", which should match "Past_Papers, Video, Presentation", so in the "Lesson_Plan" report should display the names, and not the reference, I just can't have it working...

My question now is, ho can i store multiple values in the main table, when the user is filling the form for each lesson plan (combo box?) and then link them to the Teaching_Materials_Type table so the corresponding texts for the multiple values are displayed in the report?
 

June7

AWF VIP
Local time
Today, 10:49
Joined
Mar 9, 2014
Messages
5,423
Did you try combobox on report as suggested in post #6? I just did a test. It works.
Just drag the field from Field List then set CanGrow property to Yes.
 
Last edited:

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
I can't get the way to do it...
Now, I just managed to achieve displaying the "Text" instead of the code, but the report generate one page for each "teaching material" instead of having the multiple values together....
 

June7

AWF VIP
Local time
Today, 10:49
Joined
Mar 9, 2014
Messages
5,423
Then you did not use combobox? It's just a combobox same as on form. No need to include Teaching_Materials_Categories table in report RecordSource.
 

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
when i ad a combobox in the report, it ends up empty.....
 

June7

AWF VIP
Local time
Today, 10:49
Joined
Mar 9, 2014
Messages
5,423
If you want to provide db for analysis, follow instructions at bottom of my post.
 

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
Thanks a lot!
 

Attachments

  • Lessson_Planner.zip
    4.7 MB · Views: 145

June7

AWF VIP
Local time
Today, 10:49
Joined
Mar 9, 2014
Messages
5,423
I prefer to set db for Overlapping Windows instead of Tabbed Documents. This allows moving objects and seeing them in design view side-by-side.
If Teaching_Materials is the only field you want to have multiple values then other fields with lookup should have Allow Multiple Values set to No.

Remove Teaching_Materials_Categories table from query.

Put combobox for Teaching_Materials on report in place of textbox. Pulling from Field List doesn't work because report RecordSource is a query. Copy/paste it from the form or build from scratch.
 

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
Just did it, but now that the lookup is gone from the Lesson_Plan table, I still end up with the "Teaching_Materials_Codes".... note the text.....:(

1652694770344.png
 

yvautrin

New member
Local time
Today, 20:49
Joined
May 14, 2022
Messages
9
All Good!

I just copied and paste the combobox from the Form, and it works like a Charm!

A Trillion thanks for your help! (y)

Now, time for some cosmetic work!
 

bastanu

AWF VIP
Local time
Today, 11:49
Joined
Apr 13, 2010
Messages
1,401
@Pat Hartman - I know that Pat, that's why I said it is a type of lookup because you can't set up a multi-value field without the Lookup wizard. The problem for the OP was showing the concatenated text values instead of the concatenated bound IDs; if you just eliminate the lookup as proposed then you also loose the ability to have multiple values which I assumed the OP needed. So I suggested the lookup table approach to replace the multi value field. In any case it seems everything is working as expected now.

Cheers,
 

June7

AWF VIP
Local time
Today, 10:49
Joined
Mar 9, 2014
Messages
5,423
Can definitely set up multi-value/lookup field without Lookup wizard. Just directly set the Lookup properties.
 

bastanu

AWF VIP
Local time
Today, 11:49
Joined
Apr 13, 2010
Messages
1,401
Yes, in the Lookup tab of the field in table design, isn't that called the lookup "wizard"?
 

Users who are viewing this thread

Top Bottom