D
DougW via AccessMonster.com
Hi - I have a database to track deliveries of ordered parts.
Due to our system, parts are ordered as "components"
which are just a standardized collection of parts.
I have one table called OrderedItems which has records
of all the "components" that have been ordered, along
with their estimated shipping date.
I have a second table called PrimaryPartsArray, which lists
the various parts (and their quantities) included in a given
component. Between these first two tables I can track
and report on the quantities and ship dates of the PrimaryParts.
Each Part, in addition to a type number (PartID) also has
3 variables associated with it, DrumSize, BeadSize, and DrumWidth.
A Part is uniquely identified by the combination of PartId, DrumSize,
BeadSize, and DrumWidth.
After delivery these parts are assembled into assemblies
(which are termed "drums" in my application).
I have a third source (a query) called SelectedDrumSpec,
which contains the "recipe" or spec for a particular Drum (assembly).
For a particular Spec there are a number of records identifying the
PartID, DrumSize, BeadSize, and DrumWidth of each part, along with
the number of each part required to assembly one Drum assembly.
The SelectedDrumSpec query is driven by a form where I select the
drum spec (recipe) of interest.
There are not always the same number of Part types per Drum assembly
for all specs. Some specs have only two distinct part types.
Some specs have as many as 4 distinct part types.
I am trying to devise a way to report on how many Drum assemblies
of a given spec, will be available by date. I know how many parts of
each type are available by date. I know how many parts of each type
are required to make one Drum assembly. I am now trying to get my
query to do the math and list how many potential Drums could
potentially be assembled on a given date. Ideally I want to produce
a report showing the total projected Drums available on a monthly
basis.
Currently I have a query that joins the fields for PartID, DrumSize,
BeadSize, and DrumWidth, between the PrimaryPartsArray table
and the SelectedDrumSpec query. I have it set up as a cross-tab
query. It works insofar as it correctly produces rows listing the number
of applicable parts available by date (the sum of QtyPerComp) and
also lists the number of each part required per Drum assembly
(SpecPartQty). But I'm having trouble coming up with a way to
calculate from this, how many Drum assemblies that set of available
parts can make.
I hope I have given sufficient information to explain my question.
I would appreciate any suggestions.
My SQL for the crosstab query is attached below.
PARAMETERS [Forms]![ReportDriver]![SelSpec] Long;
TRANSFORM Sum(PrimaryPartArray.QtyPerComp) AS SumOfQtyPerComp
SELECT [Ordered Items].[Ship date (est)], SelectedDrumSpec.SpecPartQty
FROM (SelectedDrumSpec INNER JOIN PrimaryPartArray ON (SelectedDrumSpec.
SpecPrimPartID = PrimaryPartArray.PrimPartID) AND (SelectedDrumSpec.
SpecDrumSizeID = PrimaryPartArray.DrumSizeID) AND (SelectedDrumSpec.
SpecBeadSizeID = PrimaryPartArray.BeadSizeID) AND (SelectedDrumSpec.
SpecDrumWidthID = PrimaryPartArray.DrumWidthID)) INNER JOIN [Ordered Items]
ON PrimaryPartArray.CompID = [Ordered Items].Comp_index
WHERE (((SelectedDrumSpec.SpecID)=[Forms]![ReportDriver]![SelSpec]))
GROUP BY SelectedDrumSpec.SpecID, [Ordered Items].[Ship date (est)],
SelectedDrumSpec.SpecPartQty, SelectedDrumSpec.SpecID
ORDER BY [Ordered Items].[Ship date (est)]
PIVOT PrimaryPartArray.PrimPartID;
Thanks in advance
-Doug
Due to our system, parts are ordered as "components"
which are just a standardized collection of parts.
I have one table called OrderedItems which has records
of all the "components" that have been ordered, along
with their estimated shipping date.
I have a second table called PrimaryPartsArray, which lists
the various parts (and their quantities) included in a given
component. Between these first two tables I can track
and report on the quantities and ship dates of the PrimaryParts.
Each Part, in addition to a type number (PartID) also has
3 variables associated with it, DrumSize, BeadSize, and DrumWidth.
A Part is uniquely identified by the combination of PartId, DrumSize,
BeadSize, and DrumWidth.
After delivery these parts are assembled into assemblies
(which are termed "drums" in my application).
I have a third source (a query) called SelectedDrumSpec,
which contains the "recipe" or spec for a particular Drum (assembly).
For a particular Spec there are a number of records identifying the
PartID, DrumSize, BeadSize, and DrumWidth of each part, along with
the number of each part required to assembly one Drum assembly.
The SelectedDrumSpec query is driven by a form where I select the
drum spec (recipe) of interest.
There are not always the same number of Part types per Drum assembly
for all specs. Some specs have only two distinct part types.
Some specs have as many as 4 distinct part types.
I am trying to devise a way to report on how many Drum assemblies
of a given spec, will be available by date. I know how many parts of
each type are available by date. I know how many parts of each type
are required to make one Drum assembly. I am now trying to get my
query to do the math and list how many potential Drums could
potentially be assembled on a given date. Ideally I want to produce
a report showing the total projected Drums available on a monthly
basis.
Currently I have a query that joins the fields for PartID, DrumSize,
BeadSize, and DrumWidth, between the PrimaryPartsArray table
and the SelectedDrumSpec query. I have it set up as a cross-tab
query. It works insofar as it correctly produces rows listing the number
of applicable parts available by date (the sum of QtyPerComp) and
also lists the number of each part required per Drum assembly
(SpecPartQty). But I'm having trouble coming up with a way to
calculate from this, how many Drum assemblies that set of available
parts can make.
I hope I have given sufficient information to explain my question.
I would appreciate any suggestions.
My SQL for the crosstab query is attached below.
PARAMETERS [Forms]![ReportDriver]![SelSpec] Long;
TRANSFORM Sum(PrimaryPartArray.QtyPerComp) AS SumOfQtyPerComp
SELECT [Ordered Items].[Ship date (est)], SelectedDrumSpec.SpecPartQty
FROM (SelectedDrumSpec INNER JOIN PrimaryPartArray ON (SelectedDrumSpec.
SpecPrimPartID = PrimaryPartArray.PrimPartID) AND (SelectedDrumSpec.
SpecDrumSizeID = PrimaryPartArray.DrumSizeID) AND (SelectedDrumSpec.
SpecBeadSizeID = PrimaryPartArray.BeadSizeID) AND (SelectedDrumSpec.
SpecDrumWidthID = PrimaryPartArray.DrumWidthID)) INNER JOIN [Ordered Items]
ON PrimaryPartArray.CompID = [Ordered Items].Comp_index
WHERE (((SelectedDrumSpec.SpecID)=[Forms]![ReportDriver]![SelSpec]))
GROUP BY SelectedDrumSpec.SpecID, [Ordered Items].[Ship date (est)],
SelectedDrumSpec.SpecPartQty, SelectedDrumSpec.SpecID
ORDER BY [Ordered Items].[Ship date (est)]
PIVOT PrimaryPartArray.PrimPartID;
Thanks in advance
-Doug