Updating Data from a 'GROUP BY' query

[Guest]

Hi,

I need to solve this problem: I have a huge database that lists all
products, by Order - Item, and has shipping information ('Vessel',
'Destination', 'Departure Date' and 'Arrival Date'). Each vessel and
destination may have thousands of order - items included so updating dates is
very upsetting and time wasting.

I want to uptade the Departure and Arrival dates grouped by 'Vessel' and
'Destination', and not for each Order - Item. I've created a 'SELECT ...
GROUP BY' query but now I don't know how to update the info I see.

Can somebody help me out pls?

Thanks. Bregards

 

[John Spencer]

You cannot update data that is grouped or uses any of the aggregate functions.
SQL does not have any way of breaking apart the data it has combined.

HOWEVER, it seems to me that your problem is one of table design. You need to
break apart your current single table into multiple tables.

Vessels -- Ship name and details about the ship that you need (perhaps,
registry, type, etc.)
Destinations -- Routing information) VesselID, DestinationID, Port, ArrivalDate, DepartureDate
Products -- DestinationID, Product, etc.

That way if the Vessels arrival date changes at any destination you change ONE
record and all products being sent to that port on that vessel on that arrival
date get "changed".

The only way you can do this with your current table set up is to use an update
query to change all the rows. You could do this through a form or just by using
a query directly.

UPDATE YourTable
SET [Departure Date] = #1/12/2006#
WHERE Vessel = "Whatever" And [Destination] = "New York" AND [Departure date] = #12/31/2005#

I would use an input form with comboboxes displaying the vessels and the destinations.

 

[Guest]

Yap, after some time I got to the same conclusion. The only problem I have is
choosing the correct 'key' to link 'products' table and 'vessel' table...

Thanks. I'll develop it in that way. Bregards

Santiago

You cannot update data that is grouped or uses any of the aggregate functions.
SQL does not have any way of breaking apart the data it has combined.

HOWEVER, it seems to me that your problem is one of table design. You need to
break apart your current single table into multiple tables.

Vessels -- Ship name and details about the ship that you need (perhaps,
registry, type, etc.)
Destinations -- Routing information) VesselID, DestinationID, Port, ArrivalDate, DepartureDate
Products -- DestinationID, Product, etc.

That way if the Vessels arrival date changes at any destination you change ONE
record and all products being sent to that port on that vessel on that arrival
date get "changed".

The only way you can do this with your current table set up is to use an update
query to change all the rows. You could do this through a form or just by using
a query directly.

UPDATE YourTable
SET [Departure Date] = #1/12/2006#
WHERE Vessel = "Whatever" And [Destination] = "New York" AND [Departure date] = #12/31/2005#

I would use an input form with comboboxes displaying the vessels and the destinations.

Hi,

I need to solve this problem: I have a huge database that lists all
products, by Order - Item, and has shipping information ('Vessel',
'Destination', 'Departure Date' and 'Arrival Date'). Each vessel and
destination may have thousands of order - items included so updating dates is
very upsetting and time wasting.

I want to uptade the Departure and Arrival dates grouped by 'Vessel' and
'Destination', and not for each Order - Item. I've created a 'SELECT ...
GROUP BY' query but now I don't know how to update the info I see.

Can somebody help me out pls?

Thanks. Bregards