Query not picking up records

[Emma35]

Hi all,
I need a little help with something which is the final snag in my project.
I've got a multi-table query called Qry_Print. It is based on several tables and for the first two records i entered everything was fine. Then when i entered a third record (via a form), the record appears in the table but not in the query ?. Someone has suggested to me that the problem might lie in the joins specified in the query but i can't seem to change them.
One thing i did notice is that if i fill in all the fields, the record will show up in the query but if i leave a few blank the it doesn't ? I will need to leave some fields blank and then update them a few days later so the query as it stands is of no use to me. here's the SQL for the query.
I'd be grateful for any help guys.

SELECT Tbl_Master.AssessmentID, Tbl_Master.AssessmentName, Tbl_Master.AssessmentDate, Tbl_Master.LocationBlock, Tbl_Master.DepartmentPlant, Tbl_Master.AssessorName, Tbl_QuestionLU.QuestionText, Tbl_Q1LU.Q1Text, Tbl_Q2LU.Q2Text, Tbl_Q3LU.Q3Text, Tbl_Q4LU.Q4Text, Tbl_Q5LU.Q5Text, Tbl_Master.Notes, Tbl_ExistingRRN.ExProbability, Tbl_ExistingRRN.ExSeverity, Tbl_ExistingRRN.ExFrequency, Tbl_ExistingRRN.ExPeopleAffected, Tbl_SeverityLU.SevDescription, Tbl_FrequencyLU.FreqDescription, Tbl_ProbabilityLU.ProbDescription, PeopleAffectedLU.PeopleDescription, Tbl_ResidualRRN.ResProbability, Tbl_ResidualRRN.ResSeverity, Tbl_ResidualRRN.ResFrequency, Tbl_ResidualRRN.ResPeopleAffected, Tbl_ResProbabilityLU.ResProbDescription, Tbl_ResSeverityLU.ResSevDescription, Tbl_ResFrequencyLU.ResFreqDescription, Tbl_ResPeopleAffectedLU.ResPeopleDescription, Tbl_Master.AssessmentType
FROM Tbl_SeverityLU INNER JOIN (Tbl_QuestionLU INNER JOIN (Tbl_Q5LU INNER JOIN (Tbl_Q4LU INNER JOIN (Tbl_Q3LU INNER JOIN (Tbl_Q2LU INNER JOIN (Tbl_Q1LU INNER JOIN (Tbl_ProbabilityLU INNER JOIN ((Tbl_Master INNER JOIN (Tbl_FrequencyLU INNER JOIN (PeopleAffectedLU INNER JOIN Tbl_ExistingRRN ON PeopleAffectedLU.PeopleAffectedID=Tbl_ExistingRRN.ExPeopleAffected) ON Tbl_FrequencyLU.FrequencyID=Tbl_ExistingRRN.ExFrequency) ON Tbl_Master.AssessmentID=Tbl_ExistingRRN.AssessmentID) INNER JOIN ((((Tbl_ResidualRRN INNER JOIN Tbl_ResProbabilityLU ON Tbl_ResidualRRN.ResProbability=Tbl_ResProbabilityLU.ProbabilityID) INNER JOIN Tbl_ResSeverityLU ON Tbl_ResidualRRN.ResSeverity=Tbl_ResSeverityLU.SeverityID) INNER JOIN Tbl_ResFrequencyLU ON Tbl_ResidualRRN.ResFrequency=Tbl_ResFrequencyLU.FrequencyID) INNER JOIN Tbl_ResPeopleAffectedLU ON Tbl_ResidualRRN.ResPeopleAffected=Tbl_ResPeopleAffectedLU.PeopleAffectedID) ON Tbl_Master.AssessmentID=Tbl_ResidualRRN.AssessmentID) ON Tbl_ProbabilityLU.ProbabilityID=Tbl_ExistingRRN.ExProbability) ON Tbl_Q1LU.Q1ID=Tbl_Master.Q1) ON Tbl_Q2LU.Q2ID=Tbl_Master.Q2) ON Tbl_Q3LU.Q3ID=Tbl_Master.Q3) ON Tbl_Q4LU.Q4ID=Tbl_Master.Q4) ON Tbl_Q5LU.Q5ID=Tbl_Master.Q5) ON Tbl_QuestionLU.QuestionID=Tbl_Master.Question) ON Tbl_SeverityLU.SeverityID=Tbl_ExistingRRN.ExSeverity;

 

[MStef]

You can to change a RELATIONSHIPS in the query,
independantly of that in the Relationships.
If you want, send a short example of your mdb (access 2000 or 2002-2003),
and tell what you want to do.

 

[Emma35]

Hi MStef....my database is Access 2007. It's an .accdb file.
Is it the INNER join that is the problem ?
How can i change it ?

 

[MStef]

I don't know if I not see it.

 

[Emma35]

Ok i'm attaching a copy...thanks

 

[MStef]

It is "Access 2007", I can't read it.

 

[Emma35]

Sorry i did mention that before i uploaded it

Is there a way to change the joins in the query ?

 

[recyan]

Till some one comes along,
1) Can you not save the db in .mdb format & then upload it.

2) The inner join could perhaps be the cause ( I think you have already thought about that).
Assume, you must have tried changing the join & got a message on trying to run the query, ambiguos joins.
a) The message will also give a hint "sub-queries". That perhaps could be one way to go.
b) Having default values for your fields in the table could perhaps be another way

3) Not an expert, but was generally wondering whether we have more than necessary tables :
For eg :
a) Reason for different tables?
Tbl_Q1LU
Tbl_Q2LU
Tbl_Q3LU
Tbl_Q4LU
Tbl_Q5LU
Tbl_QuestionLU

b) Reason for separate tables ?
Tbl_ExistingRRN
Tbl_ResidualRRN
apart from
Tbl_Master


Thanks

 

[Emma35]

Thanks recyan

Sorry i didn't even think of saving it as an mdb file

When i tried to change the joins to a LEFT OUTER JOIN i got 'Join type not supported' message

The LU tables are lookup tables because Q1 - Q5 are radio button selections and i'd like text to appear on the report rather than 1 or 2

The reason for the extra tables was my attempt to normalise the database and not have one huge table with everything in it. I'm the futhest thing from an expert myself.

I think default values might be woth looking into if i can't sort the query out. Can i change an Option Group to have a default value even if i didn't specify one when setting it up ?

Thanks again for your help

 

[recyan]

The LU tables are lookup tables because Q1 - Q5 are radio button selections and i'd like text to appear on the report rather than 1 or 2

If that is the reason, then those tables are not required. You can display 1 or 2 as Yes or No, using for eg. IIf.
IIf(Q1=1,"Yes", "No")

The reason for the extra tables was my attempt to normalise the database and not have one huge table with everything in it.

I think, the tblMaster is not big. You can combine the one-to-one relationship tables in to one.

I think default values might be woth looking into if i can't sort the query out.

I agree.

Can i change an Option Group to have a default value even if i didn't specify one when setting it up ?

I think, that can be done.

At this stage, I am unable to help any further, bcos, somehow, my mind is fixed on the design aspect & am unable to think queries, forms, reports, etc.

Also wonder, whether, we should have a table for questions, as there could be a possibility of questions changing in future.

Attached, one of my thoughts, for the current scenario.

Hope, i'm not confusing you more, rather than helping.

Thanks

 

[Emma35]

If that is the reason, then those tables are not required. You can display 1 or 2 as Yes or No, using for eg. IIf.
IIf(Q1=1,"Yes", "No")

So that code would go where ?.....into the Control Source of the text box on the report ?

I think, the tblMaster is not big. You can combine the one-to-one relationship tables in to one.

Sounds like a good idea

I think, that can be done.

At this stage, I am unable to help any further, bcos, somehow, my mind is fixed on the design aspect & am unable to think queries, forms, reports, etc.

Also wonder, whether, we should have a table for questions, as there could be a possibility of questions changing in future.

Attached, one of my thoughts, for the current scenario.

Hope, i'm not confusing you more, rather than helping.

Thanks

Not at all you've been a big help and thanks for your time....i appreciate it. I'm going to try the default values first and if that doesn't work, i'll change the table structure around and see how that goes. Looks like changing the query is a non starter.

Thanks again,
Emma

 

[recyan]

So that code would go where ?.....into the Control Source of the text box on the report ?

Should be able to do that. Can also be done in the query itself, however, I think, that is not advisable (kind of overloading the query when not needed).

i'll change the table structure around and see how that goes.

Hope, u r doing it in a copy of the db.

Do not know if you have realized it, in the diagram that I have attached,
TblProbabilityLU_1,
TblSeverityLU_1,
TblFrequencyLU_1,
TblPeopleAffectedLU_1
are not duplicate tables.
The same original tables have been added twice in the relationship window & they automatically get an alias of
_____ _1

Looks like changing the query is a non starter.

Not sure, but even I am getting the same feeling.

Thanks

 

[Emma35]

Hi again guys.......i tried giving all the fields default values but the query still isn't picking up the records. I then changed the join types in the query but now i'm getting a message saying that the query can't run because of 'Ambiguous outer join types'. I've done a search and the Microsoft website says i need to create another query to get round this but i'm not sure how t go about it.
Has anyone ever run into this problem before ?
Any help would be greatly appreciated

 

[Emma35]

Ok i've managed to get rid of a lot of tables that i really didn't need in this query (thanks recyan). Some of the controls on my form are radio buttons and when i generate a report i'm getting '1 or 2' instead of 'Yes or No'.
I'm putting the following code in the control source of the text box on the report but it's giving me a #Type! error. Any ideas what i'm doing wrong.

Code
=IIf([Question]=1,"Yes","No")

Thanks

 

[recyan]

Not much in to forms, reports,
all the same,
1) try changing the name of text box on the report, from Question to QuestionA. Keep the control source same as what you have shown.
i.e. =IIf([Question]=1,"Yes","No")
2) Alternatively, explore "Yes/No" field type.

Thanks

 

[Emma35]

No didn't work...i'm still getting the same error

 

[Emma35]

Ok i think i'm nearly there. I added a new text box to the report, put the code =IIf([Question]=1,"Yes","No") into it's control source, and set the visibility of the other control to No and it works.
Is it difficult to expand that code to cover an option group ?. Say i had a group with five possible choices Yes,No,Ok,Maybe and N/A.

If i can make this work i'm finished with this project :banghead:

Thanks

 

[Emma35]

No problem...worked it out myself. Thanks