Query is blank when other users update the source data (1 Viewer)

[Erin M 2021]

I posted similarly a while back and haven't been able to figure this out so I'm rewording in hopes to clarify.

Functionality is perfect when I update the source data .MDB file that connects to my .accdb reporting.

When any other user updates the .MDB, they can successfully view the new data in the .MDB file and in the .accdb table. Problem is occurring when they try to open the query/report using the table. It comes up blank.

This is occurring exclusively with one report. They can update .MDB files that are associated with other .accdb files just fine.

Any thoughts?

 

[CJ_London]

blank means what? plenty of records But no visible values? Or no records?

 

[Erin M 2021]

blank means what? plenty of records But no visible values? Or no records?

Column headers from the .MDB are there but no data

 

[CJ_London]

check the query/report properties - perhaps there is a filter in place that returns nothing. If so., remove it

 

[Erin M 2021]

check the query/report properties - perhaps there is a filter in place that returns nothing. If so., remove it

No Filters. I also don't see how it would work fine for me if there were any.

 

[CJ_London]

you are not showing the query properties, click in the top section, not on a table.

And you need to check on your users version - just because you don't have a filter on your machine, doesn't mean they haven't. For example you applied a filter on your machine, then distributed the FE to your users and then removed the filter.

 

[Erin M 2021]

No Filters. I also don't see how it would work fine for me if there were any.
View attachment 106966

you are not showing the query properties, click in the top section, not on a table.

And you need to check on your users version - just because you don't have a filter on your machine, doesn't mean they haven't. For example you applied a filter on your machine, then distributed the FE to your users and then removed the filter.

 

[arnelgp]

maybe you can switch from Inner Join to Outer Join?
right click on the Line Joining between [Solicitor Meeting Union] and [Solicitor Goals]
on Join Property, select, Include ALL records from [Solicitor Meeting Union] and only those records from [Solicitor Goals] ...

 

[Pat Hartman]

1. Each table should have a primary key
2. Unless you actually want to create a Cartesian Product, you need join lines to DOD Meeting Goal

 

[CJ_London]

To finish crossing the T's now show the recordsource property to the report - you say that works

 

[Erin M 2021]

To finish crossing the T's now show the recordsource property to the report - you say that works

This ties back to the screenshot above.

 

[Erin M 2021]

maybe you can switch from Inner Join to Outer Join?
right click on the Line Joining between [Solicitor Meeting Union] and [Solicitor Goals]
on Join Property, select, Include ALL records from [Solicitor Meeting Union] and only those records from [Solicitor Goals] ...

 

[Erin M 2021]

1. Each table should have a primary key
2. Unless you actually want to create a Cartesian Product, you need join lines to DOD Meeting Goal

I don't create the tables. These are exported from another database. Please advise.

 

[The_Doc_Man]

You can create a temporary relationship in the area above the query design grid so that Access knows what is related to what when you build that query. Then it doesn't matter what - if any - relationships are declared in the tables. To make the query work, you need to JOIN to the DOD Meeting Goal as Pat suggested.

 

[Erin M 2021]

You can create a temporary relationship in the area above the query design grid so that Access knows what is related to what when you build that query. Then it doesn't matter what - if any - relationships are declared in the tables. To make the query work, you need to JOIN to the DOD Meeting Goal as Pat suggested.

Please explain where the temporary relationship would go.

Additionally, this query works fine for me when I update the table/.MDB. It does not work for anyone else if they update the table/.MDB. This issue is why I've posted.

 

[Pat Hartman]

Relationships are permanent and are defined on the Relationship tab. You don't need a formal relationship to create a join in a table. A join is a relationship for the purpose of the query but joins and relationships are different and used for different purposes and so we shouldn't use the same word to describe them. The Relationship diagram and the GUI view of a query have some of the same characteristics. If these are linked tables, you can't do anything about creating a permanent relationship or primary keys unless you import them and importing them is a bad idea..

However, you do need to tell us how the third table is "related" to the others so you can draw a proper join line. We can't tell based on the column names. It may be that you need a union query if the DOD doesn't have a field that can be used to join with one of the other tables. When you have a table in the query that has no join line to another table, it acts as a multiplier and will create duplicates in the result set.

 

[Erin M 2021]

Relationships are permanent and are defined on the Relationship tab. You don't need a formal relationship to create a join in a table. A join is a relationship for the purpose of the query but joins and relationships are different and used for different purposes and so we shouldn't use the same word to describe them. The Relationship diagram and the GUI view of a query have some of the same characteristics. If these are linked tables, you can't do anything about creating a permanent relationship or primary keys unless you import them and importing them is a bad idea..

However, you do need to tell us how the third table is "related" to the others so you can draw a proper join line. We can't tell based on the column names. It may be that you need a union query if the DOD doesn't have a field that can be used to join with one of the other tables. When you have a table in the query that has no join line to another table, it acts as a multiplier and will create duplicates in the result set.

You're on target. It does not relate. It is being used in the final report to calculate percentage to team goal for each DOD. How would you suggest I write the union? Below is what I currently have (don't hate me for my naming conventions):

SELECT [Solicitor Meeting Union].Act_Category, [Solicitor Meeting Union].Act_Action_Date AS [Date], Format([Solicitor Goals]![CnGoal_1_Goal],"Fixed") AS Goal, [Solicitor Goals].Solicitor, CInt([DOD Meeting Goal]![CnGoal_1_Goal]) AS DODTeamMeetingGoal, [Solicitor Meeting Union].Act_Completed, [Solicitor Goals].CnGoal_1_Category, [Solicitor Meeting Union].*
FROM [DOD Meeting Goal], [Solicitor Meeting Union] INNER JOIN [Solicitor Goals] ON [Solicitor Meeting Union].Solicitor = [Solicitor Goals].Solicitor
WHERE ((([Solicitor Meeting Union].Act_Completed)=True) AND (([Solicitor Goals].CnGoal_1_Category)="Monthly Meeting Goal"));

Though, I still don't understand why this works for me as is and not any other user.

 

[Pat Hartman]

You are duplicating columns. You are selecting Act_Category and you also have [solicitor Meeting Union].* to select ALL columns.
If there is only ONE column in the DOD table, then there will be no duplication so you can leave it without a join. It is not logically a union relationship as you have described it.

Joins are NOT arbitrary. They reflect real world relationships between sets of data. Change the first join back to inner if there should always be a matching Solicitor in both tables. You can probably make the error caused by the left join go away by moving the DOD table to the end of the Where clause instead of the beginning.

When Access works on one PC but not on others, the problem is either security or a missing reference. Have you looked at those things?

 

[Erin M 2021]

You are duplicating columns. You are selecting Act_Category and you also have [solicitor Meeting Union].* to select ALL columns.
If there is only ONE column in the DOD table, then there will be no duplication so you can leave it without a join. It is not logically a union relationship as you have described it.

Joins are NOT arbitrary. They reflect real world relationships between sets of data. Change the first join back to inner if there should always be a matching Solicitor in both tables. You can probably make the error caused by the left join go away by moving the DOD table to the end of the Where clause instead of the beginning.

When Access works on one PC but not on others, the problem is either security or a missing reference. Have you looked at those things?

I work on any PC I try. The other user does not work on any. What do you think this indicates regarding permissions? I'll add that this specific .accdb is the only issue. We have other .accdb files where the user is able to update as expected.

 

[Pat Hartman]

We have other .accdb files where the user is able to update as expected.

Doesn't mean it isn't security or a missing reference. Did you look for those? To look for a missing reference, open any code module on the failing PC. Go to Tools/References. Is any reference marked as MISSING?