UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Report Displays The Same Image In Each Record, Access 2016    
 
   
ciapul12
post Oct 14 2019, 09:22 AM
Post#1



Posts: 293
Joined: 7-June 14



Hi All,
I've got a rather annoying problem. my report displays the same image in every record and despite my attempts to resolve this issue I have yet to find solution that would work.
Everything was fine until I've changed the way I store images. In the past I had an attachment field and all images were physically stored in database which posed an issue with max capacity of database as it grew to over 2gb pretty quickly.
I have decided to store all images on a network drive and only to store path to each image in database.

My report have been also modified to accommodate this and they all work apart from this weekly report which is slightly different because I have 4 columns (see attached photos for reference)

My unbounded image frame is linked to a field called ImagePath which contains path to an image on network drive. My report query has a "Is Not Null" condition so in theory any record that has no data in ImageLink field should be excluded from query but as you can see by the photo these records still get included and the same image appears in each one of them...

What am I doing wrong here?
Thanks You in Advance
Dan
Attached File  Capture.JPG ( 97.9K )Number of downloads: 9

Attached File  Capture2.JPG ( 101.51K )Number of downloads: 4

Attached File  Capture3.JPG ( 115.28K )Number of downloads: 2

Attached File  Capture4.JPG ( 151.99K )Number of downloads: 6


Go to the top of the page
 
arnelgp
post Oct 14 2019, 09:32 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


make ImagePath image control Bound by adding ImageLink field as its Control Source.
This post has been edited by arnelgp: Oct 14 2019, 09:33 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
ciapul12
post Oct 14 2019, 10:20 AM
Post#3



Posts: 293
Joined: 7-June 14



Done that already and still doesn't work...
Attached File  Capture5.JPG ( 131.47K )Number of downloads: 3
Go to the top of the page
 
ciapul12
post Oct 14 2019, 10:37 AM
Post#4



Posts: 293
Joined: 7-June 14



Just run my query and I can see that issues sits with query somewhere as you can clearly see repeated image paths across multiple records. Don't have a clue how to fix this tho lol
Attached File  Capture6.JPG ( 339.48K )Number of downloads: 9


Go to the top of the page
 
GroverParkGeorge
post Oct 14 2019, 11:32 AM
Post#5


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Based on the peak we've had from the screen shots, my first inclination would be to want to see the actual TABLE called Responses. I mean the data in it. It looks very much like the image field in it points to the same file for every single record. Is that the case?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
chrismbaylis
post Oct 14 2019, 01:50 PM
Post#6



Posts: 104
Joined: 25-March 10
From: St Albans, UK


Hey Dan - it looks as though your Data is repeating itself - the change to each new image starts the repeat again - this suggests the table order in the query may be incorrect and is resulting in duplicated (or 1:n) data
Go to the top of the page
 
ciapul12
post Oct 14 2019, 04:08 PM
Post#7



Posts: 293
Joined: 7-June 14



Hi There,
Data in the table seem to be ok as I have other queries and reports that are based on the same table and these work just fine...
I have no idea as to what's causing this weird behaviour..

Dan
Go to the top of the page
 
GroverParkGeorge
post Oct 14 2019, 06:00 PM
Post#8


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


So you DO see different image files in that field for each record when you look at the table directly in datasheet view?

What I might try next is to recreate that query from scratch. It's not unheard for a query to get corrupted somehow.

If that doesn't work, we'll need to actually see some data and the SQL for that query to try to figure out the problem.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ciapul12
post Oct 15 2019, 05:17 AM
Post#9



Posts: 293
Joined: 7-June 14



Hey GroverParkGeorge
I have rebuilt my query but the issue hasn't gone away I'm afraid...

Here's my SQL code:
QUOTE
SELECT qryQuestionnaireResponses.QuestionnaireName, qryQuestionnaireResponses.Comment, qryQuestionnaireResponses.Category, qryQuestionnaireResponses.[Max Score], qryQuestionnaireResponses.[Standard Required], qryQuestionnaireResponses.Score, qryQuestionnaireResponses.Week, ResponseSessions.ResponseSessionID, ResponseSessions.[Audit Area], ResponseSessions.Shift, ResponseSessions.Site, qryQuestionnaireResponses.Score, qryQuestionnaireResponses.Target, ResponseSessions.ResponseSessionDate, Responses.ImageLink
FROM (qryQuestionnaireResponses INNER JOIN ResponseSessions ON qryQuestionnaireResponses.[ResponseSessionID] = ResponseSessions.[ResponseSessionID]) INNER JOIN Responses ON ResponseSessions.ResponseSessionID = Responses.ResponseSessionID
WHERE (((qryQuestionnaireResponses.Week)=[Forms]![frmReports]![Combo3]) AND ((Responses.ImageLink) Is Not Null));


Thank You
Dan
Go to the top of the page
 
GroverParkGeorge
post Oct 15 2019, 06:01 AM
Post#10


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Okay, thanks.

BUt that looks like it's based on still another query. So we're still chasing it down. What does the SQL for THAT query look like? Does IT return the correct paths for each unique image?

Amd. just to be sure, you did confirm that the Responses.ImageLink filed does contain a different path for each image, the image assigned to that different record.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ciapul12
post Oct 15 2019, 06:45 AM
Post#11



Posts: 293
Joined: 7-June 14



Hi,
Yes it does return correct paths..
Here's SQL:
QUOTE
SELECT Questionnaires.QuestionnaireID, Questionnaires.QuestionnaireName, Questions.Question, QuestionnaireQuestions.QuestionIndex, [Responses].[Answer] & (" - "+[Responses].[AnswerText]) AS ResponseAnswer, QuestionnaireAnswers.AnswerIndex, ResponseSessions.ResponseSessionID, ResponseSessions.ResponseSessionDate, Respondents.RespondentID, CanShrinkLines([Firstname] & " " & [Lastname],[AddressLine1],[AddressLine2],[AddressLine3]) AS NameAndAddress, Respondents.TelLandLine, Respondents.TelMobile, Responses.Comment, Questions.Category, Questions.[Max Score], Questions.[Standard Required], Responses.Score, Format([ResponseSessionDate],"ww") AS Week, 4.25 AS Target, ResponseSessions.[Audit Area], ResponseSessions.Shift, ResponseSessions.Site, Responses.ImageLink
FROM (Questionnaires INNER JOIN (Questions INNER JOIN (Answers INNER JOIN (QuestionnaireQuestions INNER JOIN QuestionnaireAnswers ON (QuestionnaireQuestions.QuestionID = QuestionnaireAnswers.QuestionID) AND (QuestionnaireQuestions.QuestionnaireID = QuestionnaireAnswers.QuestionnaireID)) ON (Answers.Answer = QuestionnaireAnswers.Answer) AND (Answers.QuestionID = QuestionnaireAnswers.QuestionID)) ON Questions.QuestionID = Answers.QuestionID) ON Questionnaires.QuestionnaireID = QuestionnaireQuestions.QuestionnaireID) INNER JOIN ((Respondents INNER JOIN ResponseSessions ON Respondents.RespondentID = ResponseSessions.RespondentID) INNER JOIN Responses ON ResponseSessions.ResponseSessionID = Responses.ResponseSessionID) ON (QuestionnaireAnswers.Answer = Responses.Answer) AND (QuestionnaireAnswers.QuestionID = Responses.QuestionID) AND (QuestionnaireAnswers.QuestionnaireID = Responses.QuestionnaireID);
Go to the top of the page
 
GroverParkGeorge
post Oct 15 2019, 07:08 AM
Post#12


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Okay, so in THIS query, which is then incorporated into the second one shown above, Response.ImageLink does return the correct paths (different for each record), but then, in that second query, you return, not this field, but the same field again from Response.ImageLink. I'm going to guess that maybe using this field, in qryQuestionnaireResponses, instead of the second instance as was done in your other query, might give you the correct, unique, image paths.

If that doesn't work, we'll need some sample data to work with.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 01:16 AM