Extract Field Values from Multiple records for a single criteria

[Greg K.]

Not quite sure how to explain this.

I have orders in a table that can get shipped from a multitude of warehouses
(13 potential to be exact) spanning many records. Out of these, there are 4
control centres, meaning that certain warehouse numbers belong to certain
centres. The warehouse # is in a field in the order details table.

When a change is made to the order (whether to the entire order in the case
of a ship date change for example, or to selected lines to change a
quantity), I want to be able to scan through the order to determine which
centres are affected and email to the appropriate centres. I do not want to
select just the records for selected warehouses.

Basically it would be the same as saying that for selected order #s, if
field "column" WAREHOUSE contains 61, 73, or 74 then email to Centre A, if
it contains 66 or 76 then email to Centre B, etc .....

Any help you can provide would be greatly appreciated. I've been racking my
brain for hours on end.

Greg K

 

[tina]

if your tables design does not include a tblCentres, linked to a
tblWarehouses, then consider adding that to the design. tblCentres should
have a one-to-many relationship with tblWarehouses: one centre may handle
many warehouses, but each warehouse is handled by one one centre.

once each warehouse is assigned to a centre in the database, you can run a
query to pull the associated centre for each warehouse in the order details
records that you're working with.

hth

 

[Greg K.]

But wouldn't that still just allow me to select individual records from the
order? I want to email the entire order summary to each Centre that is
found anywhere in the order.

 

[tina]

if you have the setup i described, and you're having trouble writing a query
to identify the centre(s) associated with *all* the records you're concerned
with, then post the SQL and we'll try to help you figure it out.

hth