Very Basic

[rnash1]

I have done this many times before but must be missing something. I am just trying to gather number data from an existing query or table that shows up in a text field. I keep getting syntax errors. ??

I am also looking for a reference book that has many examples of calculated controls and expressions. This would be for form use.

[NeoPa]

I'm sorry, but you provide too little information to work with.

[rnash1]

I'm sorry, but you provide too little information to work with.

As usual I thought about it after I sent it. I agree not enough info. I'm trying to creat a text field that will show the exact # of records in a table thats already created. The I.D. #s are out of order and out of sequence so I added a new colunm called quantity and manulally #s in seguence but for some reason this just wont work. If I creat a new database this works, but not in the current one used. Sum=([quantity]) This is so simple, I cant figure out whats happening with this.

[Stewart Ross]

Hi. If you want to count the number of records use the Count function to do so. It is more useful to group the records together (by Dept, for example) so that the count is not just a single count of all the records but a meaningful count of the number within that specific grouping:

SELECT Dept, Count(*) as RecCount from Sometable GROUP BY Dept;

If you want to sum a field, as in your example, the approach is similar:

SELECT Dept, Sum([Quantity]) as NumberSold, Count(*) as RecCount from Sometable GROUP BY Dept;

On a related but different application which does not involve SQL, if you wanted to have an unbound control on a form or a report do the sum of a field called Quantity (assumed to be a part of the underlying table or query) you would set the control's control source property to

=Sum([Quantity])

I wonder if it is mixing these two syntaxes up which is causing at least part of your difficulties?

-Stewart