please help me

[mpb.vu2]

hi I am mac
I am learning access for few months
I got a problem please help me:
In the table I have two fields one is text field and the other is number
in text field i have a, b, c, d, .... , z values and in number field i have numbers corresponding to that text field.
Now i want to make some calculation and prepare a report. the formula will be like c = a+b, g = d+e+f, etc.
I want c and g will appear automatically in the report.
how can i do that?

 

[mpb.vu2]

sorry.. I mean C and G will appear in a quary in the same field where a, b, c... are. Is that possible?

 

[neileg]

If you are storing a list of data in a single field separated by commas, you are going to have all sorts of problems. Basically your design is wrong. You need to look into the topic of normalisation. You should have a separate record for each piece of data, then the task you are facing becomes very easy.

 

[mpb.vu2]

thanks neil...
but i think i couldn't explain my problem well
Im tellin again:
My table is follows:
field1(text) field2(numeric)
a1 2
a2 3
a3 4
A
b1 5
b2 6
B

is it possible to put subtotals of a1+a2+a3 and b1+b2 as A and B respectively in one query?

 

[Ron_dK]

I would suggest that you make a form displaying your field 1 and 2 data.
On this form you can add a textbox. In the textbox control source put something like :

=DSum("[field2]","yourtable","[Field1] is like a*")
This will give you the total of all a1, a2, etc.

By adding another textbox with :
=DSum("[field2]","tbone","[Field1] is like b*")
this should give you the total of b1, b2, etc.

 

[neileg]

Ah, OK.

If you want a query that returs the total of A and the total of B (or indeed any other letter, try this:

SELECT Left([Field1],1) AS Letter, Sum(tblTable.Field2) AS SumOfField2
FROM tblTable
GROUP BY Left([Field1],1);

What this does is to create a calculated field that strips the first character from Field1 ie the letter part, and then totals the values by this field. Is that what you want?

Of course, if you are using this data in a form or a report, you don't need this total in the query as it can be done in the form or report.

 

[Ron_dK]

Excellent piece of SQL Neil. I would have never thought of doing it like that.
In fact it's better than the textbox thing I described. Sometimes one tend to overlook the simple soluations.
Thanks for the pointer.
Cheers, Ron

 

[neileg]

Thanks for the compliment, Ron, but I only do simple.

 

[mpb.vu2]

owwwwwwww...
looking good...
thanks - you both especially neil...
I was pringging my head to solve this problem for the last two moths...
let me practice this first...
If it works... I will entertain both of you in Helvetia... lolz