Getting totals across a row on datasheet view 1

[JDRoss]

I am wondering if somebody could tell me why this won't work.

I have three queries with a field for totals in each table: in the queries below:

qryweekly
qryspecial
qrycollections

In a datasheet view form I am trying to show individual totals for each row (record), under headings corresponding to the tables above. However, I would also like to be able to show the sum total for each individual record from all three queries.

In the query design view I use a calculated field where I sum the totals from all three queries.

AllTotals: [sumweekly]+[Sumspecial]+[Sumcollections]

But I am not getting what I should. It will only total them if there are values from all three queries. I have tried using Val(NZ,0) but no good.

I hope that you can follow all this

Regards

John

 

[LittleSmudge]

Try

AllTotals.ControlSource = Nz([sumweekly],0)+Nz([Sumspecial],0)+Nz([Sumcollections],0)

instead



'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[JDRoss]

LittleSmudge

That just worked perfect. I had been using the NZ in a 'global' way.

On a separate issue, I wonder could you help me out on a security issue.

I have code which creates a query at run time depending on which table the user is searching. This query is created dynamically.

The problem is that whatever permissions other than the default Admins Group permissions I set for the user, it will not give them access to creating the query. Naturally, I don't want users to have Admins Group permissions.

What is curious is that Access tells them that the user does not have permissions for 'MsysTable' object. Now I have search through the hidden and system tables and can't find this one.

Any ideas.

Regards

Johm

 

[LittleSmudge]

I think you need to give one of the groups that your user is a member of the "Administer Database" privledge.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[JDRoss]

Thanks LittleSmudge.

I don't know if I'm familiar with "Administer Database" privledge?

What are the implications for that? Can users take ownership?

Regards

John

 

[LittleSmudge]

Administer means:-

For databases, set database password, replicate a database, and change startup properties.
For tables, queries, forms, reports, and macros, have full access to these objects and data, including ability to assign permissions.

So infact it is 'Administer Queries' that you need to give them. And only give it to the person (people) that need it - Create a Group that has this permission and just assign those people to the Group that need the ability.

See MSAccess Help page "Work with permissions"
and then sub page "Learn about types of permissions"




'ope-that-'elps.






G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[JDRoss]

Thanks for your help, GLS.

I have sourced my problem after reading the excellent 39-page doc from Microsoft.

"Frequently Asked Questions About Microsoft® Access Security for Microsoft Access versions 2.0 through 2000"
Version 2.41 October, 2000
By Mary Chipman, Andy Baron, Chris Bell, Michael Kaplan, Paul Litwin, and Rudy Torrico

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

I am using a backend, which had not being secured. Hence the errors, but the problem was more to do with understanding permissions on liked tables and how to reestablishing the links.

Thanks for all your help, especially the article writers above.

It deals very comprehensively with issues to do with securing a backend frontend type database and answered all of my questions.

Regards

John