Create query that subtracts fields

[Guest]

I have two tables, ProdRcvd and ProdShpd. Each table has a field LotNo which
may be common to each field. I wish to create a query that subtracts
ProdShpd.LotNo from ProdRcvd.LotNo so that I can have my inventory by lot
number. I appreciate any specifics you can provide me.

 

[Jerry Porter]

Why would you subract lot numbers? I would think you would want to get
the total quantity shipped for each log number, and subtract this from
the beginning inventory for each lot number.

 

[John Vinson]

I have two tables, ProdRcvd and ProdShpd. Each table has a field LotNo which
may be common to each field. I wish to create a query that subtracts
ProdShpd.LotNo from ProdRcvd.LotNo so that I can have my inventory by lot
number. I appreciate any specifics you can provide me.

What do you mean by "common to each field"? What kind of data is in
LotNo? If there are multiple records in each table, what field or
fields would allow you to connect an individual record in ProdShpd to
the record for the same shipment in ProdRcvd?

John W. Vinson[MVP]

 

[Guest]

Dear John Vinson,

What I meant by "may be common to each field" is that ProdRcvd table will
contain all lot numbers, wheras the ProdShpd table will only contain the lots
that shipped. The lot numbers are unique and never repeat. A lot received
will consist of multiple bins of product, usually about 24 or 25. How do I
design a query that will show me which lots did not ship? And how do I
account for partial shipments i.e. 20 of 25 bins of product shipped in a
particular lot?

 

[John Vinson]

What I meant by "may be common to each field" is that ProdRcvd table will
contain all lot numbers, wheras the ProdShpd table will only contain the lots
that shipped. The lot numbers are unique and never repeat. A lot received
will consist of multiple bins of product, usually about 24 or 25. How do I
design a query that will show me which lots did not ship?

You can use the "Unmatched Query Wizard" or roll your own using a
"frustrated outer join" query: join the ProdRcvd table to the ProdShpd
table by LotNo. Select the join line, and choose option 2 (or 3) -
"Show all records in ProdRcvd and matching records in ProdShpd". Put a
criterion on ProdShpd.LotNo of

IS NULL

and you'll see only those lots which do NOT appear in ProdShpd.

And how do I
account for partial shipments i.e. 20 of 25 bins of product shipped in a
particular lot?

Without knowing where in your table you're storing that information, I
cannot say for sure. You haven't mentioned bins previously, at least
not that I could see. If there is a field Bins in each table, then you
could join the two tables by LotNo and put a criterion on
ProdShpd.Bins of

< [ProdRcvd].[Bins]

to find those lots for which fewer bins were shipped than received.

John W. Vinson[MVP]

 

[Guest]

I believe I follow what you're are saying. I think the "is null" might be
the light bulb that didn't go off for me. Thank you very much for your
direction. I shall try this this evening and respond.

What I meant by "may be common to each field" is that ProdRcvd table will
contain all lot numbers, wheras the ProdShpd table will only contain the lots
that shipped. The lot numbers are unique and never repeat. A lot received
will consist of multiple bins of product, usually about 24 or 25. How do I
design a query that will show me which lots did not ship?

You can use the "Unmatched Query Wizard" or roll your own using a
"frustrated outer join" query: join the ProdRcvd table to the ProdShpd
table by LotNo. Select the join line, and choose option 2 (or 3) -
"Show all records in ProdRcvd and matching records in ProdShpd". Put a
criterion on ProdShpd.LotNo of

IS NULL

and you'll see only those lots which do NOT appear in ProdShpd.

And how do I
account for partial shipments i.e. 20 of 25 bins of product shipped in a
particular lot?

Without knowing where in your table you're storing that information, I
cannot say for sure. You haven't mentioned bins previously, at least
not that I could see. If there is a field Bins in each table, then you
could join the two tables by LotNo and put a criterion on
ProdShpd.Bins of

< [ProdRcvd].[Bins]

to find those lots for which fewer bins were shipped than received.

John W. Vinson[MVP]

 

[Guest]

John,

I just used the "unmatched querry wizard" and it worked very well. Now my
report only shows lots that have not shipped. Thank you. However, I still
have the issue of partial shipments of a lot. For example, each lot has a #
of bins (BinsRcvd- usually about 28) and a weight (LbsRcvd - usually about
38,000). Frequently, not all bins ship out at once. So in ProdShpd there
will be an entry for that LotNo but it may only be for 20 of the 28 bins.
With the report I just created I am not getting the other 10 because that
LotNo appears in the ProdShpd table. Is there a way to make some kind of
subtraction so that I can show the remaining # of bins and pounds?

I believe I follow what you're are saying. I think the "is null" might be
the light bulb that didn't go off for me. Thank you very much for your
direction. I shall try this this evening and respond.

What I meant by "may be common to each field" is that ProdRcvd table will
contain all lot numbers, wheras the ProdShpd table will only contain the lots
that shipped. The lot numbers are unique and never repeat. A lot received
will consist of multiple bins of product, usually about 24 or 25. How do I
design a query that will show me which lots did not ship?

You can use the "Unmatched Query Wizard" or roll your own using a
"frustrated outer join" query: join the ProdRcvd table to the ProdShpd
table by LotNo. Select the join line, and choose option 2 (or 3) -
"Show all records in ProdRcvd and matching records in ProdShpd". Put a
criterion on ProdShpd.LotNo of

IS NULL

and you'll see only those lots which do NOT appear in ProdShpd.

And how do I
account for partial shipments i.e. 20 of 25 bins of product shipped in a
particular lot?

Without knowing where in your table you're storing that information, I
cannot say for sure. You haven't mentioned bins previously, at least
not that I could see. If there is a field Bins in each table, then you
could join the two tables by LotNo and put a criterion on
ProdShpd.Bins of

< [ProdRcvd].[Bins]

to find those lots for which fewer bins were shipped than received.

John W. Vinson[MVP]

 

[John Vinson]

John,

I just used the "unmatched querry wizard" and it worked very well. Now my
report only shows lots that have not shipped. Thank you. However, I still
have the issue of partial shipments of a lot. For example, each lot has a #
of bins (BinsRcvd- usually about 28) and a weight (LbsRcvd - usually about
38,000). Frequently, not all bins ship out at once. So in ProdShpd there
will be an entry for that LotNo but it may only be for 20 of the 28 bins.
With the report I just created I am not getting the other 10 because that
LotNo appears in the ProdShpd table. Is there a way to make some kind of
subtraction so that I can show the remaining # of bins and pounds?

Of course.

However, I don't know what the expression would be because I cannot
see your database, and you have not informed me how ProdShpd stores
the amount shipped. BinsShpd? LbsShpd? Do you want the discrepancy in
bins, or pounds, or both?

To do a subtraction in a query, change your Query so that it has the
LotNo and whatever fields in ProdShpd are needed for the calculation.
In a vacant Field cell type (for example, again, *I cannot see your
tables*) something like

Shortfall: [ProdRcvd].[BinsRcvd] - NZ([ProdShpd].[BinsShpd])

and use a criterion on this field of > 0 to limit the listing to those
cases where the shipment is not complete. Do the same for the pounds;
put the criterion on a second line so that it will report the
discrepancy whether it's in bins or in pounds. If you do this, be sure
that the IS NULL criterion on [ProdShpd].[LotNo] is on a line by
itself - you want those cases where there are more bins received than
shipped, OR more pounds received than shipped, OR nothing was shipped
at all, and in the query grid, you use separate criteria lines to get
OR logic.

John W. Vinson[MVP]

 

[Guest]

John, You're a genius. It works great. My related report is far more
informative than I ever imagined. This is not my area of expertise and I
would have never figured it out. Thanks again.

John,

I just used the "unmatched querry wizard" and it worked very well. Now my
report only shows lots that have not shipped. Thank you. However, I still
have the issue of partial shipments of a lot. For example, each lot has a #
of bins (BinsRcvd- usually about 28) and a weight (LbsRcvd - usually about
38,000). Frequently, not all bins ship out at once. So in ProdShpd there
will be an entry for that LotNo but it may only be for 20 of the 28 bins.
With the report I just created I am not getting the other 10 because that
LotNo appears in the ProdShpd table. Is there a way to make some kind of
subtraction so that I can show the remaining # of bins and pounds?

Of course.

However, I don't know what the expression would be because I cannot
see your database, and you have not informed me how ProdShpd stores
the amount shipped. BinsShpd? LbsShpd? Do you want the discrepancy in
bins, or pounds, or both?

To do a subtraction in a query, change your Query so that it has the
LotNo and whatever fields in ProdShpd are needed for the calculation.
In a vacant Field cell type (for example, again, *I cannot see your
tables*) something like

Shortfall: [ProdRcvd].[BinsRcvd] - NZ([ProdShpd].[BinsShpd])

and use a criterion on this field of > 0 to limit the listing to those
cases where the shipment is not complete. Do the same for the pounds;
put the criterion on a second line so that it will report the
discrepancy whether it's in bins or in pounds. If you do this, be sure
that the IS NULL criterion on [ProdShpd].[LotNo] is on a line by
itself - you want those cases where there are more bins received than
shipped, OR more pounds received than shipped, OR nothing was shipped
at all, and in the query grid, you use separate criteria lines to get
OR logic.

John W. Vinson[MVP]