An Automatic Update Field To Show New Number Of Contacts

[dan.cawthorne]

i request some help with in my contract address book and enquiry system
i have created. or if you could advise me where i could look for some
resources which would help me.

My main Table in a contract database is contacts table, which i have a
field labled M or E, which this allows users to do a query search and
well display Mechanical or electrical contracts.

What I would like to have with on my switch board form,

Is a Mechanical Field = Number Mechanical Contacts

and a Electrical field = Number Electrical Contracts.

But i want this fields to automaticly update when either mechanical or
electricaly new contact is updated!

could you help?

 

[Douglas J Steele]

You shouldn't be storing the totals in a table. As fellow Access MVP John
Vinson likes to say "Storing calculated data generally accomplishes only
three things: it wastes disk space, it wastes time (a disk fetch is much
slower than almost any reasonable calculation), and it risks data validity,
since once it's stored in a table either the Total or one of the fields that
goes into the total may be changed, making the value WRONG."

Instead, you should create a query that contains the necessary information
from the table, as well as calculates the totals by joining to the
appropriate tables and counting the entries. Use the query wherever you
would otherwise have used the table.

 

[dan.cawthorne]

Thanks for the advise!
So what you saying is i need to creat an query which pulls the M
entries out and query what pulls the E entries and stores then
seperatly, where do i go from there?

 

[Douglas J Steele]

What I'm suggesting is creating a query that joins your existing table to
the other tables, and making that query a Totals query.

 

[dan.cawthorne]

Sorry for been a little slow Douglas,

But im a novice here,

How do i create a query that makes it a totals query?
from either my M query or my E Query?

 

[Douglas J Steele]

What does the table look like?

Let's assume it's something like:

Contacts
FirstName
LastName
ContactType

The SQL for your query would look like:

SELECT FirstName, LastName,
Sum(IIf([ContactType] = "M", 1, 0) AS Mechanical,
Sum(IIf([ContactType] = "E", 1, 0) AS Electrical
FROM Contacts
GROUP BY FirstName, LastName

To create this query using the GUI, you'd create a query like normal, and
add your contact table to that query. Drag whatever fields you want from the
Contacts table into the grid. In a blank column, type the following on the
Field row:

Mechanical: IIf([ContactType] = "M", 1, 0)

In another blank column, type the following on the Field row:

Electrical: IIf([ContactType] = "E", 1, 0)

Now, turn the query into a Totals query (either by clicking on the Sigma
icon on the button bar, or selecting Totals on the View menu). Make sure
that it says "Group By" under every field except for the two fields you
added manually above. They should say "Sum"

(Make sure you change the field and table names as appropriate.)

 

[dan.cawthorne]

Thank ou very Much i'll give that bash tommorrow, i pretty much
understood that :O)

What does the table look like?

Let's assume it's something like:

Contacts
FirstName
LastName
ContactType

The SQL for your query would look like:

SELECT FirstName, LastName,
Sum(IIf([ContactType] = "M", 1, 0) AS Mechanical,
Sum(IIf([ContactType] = "E", 1, 0) AS Electrical
FROM Contacts
GROUP BY FirstName, LastName

To create this query using the GUI, you'd create a query like normal, and
add your contact table to that query. Drag whatever fields you want from the
Contacts table into the grid. In a blank column, type the following on the
Field row:

Mechanical: IIf([ContactType] = "M", 1, 0)

In another blank column, type the following on the Field row:

Electrical: IIf([ContactType] = "E", 1, 0)

Now, turn the query into a Totals query (either by clicking on the Sigma
icon on the button bar, or selecting Totals on the View menu). Make sure
that it says "Group By" under every field except for the two fields you
added manually above. They should say "Sum"

(Make sure you change the field and table names as appropriate.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry for been a little slow Douglas,

But im a novice here,

How do i create a query that makes it a totals query?
from either my M query or my E Query?