"PC Datasheet" <sp**@nospam.net> wrote in message news:<b3*******************@newsread2.news.atl.ear thlink.net>...
Create a totals query that includes the two fields. Leave the PipeDim field as
GroupBy and change GroupBy of the Length field to Sum.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
"Arild Larsen" <ar*****@start.no> wrote in message
news:bm**********@troll.powertech.no... Hei
I'd like to find total of one field based on the size of another field
Pipedim Length
2 2
2 3
2 2
1 3
1 4
3 6
3 1
3 2
10 4
10 3
10 7
Here I want to find the total Length for those whom are Pipedim 10 then 2
and so on. The problem is I don't know the size so I can't set criteria.I
think I've seen this done, and if I cold get of with some queries insted of
a lot of VBA then ...
Thanks
MVH
Arild Larsen
Hello again
One more try:
It's a bit more complex , this is going to be a system for calculating
material for technical insulation. When the calculation is done, it's
going into an ordering system. So here is a more detailed description.
The system is not properly normalized because it isn't suitable
main table:
tblEstParent
AO (Work order) Long integer PK
DatoPåbegynt (date started) Date/time
DatoFerdig (Date finished) Date/time
There are several child tables but with similar contents so here is
one
tblEstimat4
AO (Work order) Long integer FK
Linje (Line number) string
TegningNR (drawing number)string
RørDim (pipe dimension) string (because in inches)
Lengde (pipe length) double
Type (type: Valve, pipe and so on) string
Svinn (waste) integer
LMFoam (Calculated quantity of Foamglas) double
M2Steel (Calculated quantity of Steel) double
M2Firemaster (Calculated quantity of Firemaster) double
HT (Heattrace) yes/no Boolean
TypeFoam (Type of Foamglas) String
TypeFiremaster(Type of Firemaster) String
TypeSteel (Type of Steel) String
ISOKlasse (Insulation class) Integer
Rørtemp (pipe design temperature)
ANTBEND45 ( number of 45 deg pipe bend)
ANTBEND90 ( number of 90 deg pipe bend)
ANTSUP ( number of pipe supports)
ANTEB ( number of boxends)
ANTEL ( number of sealends)
ANTKON ( number of cones)
Kommentar (comments)
TypeFoam45 (Type of 45 deg pipe Foamglas bend) String
TypeFoam45 (Type of 90 deg pipe Foamglas bend) String
Prefab (Prefabrication onshore)yes/no Boolean
For my example the query designed today looks like this in SQL
SELECT tblEstimat4.AO, tblEstimat4.Prefab, tblEstimat4.RørDim,
Sum(tblEstimat4.Lengde) AS SumOfLengde, tblEstimat4.Type,
Sum(tblEstimat4.LMFoam) AS SumOfLMFoam, Sum(tblEstimat4.M2Steel) AS
SumOfM2Steel, Sum(tblEstimat4.M2Firemaster) AS SumOfM2Firemaster,
tblEstimat4.HT, tblEstimat4.TypeFoam, tblEstimat4.TypeFiremaster,
tblEstimat4.TypeSteel, tblEstimat4.ISOKlasse, tblEstimat4.Rørtemp,
tblEstimat4.ANTBEND45, tblEstimat4.ANTBEND90, tblEstimat4.ANTSUP,
tblEstimat4.ANTEB, tblEstimat4.ANTEL, tblEstimat4.ANTKON,
tblEstimat4.Kommentar, tblEstimat4.TypeFoam45, tblEstimat4.TypeFoam90
FROM tblEstimat4
GROUP BY tblEstimat4.AO, tblEstimat4.Prefab, tblEstimat4.RørDim,
tblEstimat4.Type, tblEstimat4.HT, tblEstimat4.TypeFoam,
tblEstimat4.TypeFiremaster, tblEstimat4.TypeSteel,
tblEstimat4.ISOKlasse, tblEstimat4.Rørtemp, tblEstimat4.ANTBEND45,
tblEstimat4.ANTBEND90, tblEstimat4.ANTSUP, tblEstimat4.ANTEB,
tblEstimat4.ANTEL, tblEstimat4.ANTKON, tblEstimat4.Kommentar,
tblEstimat4.TypeFoam45, tblEstimat4.TypeFoam90
HAVING (((tblEstimat4.AO)=[AO?]) AND ((tblEstimat4.Prefab)=False) AND
((tblEstimat4.Type)="Rør"))
ORDER BY tblEstimat4.RørDim;
This produces the following result:
2 record with 1" pipes
and 4 records 4 2" pipes
What I want is 1 record with 1" pipe and 1 record with 2" pipe that
way I can add one record with materials for the 1" pipes to the order
table(other MDB) and 1 record with materials for the 2" pipes.
It gets even more complicated but if I could resolve this one I'm well
on my wayJ
Thenks
Regards
Arild Larsen
