Sum Selected Records

[RCDog]

Hello,

I'm having a problem creating an inventory sheet as an Access Report. We
assign crates with Lot #'s - each Lot # can have multiple crates. On any
given day, half, a third or all of the crates from a Lot # can be used. I
need to be able to generate an inventory report that negative sums the Lot's
for which some crates were used and doesn't sum some of the Lot's for which
no crates were used. Of course all of this needs to be generated on the same
sheet, separated by another factor - species - and then sorted by Lot #.
Also, this report needs to be generated daily. Can you help?

 

[John Vinson]

Hello,

I'm having a problem creating an inventory sheet as an Access Report. We
assign crates with Lot #'s - each Lot # can have multiple crates. On any
given day, half, a third or all of the crates from a Lot # can be used. I
need to be able to generate an inventory report that negative sums the Lot's
for which some crates were used and doesn't sum some of the Lot's for which
no crates were used. Of course all of this needs to be generated on the same
sheet, separated by another factor - species - and then sorted by Lot #.
Also, this report needs to be generated daily. Can you help?

Probably, but not without having some understanding of your table
structure. What tables do you have, and what fields are in which
tables?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[RCDog]

The tables in use are:

'RawMaterialsMain', which contains the fields: "Lot #",
"Species","Product","Cases Received" and "Weight Received"

'ProductionMain' contains the fields: "Lot #" (linked to
RawMaterialsMain.Lot #), "Cases To Production" and "Weight To Production".

The goal is an inventory of raw material, which would include all material
that had not been sent to production yet (i.e., [Cases Received]-[Cases To
Production]). The inventory is sorted by Species, then Lot # and the Cases &
Weight should be calculated automatically as records are entered.

I can't get records with the same Lot # to combine.

I hope this is clear, thanks for your help!

 

[John Vinson]

The tables in use are:

'RawMaterialsMain', which contains the fields: "Lot #",
"Species","Product","Cases Received" and "Weight Received"

'ProductionMain' contains the fields: "Lot #" (linked to
RawMaterialsMain.Lot #), "Cases To Production" and "Weight To Production".

The goal is an inventory of raw material, which would include all material
that had not been sent to production yet (i.e., [Cases Received]-[Cases To
Production]). The inventory is sorted by Species, then Lot # and the Cases &
Weight should be calculated automatically as records are entered.

I can't get records with the same Lot # to combine.

The # character is a Date/Time delimiter and it's probably unwise to
use it in a fieldname. If you do, you must ALWAYS enclose the
fieldname in square brackets [Lot #].

You say "you can't get records... to combine". What is the datatype of
[Lot #]? Are the datatype and size identical in the two tables? What
are the Primary Keys of the tables? You say "calculated automatically"
- tables should very rarely contain any calculated fields; just what
do you intend to happen, and how should the calculation be done?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[RCDog]

Lot # is a 4 digit number assigned to crates as they enter the warehouse.
The number is the same in both tables. Lot # is the primary key in the
RawMaterialsMain Table, but is not in the ProductionMain Table. In the
ProductionMain table, Work Order is the primary key (one Work Order can
contain many Lot #'s).

Ultimately, I want to generate a Report with current Raw Materials
Inventory. To get the total Raw Material remaining by Lot #, I need to
subtract [Cases To Production] from [Cases Received]. There will only be one
entry for [Cases Received] on any given Lot #, but there can be multiple
instances of [Cases To Production] for a single Lot #.

What is showing on my report now is:

Lot # Raw Cases Raw Weight
4444 5 5000
7777 10 10000
7777 15 15000

What is showing for Lot # 7777 is the remaining Raw Material after each time
cases were taken from Production. What should show is:

Lot # Raw Cases Raw Weight
4444 5 5000


Because Lot # 7777 actually has no raw material remaining - it has to be
cumulative.

The tables in use are:

'RawMaterialsMain', which contains the fields: "Lot #",
"Species","Product","Cases Received" and "Weight Received"

'ProductionMain' contains the fields: "Lot #" (linked to
RawMaterialsMain.Lot #), "Cases To Production" and "Weight To Production".

The goal is an inventory of raw material, which would include all material
that had not been sent to production yet (i.e., [Cases Received]-[Cases To
Production]). The inventory is sorted by Species, then Lot # and the Cases &
Weight should be calculated automatically as records are entered.

I can't get records with the same Lot # to combine.

The # character is a Date/Time delimiter and it's probably unwise to
use it in a fieldname. If you do, you must ALWAYS enclose the
fieldname in square brackets [Lot #].

You say "you can't get records... to combine". What is the datatype of
[Lot #]? Are the datatype and size identical in the two tables? What
are the Primary Keys of the tables? You say "calculated automatically"
- tables should very rarely contain any calculated fields; just what
do you intend to happen, and how should the calculation be done?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Lot # is a 4 digit number assigned to crates as they enter the warehouse.
The number is the same in both tables. Lot # is the primary key in the
RawMaterialsMain Table, but is not in the ProductionMain Table. In the
ProductionMain table, Work Order is the primary key (one Work Order can
contain many Lot #'s).

Ultimately, I want to generate a Report with current Raw Materials
Inventory. To get the total Raw Material remaining by Lot #, I need to
subtract [Cases To Production] from [Cases Received]. There will only be one
entry for [Cases Received] on any given Lot #, but there can be multiple
instances of [Cases To Production] for a single Lot #.

What is showing on my report now is:

Lot # Raw Cases Raw Weight
4444 5 5000
7777 10 10000
7777 15 15000

What is showing for Lot # 7777 is the remaining Raw Material after each time
cases were taken from Production. What should show is:

Lot # Raw Cases Raw Weight
4444 5 5000

Did you ever get an answer to this question, RCDog? I think you'll
need a Totals query calculating [RawMaterialMain].[Raw Weight] -
Sum([ProductionMain].[Raw Weight])

but I'm not sure I understand correctly how Cases and Weight are
related.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps