Count Function - Text Field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aMack - 30 May 2006 16:56 GMT

I need to create an update query that will count the number of fields NOT
Blank and enter the result in another field - "Eq Count".

Field Names "Wheels", "Chassis A", "Chassis B", "Chassis C".

If all fields have data the result would be 4, if one is blank, the result
should be 3.

How do I do it??

Thanks

Signature

A MACKENZIE, CMA, MBA

Reply to this Message

KARL DEWEY - 30 May 2006 17:21 GMT

Try this --
Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
IIF([Chassis B] Is Not Null, 1, 0) + IIF([Chassis C] Is Not Null, 1, 0))

> I need to create an update query that will count the number of fields NOT
> Blank and enter the result in another field - "Eq Count".
[quoted text clipped - 7 lines]
>
> Thanks

Reply to this Message

aMack - 31 May 2006 16:18 GMT

Thanks:

Error message on execution: "You tried to execute a query that does not
include the specified expression "Chassis Count" as part of an aggregate
function.

Signature

A MACKENZIE, CMA, MBA

> Try this --
> Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
[quoted text clipped - 11 lines]
> >
> > Thanks

Reply to this Message

KARL DEWEY - 31 May 2006 18:23 GMT

Post your query SQL. Open the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

> Thanks:
>
[quoted text clipped - 17 lines]
> > >
> > > Thanks

Reply to this Message

aMack - 31 May 2006 19:49 GMT

UPDATE [Chassis Ingates] SET [Chassis Ingates].[Chassis Count] =
Sum(IIf([Wheels] Is Not Null,1,0)+IIf([Chassis A] Is Not
Null,1,0)+IIf([Chassis B] Is Not Null,1,0)+IIf([Chassis C] Is Not Null,1,0));

Thx

Signature

A MACKENZIE, CMA, MBA

> Post your query SQL. Open the query in design view, click on menu VIEW - SQL
> View, highlight all, copy, and paste in a post.
[quoted text clipped - 20 lines]
> > > >
> > > > Thanks

Reply to this Message

KARL DEWEY - 31 May 2006 21:55 GMT

You are summing a bunch of records from the [Chassis Ingates] table.
Which one of the records of [Chassis Ingates] are you wanting to update?

There is no need and is bad databasing to store calculations in your table.
Now if you had dates with this and you wanted to store a snapshot calculation
you would want to append the point-in-time data to a history file.

If you insist then you can do it in two queries - first one sum and second
update - but it will update all the records with the same information.

> UPDATE [Chassis Ingates] SET [Chassis Ingates].[Chassis Count] =
> Sum(IIf([Wheels] Is Not Null,1,0)+IIf([Chassis A] Is Not
[quoted text clipped - 26 lines]
> > > > >
> > > > > Thanks

Reply to this Message

aMack - 01 Jun 2006 00:28 GMT

Thank you for your help.

I am able to place the count in both forms and Reports instead of the
database as you suggeted.

Removing the "Sum" portion of the calc gives me correct data for each line.

Thanks again.

Signature

A MACKENZIE, CMA, MBA

> You are summing a bunch of records from the [Chassis Ingates] table.
> Which one of the records of [Chassis Ingates] are you wanting to update?
[quoted text clipped - 36 lines]
> > > > > >
> > > > > > Thanks

Reply to this Message