Multi Table Query Wont Return Results

[Rik_StHelens]

Hi,

Hope someone can help me with this. Ive created a database for managing adult learners doing qualifications. There is a Learner table with learner ID, personal details etc etc. Each learner is assigned an Assessor to work with them on their qualification. There is a seperate Assessor table with Assessor ID, forname and surname only. The assessor is assigned using a lookup in the Learner table. I have designed a query which should list all the learners assigned to one assessor, however it returns no results. Im currently using the Assessor field in the Learner table to query the assessors name, but i get no results.

Hope this is clear enough, and please ask any questions and i will check back regularly.

Thanks
Rik

 

[namliam]

Welcome to AWF

Then if I understand correctly, your data is not returning because there is no data that matches... On a normal Join you require there to be an equal value...

 

[Rik_StHelens]

Welcome to AWF

Then if I understand correctly, your data is not returning because there is no data that matches... On a normal Join you require there to be an equal value...

Thanks for the welcome!

The two tables have Assessor ID as the primary and foreign key respectively and that link is used to create a lookup in the Learner table to assign an Assessor for the qualification duration. Currently all the query does is ask the user to enter the full name of the assessor they wish to search for, but in the learner table to return all instances in which that assessor is assigned to a learner.

Hope this helps.

I can also add a file if this helps further.

Thanks again

 

[namliam]

But if the name of the assessor doesnt exist (check for spaces and such) then it wont return anything.
Also if the assessor doesnt have any learner(s) then it still dont return anything.

Make sure these criteria are met... Perhaps upload a sample of your DB that is having this problem.

 

[Rik_StHelens]

I have posted a blank DB sample to rapidshare which i hope will be of some help. I used to use access a lot but ive forgotten most of what i know unfortunately. Any advice you can offer would be great. The Main Menu will come up as default, but to get to the Assessor query exit the Form, and it is listed under Search by Assessor in queries.

Here is the link:

http://rapidshare.com/files/280344114/Internal_Verification_of_NVQs.mdb.html

Thanks a lot

 

[dbDamo]

Any chance you could just zip your db up and upload it here, rather than a file hosting site (can't access from work).

You can upload here by pressing the Go Advanced button, the pressing the Manage Attachments button under Additional Options

 

[namliam]

rather than a file hosting site (can't access from work).

Ditto

Strange though it may seem

 

[Rik_StHelens]

No problem.

should be attached now. was having problems zipping on works pcs

thanks

 

[namliam]

The nightmare of lookup columns.... You cannot search a lookup column, it is a FK as you described, it contains a number not a name.

You need to add your assessor table to the query and join it on FK-PK

Additional note:
- Search naming convention, find one use one...
this means NO spaces anywhere in code/naming
this means NO default names of controls (i.e. Command3), its a nightmare to maintian
this means NO 'simple' names in objects (i.e. Assessor), you could end up with 5 objects all called the same for another maintainance nightmare... Use tblAssessor instead. qry, frm, mcr, mdl for the rest.

Good luck on the project !

 

[Rik_StHelens]

Thanks for the suggestion.

I did set up a relationship between the query and the assessor lookup in the learner table in relationship view. Im guessing this was the wrong way to do it as it didnt work.

Forgive me as im probably asking a daft question but where/how should i make a link for the query to use data from both tables?

Do you mean to link assessor name in the assesor table with assessor name in the query?

Hope im not asking the obvious

I suppose the other thing to do would be to give each assessor a manual ID number (they probably have a number which identifies them nationally already) and get users to search by the manually assigned assessor ID because people should know this anyway

 

[namliam]

Refering to the title of this, " multi table query " where in this query do you see multi tables??? I dont...

You need this link INSIDE the query, you need to add each table where you want to fetch data from, have the link there (which is auto added from the relationship screen). Then add the columns from the tables and display your data.

A lookup column in a table is a No-No just because of this... It adds confusion as in the table you see "ben" or something while the column actually is a Foreign Key to your Assessor table containing only integers, not names.
Hence on this column you can only search using integers, not using "Ben" or something or other. To search for Ben you need the FirstName column of the assessor table...

If you understand what I mean.

 

[boblarson]

Rik:

Read this about lookups at table level.