query needed please help

[subs]

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20 450
C D 4/1/08 15 890
E F 9/1/08 78 750
A B 12/1/07 42 789
X Y 1/1/08 17 900
C D 4/1/08 34 90

Needed Output

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20
450
A B 12/1/07 42 789
E F 9/1/08 78
750

I need to create a query which extracts rows with wgt >60 and also
when the fields ozip, dzip and shipdate are same, the query should add
the wgts and the sum of wgts should be >60- for example the first and
fifth rows should be extracted since sum is 62. The third record
should also be extracted since wgt is >60.
I have attached the output that is required for the above example.
please help

 

[Jeff Boyce]

I'm not sure if I understand your description...

If I'm interpreting it correctly, you might be able to create a Totals query
first that sums the [wgt] field for all unique combinations of [ozip] &
[dzip] and [shipdate]. This would use GroupBy for those three fields and
Sum for the [wgt] field.

Once that query was working, you could create a second query against that
first one to find all combinations of [ozip] & [dzip] and [shipdate] with
[wgt]>60 (do you mean >60 or >=60?).

Then, if you needed the details back in the original table, you could join
the second query (with the three fields + wgt) back to the table to see all
the info (in a third query).

Finally, you might need/want to create a report and "group by" to get totals
for each unique combination.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[subs]

I'm not sure if I understand your description...

If I'm interpreting it correctly, you might be able to create a Totals query
first that sums the [wgt] field for all unique combinations of [ozip] &
[dzip] and [shipdate].  This would use GroupBy for those three fields and
Sum for the [wgt] field.

Once that query was working, you could create a second query against that
first one to find all combinations of [ozip] & [dzip] and [shipdate] with
[wgt]>60 (do you mean >60 or >=60?).

Then, if you needed the details back in the original table, you could join
the second query (with the three fields + wgt) back to the table to see all
the info (in a third query).

Finally, you might need/want to create a report and "group by" to get totals
for each unique combination.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

ozip   dzip    shipdate       wgt         Transport cost

A       B          12/1/07    20             450
C       D          4/1/08      15              890
E       F          9/1/08       78             750
A      B           12/1/07      42            789
X      Y            1/1/08        17            900
C      D            4/1/08         34            90

Needed Output

ozip   dzip    shipdate       wgt         Transport cost

A       B          12/1/07      20
450
A      B           12/1/07      42            789
E       F          9/1/08       78
750

I need to create a query which extracts rows with wgt >60  and also
when the fields ozip, dzip and shipdate are same, the query should add
the wgts and the sum of wgts should be >60- for example the first and
fifth rows should be extracted since sum is 62.  The third record
should also be extracted since wgt is >60.
I have attached the output that is required for the above example.
please help- Hide quoted text -

- Show quoted text -

Is there a single SQL statement?

 

[John Spencer]

One method would be as follows
SELECT Ta.*
FROM YourTable as Ta
WHERE Exists
(SELECT Sum(Wgt)
FROM YourTable as Tb
WHERE Tb.Ozip = Ta.Ozip
And Tb.DZip = Ta.Dzip
And Tb.ShipDate = Ta.ShipDate
GROUP BY Tb.Ozip, Tb.Dzip, ShipDate
HAVING Sum(Wgt) >= 60)

Another method would be to use a sub query in the from clause (probably the
fastest but probably not updateable)

SELECT Ta.*
FROM YourTable as TA
INNER JOIN
(SELECT Ozip, DZip, ShipDate
FROM YourTable as Tc
GROUP BY Ozip, Dzip, ShipDate
HAVING Sum(Wgt) >= 60) as Tb
ON Ta.Dzip = Tb.Dzip
AND Ta.Ozip = Tb.Ozip
AND Ta.ShipDate = Tb.ShipDate

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jeff Boyce]

Why? If it works, what does it matter? (I'm not aware of one, but perhaps
other newsgroup readers can offer a more elegant solution...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm not sure if I understand your description...

If I'm interpreting it correctly, you might be able to create a Totals
query
first that sums the [wgt] field for all unique combinations of [ozip] &
[dzip] and [shipdate]. This would use GroupBy for those three fields and
Sum for the [wgt] field.

Once that query was working, you could create a second query against that
first one to find all combinations of [ozip] & [dzip] and [shipdate] with
[wgt]>60 (do you mean >60 or >=60?).

Then, if you needed the details back in the original table, you could join
the second query (with the three fields + wgt) back to the table to see
all
the info (in a third query).

Finally, you might need/want to create a report and "group by" to get
totals
for each unique combination.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20 450
C D 4/1/08 15 890
E F 9/1/08 78 750
A B 12/1/07 42 789
X Y 1/1/08 17 900
C D 4/1/08 34 90

Needed Output

ozip dzip shipdate wgt Transport cost

A B 12/1/07 20
450
A B 12/1/07 42 789
E F 9/1/08 78
750

I need to create a query which extracts rows with wgt >60 and also
when the fields ozip, dzip and shipdate are same, the query should add
the wgts and the sum of wgts should be >60- for example the first and
fifth rows should be extracted since sum is 62. The third record
should also be extracted since wgt is >60.
I have attached the output that is required for the above example.
please help- Hide quoted text -

- Show quoted text -

Is there a single SQL statement?

 

[subs]

One method would be as follows
SELECT Ta.*
FROM YourTable as Ta
WHERE Exists
   (SELECT Sum(Wgt)
    FROM YourTable as Tb
    WHERE Tb.Ozip = Ta.Ozip
     And Tb.DZip = Ta.Dzip
     And Tb.ShipDate = Ta.ShipDate
    GROUP BY Tb.Ozip, Tb.Dzip, ShipDate
    HAVING Sum(Wgt) >= 60)

Another method would be to use a sub query in the from clause (probably the
fastest but probably not updateable)

SELECT Ta.*
FROM YourTable as TA
INNER JOIN
   (SELECT Ozip, DZip, ShipDate
    FROM YourTable as Tc
    GROUP BY Ozip, Dzip, ShipDate
    HAVING Sum(Wgt) >= 60) as Tb
ON Ta.Dzip = Tb.Dzip
AND Ta.Ozip = Tb.Ozip
AND Ta.ShipDate = Tb.ShipDate

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.











- Show quoted text -

hi

Thanks for your help . I tried your first option but in the query
output, i get an empty column for sum(weight) named as field1. Field1
is toltally empty Any ideas why i am getting this result> This is for
Johns reply

 

[John Spencer]

You didn't ask for the sum of the weights to show up. You asked for the
individual records that made up the sum.

If you use option two with a modification I think you will get what you seem
to be asking for.

SELECT Ta.*, tb.TotalWeight
FROM YourTable as TA
INNER JOIN
(SELECT Ozip, DZip, ShipDate
, Sum(Wgt) as TotalWeight
FROM YourTable as Tc
GROUP BY Ozip, Dzip, ShipDate
HAVING Sum(Wgt) >= 60) as Tb
ON Ta.Dzip = Tb.Dzip
AND Ta.Ozip = Tb.Ozip
AND Ta.ShipDate = Tb.ShipDate

Your other option is to use a subquery in the select clause to get the total
weight. If you will post the query you are using that is not giving you the
total weight, perhaps we can modify it to give you what you want.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

One method would be as follows
SELECT Ta.*
FROM YourTable as Ta
WHERE Exists
(SELECT Sum(Wgt)
FROM YourTable as Tb
WHERE Tb.Ozip = Ta.Ozip
And Tb.DZip = Ta.Dzip
And Tb.ShipDate = Ta.ShipDate
GROUP BY Tb.Ozip, Tb.Dzip, ShipDate
HAVING Sum(Wgt) >= 60)

Another method would be to use a sub query in the from clause (probably
the
fastest but probably not updateable)

SELECT Ta.*
FROM YourTable as TA
INNER JOIN
(SELECT Ozip, DZip, ShipDate
FROM YourTable as Tc
GROUP BY Ozip, Dzip, ShipDate
HAVING Sum(Wgt) >= 60) as Tb
ON Ta.Dzip = Tb.Dzip
AND Ta.Ozip = Tb.Ozip
AND Ta.ShipDate = Tb.ShipDate

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.











- Show quoted text -

hi

Thanks for your help . I tried your first option but in the query
output, i get an empty column for sum(weight) named as field1. Field1
is toltally empty Any ideas why i am getting this result> This is for
Johns reply