Tempermental Access queries- HELP?

[mistercormell]

I have a real issue with my database and a large number (not all) of the queries that make up the database system. The information is presented as reports but it is a problem with the query not the reports.

It seems that I have a simple select query that does a few calculations and other stuff, but yet it only selects data that has been on the system for a while. E.g. if I were to enter a new record now that would be selected by the query (and checked it had been stored in the correct table) it would not appear in the results of the query.

It seems that the queries won't find data that has just recently been entered, only results that were entered before a specific date in the past.

I'm confused why it does this? Can anyone explain?

 

[neileg]

This is not a usual Access fault so it probably comes from the design. Can you share the design with us?

 

[mistercormell]

I have had alook into this problem and I can understand now why Accding this, and as you say it is a design problem. In the database I have 3 tables: Associates (staff, volunteers, funders or just general business contacts), Clients, Projects. Both associates and clients can be assigned to projects in the one to many relationship. (I have added two other tables tblasso_projects and tblclients_projects - to store these assignments)

The problem is, when I have created a query (I'm using just one as an example) that selects all the details about a specific associate (using criteria from a dialogue box) including details about the project/s it is assigned to. This works for exmaple if you search for a staff member as they (like volunteers) can be assigned to projects. However the problem comes when you search for an associate who has no associate_project assignments. In this case the whole report is blank, but what I want it to do is have all the information except the project assignments if they don't exist for that person.

If this isn't enough information I could send you the query design.

Thanks

Note: I have attached my database, the queries that do not work are: qryassociate, qryclients, qryproject, qryneeded due to the problem explained above.

E.g. if a project doesn't have any associates assigned to it, then no project information is displayed in qryproject and is not shown on qryneeded.

 

[neileg]

You are using inner joins (a type 1 join in Access terms). This will only return results if data exists in both tables. You need to look at left or right outer joins (type 2 0r 3 in Access terms). These return all the data from one table and any data from the other table if it exists.

By the way, it's a bad idea to uses spaces or punctuation in your object names so [General Contact?] should become GeneralContact.

 

[mistercormell]

How would you go about creating one of those in my project, plus how do you know when to use right and when to use left joins?

 

[neileg]

How would you go about creating one of those in my project, plus how do you know when to use right and when to use left joins?

Left and right joins are identical except the tables are reversed! So you always get all of one table and the matching data from the other. If you put the main table on the left, you use a left join and vice versa. Not as technical as it may seem.

You can change the join type, either in the Relationships window or in a query by right clicking the join line and selecting the appropriate option. Or you can edit the SQL, but I suggest you use the point and click for now.

 

[mistercormell]

THanks for your help so far, it is appreciated. I altered the SQL for qryneeded and solved that problem. However with the other three it won't let me just change inner joins to left joins (or even do it by clicking on the join lines in the query design view) because for those, three tables are involved and it doesn't seem to want to do a left join of a left join. When I try and alter it to LEFT it says invalid SQL join type.

 

[neileg]

That makes sense to me. Using qryassociate as an example, you want all associates and the projects that they are linked to. So you want all of the records from tblasso, then all of the records from tblasso_project that match, then all of the records from tblprojects that match. So the sql becomes:

SELECT tblasso.[Associate ID], tblasso.Title, tblasso.Name, tblasso.Surname, tblasso.[Address 1], tblasso.[Address 2], tblasso.[Town/City], tblasso.County, tblasso.[Post Code], tblasso.[Home Telephone], tblasso.[Work Telephone], tblasso.[Mobile Telephone], tblasso.Email, tblasso.[Date of Birth], tblasso.Gender, tblasso.[Staff?], tblasso.[Volunteer?], tblasso.[Funder?], tblasso.[General Contact?], tblasso.[CRB Number], tblasso.[CRB Issue Date], tblasso.Notes, tblProjects.[Project Name]
FROM tblProjects RIGHT JOIN (tblasso LEFT JOIN tblasso_project ON tblasso.[Associate ID] = tblasso_project.[Associate ID]) ON tblProjects.[Project ID] = tblasso_project.[Project ID]
WHERE (((tblasso.Name)=[Forms]![fdlgSelectasso].[txtdlgName]) AND ((tblasso.Surname)=[Forms]![fdlgSelectasso].[txtdlgSurname]));

 

[mistercormell]

Yes, that sounds right. However the only reason I need to take data from tblprojects is because I want the title of the project rather than just the ID that can be found in tblasso_projects.

I will give it a try later tonight