query is not responding properly

[dbertanjoli]

Hello,
I am attaching my database as I gave up on my smartness. I have the following problem.

There is online form for patients. Few times a year they are obligated to fill in the form so we can follow up on their progress. Each patient has unique medicalrecordid and every time they do the form they need to provide this info. Each submission of the form has unique responseid.

So presently, when you open my main form you will see a list with patient last name and medicalrecordid but the key field is responseid. So if you select any name and then click on the report (on your right) you will see how that patient responded at certain time. But this is not exactly what we want. I purposely duplicated one record for testing. We want all patients to group by their medicalrecord (to be shown on this list only once) and when I select a patient we would like to see report with all his/her submissions (by responsid and then my medicalrecordid). Please open my database it will be clear what I am trying to do. I think the problem is in my query that I base my report on (called qryFullAnswer which is based on qryNumbers and qryNumbers is based on crosstab qery called qryCrossQuery - it has parameter value)

I would so greatly appreciate any help.
Deb

 

[RuralGuy]

Have you tried to compile your db yet? <ALT> F11, then Debug>Compile. I would start by fixing all of the compile errors first.

 

[dbertanjoli]

Hi thank you for your reply. I did lot of changes and know it is working but the only problem is that if you select first name on the list (has two responses) and then click on the report it shows two reports for this person but second report keeps accumulating from first one (my query looks correct, just report keeps accumulating). Can you please take a look.
deb

 

[dbertanjoli]

Sorry, my attachment didn't go through.
Here it is.
Deb

 

[RuralGuy]

Hi Deb,
Does your db compile without errors before you post it? Mine does not.

 

[dbertanjoli]

I just noticed, if I use qryMain2 for my listbox on main form calculation is done properly in my report but then in my list box i can see duplicates (actually then are not duplicates but just same patient responsed more then once)

 

[dbertanjoli]

I just compiled it with no problems.
Deb

 

[dbertanjoli]

and I want all responses by one patient to show in one report (by responseid)

I am so sorry for cutting off my messages..

 

[dbertanjoli]

So basically I have to direct all responseid to one unique medicalrecordid in my list box on main form.

 

[RuralGuy]

I'm afraid I'm still trying to understand what you want from your report.

 

[dbertanjoli]

Well, let say I have unique medicalrecordid and I am obligated to do/submit survey on line a few times a year. So every submission has unique response id. I would like my listbox, on my main form, to list all patients who did survey (but not repeating the medicalrecordid) and by selecting one patient and clicking on the report button to see all responses he/she did so we can compare and see if there is any progress.
So, basically to group reports by medicalrecordid (but each report has unique responseid)

 

[RuralGuy]

Your 1st patient completed two surveys so the report has two pages. Did you only want the *last* survey completed?

 

[dbertanjoli]

I want to see both surveys in my report. I purposly duplicated it to test if I will be able to see several reports in one for one patient with unique medicalrecord id.
so basically
patient has medical record id 112233 and from this patient we can have several responses (survey done in different time) let say responseid 31 , 32... so I want to see all of them in one report.

Deb

 

[RuralGuy]

I'm sorry but my pea brain is still struggling. How does that differ from what is happening currently with the two page report? Are you talking about combining the numbers somehow?

 

[dbertanjoli]

Okay, because I forced unique values in my listbox, mainform, you can see only unuque medicalrecordid which is what I want. but the problem is if you select record 112233 and click on report you will see two reports, what we want, but the problem is second report is accumulating values from the first report, I am not sure why is doing this as query reads it correctly.

Deb

 

[RuralGuy]

Well in the rptMainSource, the Text282 TextBox has the RunningSum property set to OverAll for example.

 

[dbertanjoli]

BINGO! that was it. I CAN'T BELEIVE. Thank you so much. Rating 10/10
Deb

 

[RuralGuy]

Glad I could help Deb.