I am creating a select query that will be changed into a "make table" 
query when finished. The results I am looking for are:


ID	Code	L1	L2	L3	L4	L5	L6
T-1	A	15	72	23	92	45	197
T-1A	A	23	72	24	92	76	197
T-1B	A	34	72	45	92	76	197
T-2	B	76	88	12	35	45	59
T-2A	B	8	88	14	35	2	59
T-2B	B	4	88	9	35	12	59

My problem is getting L2, L4, and L6
L2 is equal to the sum of L1 with a code A or B.
That is to say L2 of T-1 is 15 + 23 + 34 = 72
L4 is equal to the sum of L3 with a code A or B.
That is to say L4 of T-1 is 23 + 24 + 45 = 92

So in my select query, I have 2 tables:
---------------------------------------------------------------------------
plat_id and table2
	linked through field "ID"
---------------------------------------------------------------------------
Field:	ID	code	L1:L5	L2:?	L3:L15	L4:?	L5:L20	L6:?
Table:	plat_id	plat_id	table2		table2		table2
Sort:
Show:	x	x	x	x	x	x	x	x	
Criteria:
or:
---------------------------------------------------------------------------

In Table2, L5, L15, and L20 are calculated fields from other tables that 
will be updated every couple of months so that when they are updated, this 
query will have to be re-run to re-calculate the new data.

There are a total of 100 ID's so it ends up being a large table.

How do you suggest tackling this?

Thanks

The best way to tackle it in my view is to completely get rid of the "make table" queries.

If the new tables are updated "every couple of months" then any information you extract from 
your data could be a couple of months out of date!
Unless I'm missing something here - there's HARDLY EVER a reason to use a make table query. 
And especially one that stores calculated fields.
Say, for example you have a table with someone's date of birth and date of death, and 
someone wants to know how old people were when they died. There's no point having a 
whole field for their age, because it's already in the other two fields - just have a query that 
does something along the lines of DateDiff("yyyy", [DOB], [DOD])
If needs be - you can then make a query on that query.

I know that all this hasn't answered yr question yet, so I'll get to that now.

If you want the sum of all the L1s where the ID starts with "T-1", all you have to do is use  
string functions (see Access Help on Left, Right, and Mid for the syntax and examples if you 
need it), have that as a group by - then have L1 as a Sum.

Then have it as an update query to update the L4 field

I'm not sure why you'd want the same data (which is just a "calculated field" anyway) in so 
many records.

The way I see it - you can do away with L4


> I am creating a select query that will be changed into a "make table" 
q> uery when finished. The results I am looking for are:

> 
I> D	Code	L1	L2	L3	L4	L5	L6
T> -1	A	15	72	23	92	45	197
T> -1A	A	23	72	24	92	76	197
T> -1B	A	34	72	45	92	76	197
T> -2	B	76	88	12	35	45	59
T> -2A	B	8	88	14	35	2	59
T> -2B	B	4	88	9	35	12	59

> My problem is getting L2, L4, and L6
L> 2 is equal to the sum of L1 with a code A or B.
T> hat is to say L2 of T-1 is 15 + 23 + 34 = 72
L> 4 is equal to the sum of L3 with a code A or B.
T> hat is to say L4 of T-1 is 23 + 24 + 45 = 92

> So in my select query, I have 2 tables:
-> 
--------------------------------------------------------------------------
p> lat_id and table2
	> linked through field "ID"
-> 
--------------------------------------------------------------------------
F> ield:	ID	code	L1:L5	L2:?	L3:L15	L4:?	L5:L20	
L6:?
T> able:	plat_id	plat_id	table2		table2		table2
S> ort:
S> how:	x	x	x	x	x	x	x	x	
C> riteria:
o> r:
-> 
--------------------------------------------------------------------------

> In Table2, L5, L15, and L20 are calculated fields from other tables that 
w> ill be updated every couple of months so that when they are updated, 
this 
q> uery will have to be re-run to re-calculate the new data.

> There are a total of 100 ID's so it ends up being a large table.

> How do you suggest tackling this?

> Thanks