DSum - Criteria

  • Thread starter Thread starter A Hopper
  • Start date Start date
A

A Hopper

I have three tables that represent the various parts of
the process and data is entered by different employees
into these tables.

ProductionTable
Skid Number Quantity Produced
1 100
2 200
3 150
4 300
5 235
6 400

CuttingTable
Skid Number Quantity Produced
1 95
3 145
4 195
5 234

PackagingTable
Skid Number Quantity Produced
1 90
3 140
5 233


The information from all tables, if put together, would be.
SkidNumber QuantityProduced QuantityCut QuantityPackaged
1 100 95 91
2 200
3 150 145 142
4 300 195
5 235 234 233
6 400

On the form I want to show CutSpoils and PackSpoils
for "SkidNumber"(s) that have completed the entire
process. (In the above example skids 1,3 & 5 which are
completed through packaging).

The question marks below inidcate critieria I don't know
how to write.

Dim QtyProduced As Single
Dim QtyCut As Single
Dim QtyPacked As Single
Dim QuantityCutSpoils As Single
Dim QuantityPackSpoils As Single

QtyProduced = (Nz(DSum("(Nz
[QuantityProduced]))", "ProductionTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 100
SkidNumber 3 = 150
SkidNumber 5 = 235
Sum = 485

QtyCut = (Nz(DSum("(Nz
[QuantityCut]))", "CuttingTable", "[SkidNumber]=" & ????)))
Answer needed: SkidNumber 1 = 95
SkidNumber 3 = 145
SkidNumber 5 = 234
Sum = 474

QtyPacked = (Nz(DSum("(Nz
[QuantityPackaged]))", "PackagingTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 91
SkidNumber 3 = 142
SkidNumber 5 = 233
Sum = 466

QuantityCutSpoils = QtyProduced - QtyCut Answer 485 - 474
QuantityPackSpoils = QtyCut - QtyPacked Answer 474 - 466

I will display the answer in textboxes on the form.

Me.CutSpoils = Nz(QuantityCutSpoils) Answer = 11
Me.PackSpoils = Nz(QuantityPackSpoils) Answer = 8

Thanks for your help.

Allan
 
Sounds to me like a query would be a much simpler solution.
If using the query design grid, add all three tables with inner joins on the
skid number fields, then add the following expressions....

CutSpoils:
Sum([productiontable].[quantityproduced]-[cuttingtable].[quantityproduced])
PackSpoils:
Sum([cuttingtable].[quantityproduced]-[packagingtable].[quantityproduced])

I think this will give you your results. There are various ways of
displaying these query results on a form; use the query as the underlying
recordsource for a form or run the query from VBA and access the results.

HTH
Sam

A Hopper said:
I have three tables that represent the various parts of
the process and data is entered by different employees
into these tables.

ProductionTable
Skid Number Quantity Produced
1 100
2 200
3 150
4 300
5 235
6 400

CuttingTable
Skid Number Quantity Produced
1 95
3 145
4 195
5 234

PackagingTable
Skid Number Quantity Produced
1 90
3 140
5 233


The information from all tables, if put together, would be.
SkidNumber QuantityProduced QuantityCut QuantityPackaged
1 100 95 91
2 200
3 150 145 142
4 300 195
5 235 234 233
6 400

On the form I want to show CutSpoils and PackSpoils
for "SkidNumber"(s) that have completed the entire
process. (In the above example skids 1,3 & 5 which are
completed through packaging).

The question marks below inidcate critieria I don't know
how to write.

Dim QtyProduced As Single
Dim QtyCut As Single
Dim QtyPacked As Single
Dim QuantityCutSpoils As Single
Dim QuantityPackSpoils As Single

QtyProduced = (Nz(DSum("(Nz
[QuantityProduced]))", "ProductionTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 100
SkidNumber 3 = 150
SkidNumber 5 = 235
Sum = 485

QtyCut = (Nz(DSum("(Nz
[QuantityCut]))", "CuttingTable", "[SkidNumber]=" & ????)))
Answer needed: SkidNumber 1 = 95
SkidNumber 3 = 145
SkidNumber 5 = 234
Sum = 474

QtyPacked = (Nz(DSum("(Nz
[QuantityPackaged]))", "PackagingTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 91
SkidNumber 3 = 142
SkidNumber 5 = 233
Sum = 466

QuantityCutSpoils = QtyProduced - QtyCut Answer 485 - 474
QuantityPackSpoils = QtyCut - QtyPacked Answer 474 - 466

I will display the answer in textboxes on the form.

Me.CutSpoils = Nz(QuantityCutSpoils) Answer = 11
Me.PackSpoils = Nz(QuantityPackSpoils) Answer = 8

Thanks for your help.

Allan
Click to expand...
 
Sam, I have been working on understanding and using code
and as a result sometimes I overlook alternate (simpler)
ways of achieving my goal. I can see how the query will
work for me, and since I have never run a query from VBA I
would like to learn how to do that. Does the query in SQL
form become part of the code for a form event, text/combo
box event, a command button or any of these?
Thank you for your help
Allan
-----Original Message-----
Sounds to me like a query would be a much simpler solution.
If using the query design grid, add all three tables with inner joins on the
skid number fields, then add the following expressions....

CutSpoils:
Sum([productiontable].[quantityproduced]-[cuttingtable]. [quantityproduced])
PackSpoils:
Sum([cuttingtable].[quantityproduced]-[packagingtable].
Click to expand...
[quantityproduced])

I think this will give you your results. There are various ways of
displaying these query results on a form; use the query as the underlying
recordsource for a form or run the query from VBA and access the results.

HTH
Sam

A Hopper said:
I have three tables that represent the various parts of
the process and data is entered by different employees
into these tables.

ProductionTable
Skid Number Quantity Produced
1 100
2 200
3 150
4 300
5 235
6 400

CuttingTable
Skid Number Quantity Produced
1 95
3 145
4 195
5 234

PackagingTable
Skid Number Quantity Produced
1 90
3 140
5 233


The information from all tables, if put together, would be.
SkidNumber QuantityProduced QuantityCut QuantityPackaged
1 100 95 91
2 200
3 150 145 142
4 300 195
5 235 234 233
6 400

On the form I want to show CutSpoils and PackSpoils
for "SkidNumber"(s) that have completed the entire
process. (In the above example skids 1,3 & 5 which are
completed through packaging).

The question marks below inidcate critieria I don't know
how to write.

Dim QtyProduced As Single
Dim QtyCut As Single
Dim QtyPacked As Single
Dim QuantityCutSpoils As Single
Dim QuantityPackSpoils As Single

QtyProduced = (Nz(DSum("(Nz
[QuantityProduced]))", "ProductionTable", "[SkidNumber] ="
& ????)))
Answer needed: SkidNumber 1 = 100
SkidNumber 3 = 150
SkidNumber 5 = 235
Sum = 485

QtyCut = (Nz(DSum("(Nz
[QuantityCut]))", "CuttingTable", "[SkidNumber]=" & ????)))
Answer needed: SkidNumber 1 = 95
SkidNumber 3 = 145
SkidNumber 5 = 234
Sum = 474

QtyPacked = (Nz(DSum("(Nz
[QuantityPackaged]))", "PackagingTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 91
SkidNumber 3 = 142
SkidNumber 5 = 233
Sum = 466

QuantityCutSpoils = QtyProduced - QtyCut Answer 485 - 474
QuantityPackSpoils = QtyCut - QtyPacked Answer 474 - 466

I will display the answer in textboxes on the form.

Me.CutSpoils = Nz(QuantityCutSpoils) Answer = 11
Me.PackSpoils = Nz(QuantityPackSpoils) Answer = 8

Thanks for your help.

Allan
Click to expand...


.
Click to expand...
 
Allan,

Just to go off on a tangent from your main question... Managing the
data in the way you want is much more difficult than it should be
because your data structure is in an unnormalised design. You have
fallen into what is sometimes known as the "tables as data trap". If
your database is not set in concrete, it would definitely be worth
reviewing your table design, because at the moment the tables you
showed us ;ook more like a spreadsheet than a database. In a
nutshell, the Production, Cutting, and Packaging tables should all be
combined into one table, with an additinal field for ProcessType.

- Steve Schapel, Microsoft Access MVP
 
Steve, sorry for the late reply but I have had some
computer problems. The example I gave is simplified from
the actual tables, because of the information I was trying
to extract from the tables. The tables do contain
additional information. The project starts out in a unit
made up of several smaller units (different items) that
end up as individual units (items) by the time they are
packaged. At the start items can be tracked as a group by
attached number but about half way through the process the
number is removed and the items are tracked by their
position (Upper left, Center, Upper Right, etc)Spoils need
to be tracked by the individual items, their position and
their root cause throughout the process (unless all items
are destroyed early in the process, in which case the
attached number can be used for all the items). Each
package is assigned a unique package number and the
manufacture position, quantity of each item in that
package, the source skid number must be tracked. The
process itself takes place in stages. One part of the
process is finished and the product may be stored for some
time before the next part of the process is started. I
didn't think I would be able to accomplish all of this in
one table so I choose to use seperate tables for the
various parts of the process. Even though I do store parts
of the information more than once I thought that by trying
to combine it in one table I would end up with records
that would have a lot of null enteries. I choose to use
the Job and Skid numbers as the key in each table and to
track each part of the process as a seperate table. This
is my first database so I may be wrong. I would appreciate
any suggestions or help to make the database as efficient
as possible.

Thanks
Allan
 
Allan,
If you create the query, as I mentioned, and the query was called qrySpoils
then you could just set the control source of your text boxes directly using
dlookup without any criteria as only a single record is returned by such a
query.

That is...
=dlookup("cutspoils","qrySpoils")
=dlookup("packspoils","qrySpoils")

If you wish to do the whole thing in VBA then something like...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

'construct SQL
SQL = "SELECT
Sum([productiontable].[quantityproduced]-[cuttingtable].[quantityproduced])
AS CutSpoils, "
SQL = SQL &
"Sum([cuttingtable].[quantityproduced]-[packagingtable].[quantityproduced])
AS PackSpoils "
SQL = SQL & "FROM 'and so on...

'open recordset based on SQL
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

'shove results in the text boxes
Me.txtCutSpoils = rs.Fields("cutspoils")
Me.txtProdSpoils = rs.Fields("packspoils")

rs.Close
db.Close

It's up to you where you place this code, depends if the results are always
needed or if the user can indicate that they should be calculated.
HTH
Sam

A Hopper said:
Sam, I have been working on understanding and using code
and as a result sometimes I overlook alternate (simpler)
ways of achieving my goal. I can see how the query will
work for me, and since I have never run a query from VBA I
would like to learn how to do that. Does the query in SQL
form become part of the code for a form event, text/combo
box event, a command button or any of these?
Thank you for your help
Allan
-----Original Message-----
Sounds to me like a query would be a much simpler solution.
If using the query design grid, add all three tables with inner joins on the
skid number fields, then add the following expressions....

CutSpoils:
Sum([productiontable].[quantityproduced]-[cuttingtable]. [quantityproduced])
PackSpoils:
Sum([cuttingtable].[quantityproduced]-[packagingtable].
Click to expand...
[quantityproduced])

I think this will give you your results. There are various ways of
displaying these query results on a form; use the query as the underlying
recordsource for a form or run the query from VBA and access the results.

HTH
Sam

A Hopper said:
I have three tables that represent the various parts of
the process and data is entered by different employees
into these tables.

ProductionTable
Skid Number Quantity Produced
1 100
2 200
3 150
4 300
5 235
6 400

CuttingTable
Skid Number Quantity Produced
1 95
3 145
4 195
5 234

PackagingTable
Skid Number Quantity Produced
1 90
3 140
5 233


The information from all tables, if put together, would be.
SkidNumber QuantityProduced QuantityCut QuantityPackaged
1 100 95 91
2 200
3 150 145 142
4 300 195
5 235 234 233
6 400

On the form I want to show CutSpoils and PackSpoils
for "SkidNumber"(s) that have completed the entire
process. (In the above example skids 1,3 & 5 which are
completed through packaging).

The question marks below inidcate critieria I don't know
how to write.

Dim QtyProduced As Single
Dim QtyCut As Single
Dim QtyPacked As Single
Dim QuantityCutSpoils As Single
Dim QuantityPackSpoils As Single

QtyProduced = (Nz(DSum("(Nz
[QuantityProduced]))", "ProductionTable", "[SkidNumber] ="
& ????)))
Answer needed: SkidNumber 1 = 100
SkidNumber 3 = 150
SkidNumber 5 = 235
Sum = 485

QtyCut = (Nz(DSum("(Nz
[QuantityCut]))", "CuttingTable", "[SkidNumber]=" & ????)))
Answer needed: SkidNumber 1 = 95
SkidNumber 3 = 145
SkidNumber 5 = 234
Sum = 474

QtyPacked = (Nz(DSum("(Nz
[QuantityPackaged]))", "PackagingTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 91
SkidNumber 3 = 142
SkidNumber 5 = 233
Sum = 466

QuantityCutSpoils = QtyProduced - QtyCut Answer 485 - 474
QuantityPackSpoils = QtyCut - QtyPacked Answer 474 - 466

I will display the answer in textboxes on the form.

Me.CutSpoils = Nz(QuantityCutSpoils) Answer = 11
Me.PackSpoils = Nz(QuantityPackSpoils) Answer = 8

Thanks for your help.

Allan
Click to expand...


.
Click to expand...
Click to expand...
 
Allan,

I regret that I am unable, on the basis of limited information, to
offer any specific or detailed advice as regards your data structure.
And you have certainly taken on a project with some tricky aspects for
your first database! :-)

However, I think this principle would apply... At the stage of the
process where you need to track the individual items/components
separately, then you need to record the processing stages related to
them as individual items. This can not be done in one table, as such.
You need at least a table for the components and a table for the
processing. But each stage in the process should not be a separate
table or a separate field, it should be a separate record. Each item
needs to be identified, apparently, in a number of ways... by its
attached number, by its position, by its package number, etc. At the
time that the items start to be tracked by position or by package,
this identifying data needs to be assigned to them at this stage., but
they obviously also need to retain the prior identifying data as well.
In any case, think records, not fields or tables. The processing
table should be deep and narrow. It might just contain 2 fields...
ItemID and ProcessID. So, every time the processing of an item is
completeed, the event is recorded in this table. The ProcessID
identifies the process, of which I presume there are a fixed finite
list of, and the ItemID identified the Item, which can be an
individual component or a package or whatever, depending on the stage
in the process.

I hope these ramblings might help. I think it is worth the effort to
get things set up "the database way"... even though this might
sometimes not be intuitive or simple, in the end it does make the work
easier.

- Steve Schapel, Microsoft Access MVP
 
Steve, you have definately been helpful. I have been
thinking more fields and tables rather than records. You
are right about the project being a big challenge and it
is complicated for a beginner. Likely someone with
experience looking at what I have done would shake their
head in disbelief. I appreciate very much your effort to
help me understand database design. I wish I could show
someone like yourself the database so they could analyze
it, however it is definately too big to send anywhere and
it is split.

Allan
 
Sam, I will experiment with both ways so I can have a
broader range of tools to work with. The SQL is the one I
have never constructed. I want to learn how to do that
even if I don't use it in this case.
Thank you very much for your help

Allan
-----Original Message-----
Allan,
If you create the query, as I mentioned, and the query was called qrySpoils
then you could just set the control source of your text boxes directly using
dlookup without any criteria as only a single record is returned by such a
query.

That is...
=dlookup("cutspoils","qrySpoils")
=dlookup("packspoils","qrySpoils")

If you wish to do the whole thing in VBA then something like...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

'construct SQL
SQL = "SELECT
Sum([productiontable].[quantityproduced]-[cuttingtable]. [quantityproduced])
AS CutSpoils, "
SQL = SQL &
"Sum([cuttingtable].[quantityproduced]-[packagingtable]. [quantityproduced])
AS PackSpoils "
SQL = SQL & "FROM 'and so on...

'open recordset based on SQL
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

'shove results in the text boxes
Me.txtCutSpoils = rs.Fields("cutspoils")
Me.txtProdSpoils = rs.Fields("packspoils")

rs.Close
db.Close

It's up to you where you place this code, depends if the results are always
needed or if the user can indicate that they should be calculated.
HTH
Sam

A Hopper said:
Sam, I have been working on understanding and using code
and as a result sometimes I overlook alternate (simpler)
ways of achieving my goal. I can see how the query will
work for me, and since I have never run a query from VBA I
would like to learn how to do that. Does the query in SQL
form become part of the code for a form event, text/combo
box event, a command button or any of these?
Thank you for your help
Allan
-----Original Message-----
Sounds to me like a query would be a much simpler solution.
If using the query design grid, add all three tables
Click to expand...
with
inner joins on the
skid number fields, then add the following expressions....

CutSpoils:
Sum([productiontable].[quantityproduced]-
Click to expand...
[cuttingtable].
[quantityproduced])
PackSpoils:
Sum([cuttingtable].[quantityproduced]-[packagingtable]. [quantityproduced])

I think this will give you your results. There are various ways of
displaying these query results on a form; use the query as the underlying
recordsource for a form or run the query from VBA and access the results.

HTH
Sam

I have three tables that represent the various parts of
the process and data is entered by different employees
into these tables.

ProductionTable
Skid Number Quantity Produced
1 100
2 200
3 150
4 300
5 235
6 400

CuttingTable
Skid Number Quantity Produced
1 95
3 145
4 195
5 234

PackagingTable
Skid Number Quantity Produced
1 90
3 140
5 233


The information from all tables, if put together,
Click to expand...
would
be.
SkidNumber QuantityProduced QuantityCut QuantityPackaged
1 100 95 91
2 200
3 150 145 142
4 300 195
5 235 234 233
6 400

On the form I want to show CutSpoils and PackSpoils
for "SkidNumber"(s) that have completed the entire
process. (In the above example skids 1,3 & 5 which are
completed through packaging).

The question marks below inidcate critieria I don't know
how to write.

Dim QtyProduced As Single
Dim QtyCut As Single
Dim QtyPacked As Single
Dim QuantityCutSpoils As Single
Dim QuantityPackSpoils As Single

QtyProduced = (Nz(DSum("(Nz
Click to expand...
[QuantityProduced]))", "ProductionTable", "[SkidNumber]
="
& ????)))
Answer needed: SkidNumber 1 = 100
SkidNumber 3 = 150
SkidNumber 5 = 235
Sum = 485

QtyCut = (Nz(DSum("(Nz
[QuantityCut]))", "CuttingTable", "[SkidNumber]=" & ????)))
Answer needed: SkidNumber 1 = 95
SkidNumber 3 = 145
SkidNumber 5 = 234
Sum = 474

QtyPacked = (Nz(DSum("(Nz
[QuantityPackaged]))", "PackagingTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 91
SkidNumber 3 = 142
SkidNumber 5 = 233
Sum = 466

QuantityCutSpoils = QtyProduced - QtyCut Answer
Click to expand...
485 -
474
QuantityPackSpoils = QtyCut - QtyPacked Answer
Click to expand...
474 -
466
I will display the answer in textboxes on the form.

Me.CutSpoils = Nz(QuantityCutSpoils) Answer = 11
Me.PackSpoils = Nz(QuantityPackSpoils) Answer = 8

Thanks for your help.

Allan





.
Click to expand...
Click to expand...


.
Click to expand...
 
Sam, I will experiment with both ways so I can have a
broader range of tools to work with. The SQL is the one I
have never constructed. I want to learn how to do that
even if I don't use it in this case.
Thank you very much for your help

Allan
-----Original Message-----
Allan,
If you create the query, as I mentioned, and the query was called qrySpoils
then you could just set the control source of your text boxes directly using
dlookup without any criteria as only a single record is returned by such a
query.

That is...
=dlookup("cutspoils","qrySpoils")
=dlookup("packspoils","qrySpoils")

If you wish to do the whole thing in VBA then something like...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

'construct SQL
SQL = "SELECT
Sum([productiontable].[quantityproduced]-[cuttingtable]. [quantityproduced])
AS CutSpoils, "
SQL = SQL &
"Sum([cuttingtable].[quantityproduced]-[packagingtable]. [quantityproduced])
AS PackSpoils "
SQL = SQL & "FROM 'and so on...

'open recordset based on SQL
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

'shove results in the text boxes
Me.txtCutSpoils = rs.Fields("cutspoils")
Me.txtProdSpoils = rs.Fields("packspoils")

rs.Close
db.Close

It's up to you where you place this code, depends if the results are always
needed or if the user can indicate that they should be calculated.
HTH
Sam

A Hopper said:
Sam, I have been working on understanding and using code
and as a result sometimes I overlook alternate (simpler)
ways of achieving my goal. I can see how the query will
work for me, and since I have never run a query from VBA I
would like to learn how to do that. Does the query in SQL
form become part of the code for a form event, text/combo
box event, a command button or any of these?
Thank you for your help
Allan
-----Original Message-----
Sounds to me like a query would be a much simpler solution.
If using the query design grid, add all three tables
Click to expand...
with
inner joins on the
skid number fields, then add the following expressions....

CutSpoils:
Sum([productiontable].[quantityproduced]-
Click to expand...
[cuttingtable].
[quantityproduced])
PackSpoils:
Sum([cuttingtable].[quantityproduced]-[packagingtable]. [quantityproduced])

I think this will give you your results. There are various ways of
displaying these query results on a form; use the query as the underlying
recordsource for a form or run the query from VBA and access the results.

HTH
Sam

I have three tables that represent the various parts of
the process and data is entered by different employees
into these tables.

ProductionTable
Skid Number Quantity Produced
1 100
2 200
3 150
4 300
5 235
6 400

CuttingTable
Skid Number Quantity Produced
1 95
3 145
4 195
5 234

PackagingTable
Skid Number Quantity Produced
1 90
3 140
5 233


The information from all tables, if put together,
Click to expand...
would
be.
SkidNumber QuantityProduced QuantityCut QuantityPackaged
1 100 95 91
2 200
3 150 145 142
4 300 195
5 235 234 233
6 400

On the form I want to show CutSpoils and PackSpoils
for "SkidNumber"(s) that have completed the entire
process. (In the above example skids 1,3 & 5 which are
completed through packaging).

The question marks below inidcate critieria I don't know
how to write.

Dim QtyProduced As Single
Dim QtyCut As Single
Dim QtyPacked As Single
Dim QuantityCutSpoils As Single
Dim QuantityPackSpoils As Single

QtyProduced = (Nz(DSum("(Nz
Click to expand...
[QuantityProduced]))", "ProductionTable", "[SkidNumber]
="
& ????)))
Answer needed: SkidNumber 1 = 100
SkidNumber 3 = 150
SkidNumber 5 = 235
Sum = 485

QtyCut = (Nz(DSum("(Nz
[QuantityCut]))", "CuttingTable", "[SkidNumber]=" & ????)))
Answer needed: SkidNumber 1 = 95
SkidNumber 3 = 145
SkidNumber 5 = 234
Sum = 474

QtyPacked = (Nz(DSum("(Nz
[QuantityPackaged]))", "PackagingTable", "[SkidNumber]="
& ????)))
Answer needed: SkidNumber 1 = 91
SkidNumber 3 = 142
SkidNumber 5 = 233
Sum = 466

QuantityCutSpoils = QtyProduced - QtyCut Answer
Click to expand...
485 -
474
QuantityPackSpoils = QtyCut - QtyPacked Answer
Click to expand...
474 -
466
I will display the answer in textboxes on the form.

Me.CutSpoils = Nz(QuantityCutSpoils) Answer = 11
Me.PackSpoils = Nz(QuantityPackSpoils) Answer = 8

Thanks for your help.

Allan





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top