By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,262 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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" <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

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" <Ru******@xtra.NOSPAM.co.nz> 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" <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


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" <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

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" <ar*****@start.no> 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" <Ru******@xtra.NOSPAM.co.nz> 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" <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



Nov 12 '05 #5

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

P: n/a
Arild,

Have you tried replacing "Having" with "Where"?

PC Datasheet

"Arild LArsen" <ar*****@start.no> wrote in message
news:10**************************@posting.google.c om...
"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

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" <sp**@nospam.net> wrote in message news:<JT*****************@newsread1.news.atl.earth link.net>...
Arild,

Have you tried replacing "Having" with "Where"?

PC Datasheet

"Arild LArsen" <ar*****@start.no> wrote in message
news:10**************************@posting.google.c om...
"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

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.