Display correct info on form from 3 tables

[gmazza via AccessMonster.com]

Hey there,
I am trying to display the results I want on a form from a few tables based
on the ClinicalTrialId.
Here is my Record Source for my form:
SELECT PatientData.PatientId, PatientData.ClinicalTrialId, CriteriaOption.
CriteriaOptionValue, Criteria.CriteriaValue FROM Criteria INNER JOIN
(CriteriaOption INNER JOIN PatientData ON CriteriaOption.CriteriaOptionId =
PatientData.CriteriaOptionId) ON Criteria.CriteriaId = PatientData.
CriteriaId;

Right now ALL records from the PatientData table are showing up.
I only want records with ClinicalTrialId of 2 showing up.
I have a textbox on my form that is called ClinicalTrialId, and the value is
2.
When I add this to the end of my Record Source query,
Where ClinicalTrialId = Forms!PatientData!ClinicalTrialId
I get the error 'The field ClinicalTrialId could refer to more than 1 table
listed in the FROM clause of your SQL statement.

So when I add the table name to the ClinicalTrialId in the WHER clause, like:
Where PatientData.ClinicalTrialId = Forms!PatientData!ClinicalTrialId
no data shows up at all on the form. Same for when I add the other 2 table
names in front of ClinicalTrialId, nothing shows up.
If I put Where ClinicalTrialId = 2, then it works, I'm just not understanding
why it can't read my textbox?
Any thoughts?

 

[KARL DEWEY]

I would use left joins instead of inner.
Try this --
Where [YourTableName].[ClinicalTrialId] =
Val([Forms]![PatientData]![ClinicalTrialId])

 

[gmazza via AccessMonster.com]

Thanks for your reply Karl, I don't really know how to use left joins, I
replaced INNER with LEFT and that didn't work.
I tried using your Where clause like so:
Where [PatientData].[ClinicalTrialId] =
Val([Forms]![PatientData]![ClinicalTrialId])

and received the error 'This expression is typed incorrectly, or is too
complex to be evaluated. For example, a numeric expressionmay contain too
many complicated elements. Try simplify the expression by assigning parts of
the expression to variables.

I would use left joins instead of inner.
Try this --
Where [YourTableName].[ClinicalTrialId] =
Val([Forms]![PatientData]![ClinicalTrialId])

Hey there,
I am trying to display the results I want on a form from a few tables based

[quoted text clipped - 22 lines]

why it can't read my textbox?
Any thoughts?

 

[KARL DEWEY]

I assumed that [PatientData].[ClinicalTrialId] was a number field and
therefore needed the text of [Forms]![PatientData]![ClinicalTrialId]
converted.

A left join is used where there may not be matching records n the right
table. This allows the left to be shown anyway.

If you post sample data I might be able to understand your query better.

Thanks for your reply Karl, I don't really know how to use left joins, I
replaced INNER with LEFT and that didn't work.
I tried using your Where clause like so:
Where [PatientData].[ClinicalTrialId] =
Val([Forms]![PatientData]![ClinicalTrialId])

and received the error 'This expression is typed incorrectly, or is too
complex to be evaluated. For example, a numeric expressionmay contain too
many complicated elements. Try simplify the expression by assigning parts of
the expression to variables.

I would use left joins instead of inner.
Try this --
Where [YourTableName].[ClinicalTrialId] =
Val([Forms]![PatientData]![ClinicalTrialId])

Hey there,
I am trying to display the results I want on a form from a few tables based

[quoted text clipped - 22 lines]

why it can't read my textbox?
Any thoughts?

 

[gmazza via AccessMonster.com]

It is a number field. ClinicalTrialId in the PatientData table is a number.
Sample data:
Criteria Table:
CriteriaId: 8 CriteriaValue: Cough-Day 1 ClinicalTrialId: 2
CriteriaId: 19 CriteriaValue: Cough-Day 1 ClinicalTrialId: 1

CriteriaOption Table:
CriteriaOptionId: 10 CriteriaId: 8 CriteriaOptionValue: yes
ClinicalTrialId: 2
CriteriaOptionId: 11 CriteriaId: 8 CriteriaOptionValue: no
ClinicalTrialId: 2
CriteriaOptionId: 46 CriteriaId: 8 CriteriaOptionValue: yes
ClinicalTrialId: 1
CriteriaOptionId: 47 CriteriaId: 8 CriteriaOptionValue: no
ClinicalTrialId: 1

PatientData Table:
PatientId: 6 CriteriaId: 8 CriteriaOptionId: 10 ClinicalTrialId:
2
PatientId: 18 CriteriaId: 19 CriteriaOptionId: 46 ClinicalTrialId:
1

When the PatientData form opens, it shows ALL records in the PatientData form,
for ALL Clinical Trials. I just want it to show for whatever Clinical Trial I
am on.
Hope that helps! Thanks for your help!

I assumed that [PatientData].[ClinicalTrialId] was a number field and
therefore needed the text of [Forms]![PatientData]![ClinicalTrialId]
converted.

A left join is used where there may not be matching records n the right
table. This allows the left to be shown anyway.

If you post sample data I might be able to understand your query better.

Thanks for your reply Karl, I don't really know how to use left joins, I
replaced INNER with LEFT and that didn't work.

[quoted text clipped - 17 lines]

 

[KARL DEWEY]

This worked for me --
SELECT PatientData.PatientId, PatientData.ClinicalTrialId,
CriteriaOption.CriteriaOptionValue, Criteria.CriteriaValue
FROM Criteria INNER JOIN (CriteriaOption INNER JOIN PatientData ON
CriteriaOption.CriteriaOptionId = PatientData.CriteriaOptionId) ON
Criteria.CriteriaId = PatientData.CriteriaId
WHERE
(((PatientData.ClinicalTrialId)=[Forms]![PatientData]![ClinicalTrialId]));

It is a number field. ClinicalTrialId in the PatientData table is a number.
Sample data:
Criteria Table:
CriteriaId: 8 CriteriaValue: Cough-Day 1 ClinicalTrialId: 2
CriteriaId: 19 CriteriaValue: Cough-Day 1 ClinicalTrialId: 1

CriteriaOption Table:
CriteriaOptionId: 10 CriteriaId: 8 CriteriaOptionValue: yes
ClinicalTrialId: 2
CriteriaOptionId: 11 CriteriaId: 8 CriteriaOptionValue: no
ClinicalTrialId: 2
CriteriaOptionId: 46 CriteriaId: 8 CriteriaOptionValue: yes
ClinicalTrialId: 1
CriteriaOptionId: 47 CriteriaId: 8 CriteriaOptionValue: no
ClinicalTrialId: 1

PatientData Table:
PatientId: 6 CriteriaId: 8 CriteriaOptionId: 10 ClinicalTrialId:
2
PatientId: 18 CriteriaId: 19 CriteriaOptionId: 46 ClinicalTrialId:
1

When the PatientData form opens, it shows ALL records in the PatientData form,
for ALL Clinical Trials. I just want it to show for whatever Clinical Trial I
am on.
Hope that helps! Thanks for your help!

I assumed that [PatientData].[ClinicalTrialId] was a number field and
therefore needed the text of [Forms]![PatientData]![ClinicalTrialId]
converted.

A left join is used where there may not be matching records n the right
table. This allows the left to be shown anyway.

If you post sample data I might be able to understand your query better.

Thanks for your reply Karl, I don't really know how to use left joins, I
replaced INNER with LEFT and that didn't work.

[quoted text clipped - 17 lines]

why it can't read my textbox?
Any thoughts?

 

[gmazza via AccessMonster.com]

I don't know what to say, I am getting a blank form when I have that in my
Record Source for the form. Weird.

This worked for me --
SELECT PatientData.PatientId, PatientData.ClinicalTrialId,
CriteriaOption.CriteriaOptionValue, Criteria.CriteriaValue
FROM Criteria INNER JOIN (CriteriaOption INNER JOIN PatientData ON
CriteriaOption.CriteriaOptionId = PatientData.CriteriaOptionId) ON
Criteria.CriteriaId = PatientData.CriteriaId
WHERE
(((PatientData.ClinicalTrialId)=[Forms]![PatientData]![ClinicalTrialId]));

It is a number field. ClinicalTrialId in the PatientData table is a number.
Sample data:

[quoted text clipped - 37 lines]

 

[gmazza via AccessMonster.com]

and what doesn't make sense is when I pull up the query view for the Record
Source, put in 1 under the criteria for ClinicalTrialId, then run the query,
it shows up correctly. I substitute 1 for
[Forms]![PatientData]![ClinicalTrialId] and it nothing shows.
Maybe my Control Source for my ClinicalTrialId on my form is no good, yet I
put in 2 as my Control Source and the form still shows up blank. I am baffled.
...

I don't know what to say, I am getting a blank form when I have that in my
Record Source for the form. Weird.

This worked for me --
SELECT PatientData.PatientId, PatientData.ClinicalTrialId,

[quoted text clipped - 10 lines]

 

[KARL DEWEY]

Try saving the SQL I posted as a named query and use the name as Record
Source for the form.

and what doesn't make sense is when I pull up the query view for the Record
Source, put in 1 under the criteria for ClinicalTrialId, then run the query,
it shows up correctly. I substitute 1 for
[Forms]![PatientData]![ClinicalTrialId] and it nothing shows.
Maybe my Control Source for my ClinicalTrialId on my form is no good, yet I
put in 2 as my Control Source and the form still shows up blank. I am baffled.
...

I don't know what to say, I am getting a blank form when I have that in my
Record Source for the form. Weird.

This worked for me --
SELECT PatientData.PatientId, PatientData.ClinicalTrialId,

[quoted text clipped - 10 lines]

why it can't read my textbox?
Any thoughts?

 

[KARL DEWEY]

By the way you have to refresh the query after entering data in the text box

and what doesn't make sense is when I pull up the query view for the Record
Source, put in 1 under the criteria for ClinicalTrialId, then run the query,
it shows up correctly. I substitute 1 for
[Forms]![PatientData]![ClinicalTrialId] and it nothing shows.
Maybe my Control Source for my ClinicalTrialId on my form is no good, yet I
put in 2 as my Control Source and the form still shows up blank. I am baffled.
...

I don't know what to say, I am getting a blank form when I have that in my
Record Source for the form. Weird.

This worked for me --
SELECT PatientData.PatientId, PatientData.ClinicalTrialId,

[quoted text clipped - 10 lines]

why it can't read my textbox?
Any thoughts?

 

[gmazza via AccessMonster.com]

Tried it, same thing.
When I goto query design, under my criteria for ClinicalTrialId is [Forms]!
[PatientData]![ClinicalTrialId]
When I run the query, shouldn't it ask for a value for [Forms]![PatientData]!
[ClinicalTrialId]
because when I run the query, no data shows up at all.

Try saving the SQL I posted as a named query and use the name as Record
Source for the form.

and what doesn't make sense is when I pull up the query view for the Record
Source, put in 1 under the criteria for ClinicalTrialId, then run the query,

[quoted text clipped - 12 lines]

 

[gmazza via AccessMonster.com]

I did not know that, thanks!
I can always email you the db, its super tiny, nothing to it more than what
we talked about, maybe you can see better what I am doing wrong. up to you,
let me know, don't worry, I won't keep your email and heckle you every time I
have a problem. I post things here if I have any issues

By the way you have to refresh the query after entering data in the text box
on the form.

and what doesn't make sense is when I pull up the query view for the Record
Source, put in 1 under the criteria for ClinicalTrialId, then run the query,

[quoted text clipped - 12 lines]

 

[KARL DEWEY]

It should ask for input unless you have the form open. If the form is open
and nothing in the text box it will not prompt for input and you will not get
any records as the text box contains a Null and that does not match your
criteria.

Open the query in design view and scroll to the right to an empty column and
paste this --
My_Check: [Forms]![PatientData]![ClinicalTrialId]

Open the form and enter 2 in the text box.

Run the query.

My_Check should display '2'. If not then double check all spelling of the
form and text box name every where it is used.

Tried it, same thing.
When I goto query design, under my criteria for ClinicalTrialId is [Forms]!
[PatientData]![ClinicalTrialId]
When I run the query, shouldn't it ask for a value for [Forms]![PatientData]!
[ClinicalTrialId]
because when I run the query, no data shows up at all.

Try saving the SQL I posted as a named query and use the name as Record
Source for the form.

and what doesn't make sense is when I pull up the query view for the Record
Source, put in 1 under the criteria for ClinicalTrialId, then run the query,

[quoted text clipped - 12 lines]

why it can't read my textbox?
Any thoughts?

 

[gmazza via AccessMonster.com]

Ya it doesn't ask for input and the form is not open.

I did your my check and it worked.
I pasted your stuff in a column in the query.
I entered 2 in the unbound textbox on the form
I ran the query and all the correct results showed up for Clinical Trial 2,
and and my check said 2.
I did it for Clinical Trial 1 and again the reuslts were correct for Clinical
Trial 1 and my check said 1.

It should ask for input unless you have the form open. If the form is open
and nothing in the text box it will not prompt for input and you will not get
any records as the text box contains a Null and that does not match your
criteria.

Open the query in design view and scroll to the right to an empty column and
paste this --
My_Check: [Forms]![PatientData]![ClinicalTrialId]

Open the form and enter 2 in the text box.

Run the query.

My_Check should display '2'. If not then double check all spelling of the
form and text box name every where it is used.

Tried it, same thing.
When I goto query design, under my criteria for ClinicalTrialId is [Forms]!

[quoted text clipped - 11 lines]

 

[gmazza via AccessMonster.com]

Ya it doesn't ask for input and the form is not open.

I did your my check and it worked.
I pasted your stuff in a column in the query.
I entered 2 in the unbound textbox on the form
I ran the query and all the correct results showed up for Clinical Trial 2,
and and my check said 2.
I did it for Clinical Trial 1 and again the reuslts were correct for Clinical
Trial 1 and my check said 1.

It should ask for input unless you have the form open. If the form is open
and nothing in the text box it will not prompt for input and you will not get
any records as the text box contains a Null and that does not match your
criteria.

Open the query in design view and scroll to the right to an empty column and
paste this --
My_Check: [Forms]![PatientData]![ClinicalTrialId]

Open the form and enter 2 in the text box.

Run the query.

My_Check should display '2'. If not then double check all spelling of the
form and text box name every where it is used.

Tried it, same thing.
When I goto query design, under my criteria for ClinicalTrialId is [Forms]!

[quoted text clipped - 11 lines]