435,639 Members | 2,262 Online
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

# Query

 P: n/a 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 Nov 12 '05 #1
7 Replies

 P: n/a Create a query, with the table that you have, then click on the 'Totals' button (it's the one that sort of looks like a weird capital E... Can't remember the greek name for it)... Then in the 'Total' row, set the option to 'Group By' for the Pipedim field and 'Sum' for the Length field... "Arild Larsen" 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 Nov 12 '05 #2

 P: n/a Hello again I think I did'nt make this clear enough I only Want the total for for example 10 and not the rest.The point is that these records are going to be added to another table as 1 record for 10 one for 2 and so on. regards Arild Larsen "Ruskin Hardie" wrote in message news:LI**********************@news.xtra.co.nz... Create a query, with the table that you have, then click on the 'Totals' button (it's the one that sort of looks like a weird capital E... Can't remember the greek name for it)... Then in the 'Total' row, set the option to 'Group By' for the Pipedim field and 'Sum' for the Length field... "Arild Larsen" 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 Nov 12 '05 #3

 P: n/a 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" 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 Nov 12 '05 #4

 P: n/a Arild, I provided the same answer as Ruskin. From what you say here, your table are not designed correctly. You can get the same information you want in the other table from the table you originally provided in your post. There's no need for the second table! -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications www.pcdatasheet.com "Arild Larsen" wrote in message news:bm**********@troll.powertech.no... Hello again I think I did'nt make this clear enough I only Want the total for for example 10 and not the rest.The point is that these records are going to be added to another table as 1 record for 10 one for 2 and so on. regards Arild Larsen "Ruskin Hardie" wrote in message news:LI**********************@news.xtra.co.nz... Create a query, with the table that you have, then click on the 'Totals' button (it's the one that sort of looks like a weird capital E... Can't remember the greek name for it)... Then in the 'Total' row, set the option to 'Group By' for the Pipedim field and 'Sum' for the Length field... "Arild Larsen" 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 Nov 12 '05 #5

 P: n/a "PC Datasheet" wrote in message news:... 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" 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 Nov 12 '05 #6

 P: n/a Arild, Have you tried replacing "Having" with "Where"? PC Datasheet "Arild LArsen" wrote in message news:10**************************@posting.google.c om... "PC Datasheet" wrote in message news:... 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" 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 Nov 12 '05 #7

 P: n/a Yes ,but this doesn't work with Totals queries.I, split up the queriy even more and then got the result I wanted.I have to do the operation in several steps with several queries. Regards Arild Larsen "PC Datasheet" wrote in message news:... Arild, Have you tried replacing "Having" with "Where"? PC Datasheet "Arild LArsen" wrote in message news:10**************************@posting.google.c om... "PC Datasheet" wrote in message news:... 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" 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 Nov 12 '05 #8

### This discussion thread is closed

Replies have been disabled for this discussion.