Separate number from column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have a table with the following column of data:

gooduser (2 pages)

gooduser (10 pages)

I want a query that will calculate the number in the brackets and total it
up for the whole column. I figure a bit of VB will do it butdont knowhow to
do it my self. Thanks for any help
Nathan
 
Nathan

I'm not understanding your data structure. Are you saying your column
(field) contains more than one fact? From your example, it appears to
contain a name/type/category ('gooduser'), a count ('2'), and a what's being
counted ('pages').

If you have more than one fact in one field, you will make considerable
extra work for yourself (and for Access). One of the basic rules in
designing a well-normalized relational database structure is "one field, one
fact".

Or have I misinterpreted your example?
 
You have understood me fine.
The reason the data is the way it is, is because I was importing from
another file (excel) and that was the structure of the data. Is there any way
of just getting the number in the rackets and totalling it up?
 
If you have to use that value, then you can use a calculated value and SUM that.
I'm assuming that you are already using an aggregate (totals) query.

Field: SUM(Val(Mid(YourField,Instr(1,YourField,"(") +1)))

Instr locates the position of the first "(" in your field
Mid returns characters starting at that position (plus 1)
VAL converts the string to a numeric value
SUM adds all the fields up
 
Thanks for that.
Im not very good with access. Could you explain to me how to apply this to
my query.
Cheers
nathan
 
I think I have it working. However it just records a zero in the expression
column for each record.
Any ideas
 
When run on the following set of data the following results were output

Field1 Expr1
complete (0 pages) 0
complete (2 pages) 24
complete (4 pages) 20
complete (6 pages) 6
complete (8 pages) 16
 
Then I would assume that
You have an indeterminate number of records with 0 pages
12 records with 2 Pages
5 records with 4 pages
1 record with 6 pages
2 records with 8 pages

Do you want something different like the count of records with the value? You
asked for the sum.
 
Back
Top