All records

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
I

Ivor Williams

I have a form with a subform in a database I use to track inventory
movement. In the form are two combo boxes from which I choose a source and
destination for the inventory. I'm trying to set up a query to tell me the
quantity of each item in inventory I have in each location. The query only
returns those items which have been moved to and from a chosen location, not
those items which have only gone to the location. I'm sure this is probably
a relationship issue, but don't know how to resolve it. Help, please.

Ivor
 
Ivor,

It would be helpful if you could post the SQL view of the query you are
trying so far, or else some more details of your data and fields involved.

On the face of what you have said so far, it would be necessary to do it
in three steps... Get the total of each item that has gone "to" the
chosen destination, get the total of each item that has gone "from" the
chosen destination, and then subtract one from another.

It would appear that you have the source and destination as two separate
fields for each movement. A better design would be to have a separate
table for MovementLocations, with a Location field and a LocationType
field (source or destination), and so this data is recorded as two
separate records rather than two separate fields. This would make the
kind of calculations you are doing now a lot simpler.
 
Back
Top