Hello Evi,
This is another question on the DB which you gave me some advice on earlier.
The tables are fairly well "normalized" as best I can tell, arranged as
follows:
PrimaryParts (table)
PrimaryPartIndex (primary key)
PrimaryPartDesc (text describing this part type)
(Note: this short table describes 11 basic types of parts)
PrimaryPartArray (table)
PartArrayIndex (primary key)
PrimPartID (foreign key to PrimaryPartIndex in PrimaryParts table)
CompID (fk to OrderedItems table, not shown here)
DrumSizeID (fk to short DrumSize table, listing of possible variants, not
shown)
BeadSizeID (fk to short BeadSize table, listing of possible variants, not
shown)
DrumWidthID (fk to short DrumWidth table, listing of possible variants, not
shown)
QtyPerComp (quantity of this part per order)
(Note: this large table identifies a set of Part Types and their parameters
DrumSize, BeadSize and Drum Width that are associated with one ComponentID.
When one "component" is ordered on a purchase order it results in a
particular set of PrimaryParts. A "component" is a short-hand description of
a bill of materials - this table breaks that out into discrete "primary"
parts)
DrumSpecs (table)
DrumSpecIndex (PK)
SpecPartSetTypeID (fk to short PartSetType table, not shown)
SpecDrumSizeID (fk to short DrumSize table, listing of possible variants, not
shown)
SpecBeadSizeID (fk to short BeadSize table, listing of possible variants, not
shown)
SpecDrumWidthID (fk to short DrumWidth table, listing of possible variants,
not shown)
SpecDesc (text describing this spec)
(Note: this relatively short table describes the header information of my
list of specifications)
DrumSpecArray (table)
SpecArrayIndex (PK)
SpecID (fk to DrumSpecIndex in DrumSpec table)
SpecPrimPartID (fk to PrimaryPartIndex in PrimaryParts table)
SpecDrumSizeID (fk to short DrumSize table, listing of possible variants, not
shown)
SpecBeadSizeID (fk to short BeadSize table, listing of possible variants, not
shown)
SpecDrumWidthID (fk to short DrumWidth table, listing of possible variants,
not shown)
SpecPartQty (quantity of a given part needed to fulfill this specification)
(Note: this relatively large table describes the details of individual parts
and quantities that make up a particular specification. This is "the thing
that motivates me to put parts together" as you described)
My existing crosstab query generates the list by Parts described by their
PrimPartID and the 3 variants of DrumSize, BeadSize, DrumWidth, and the
quantity and delivery date of each of these parts. It is based on the
PrimaryPartArray table as joined to the DrumSpecArray table at these 3 fields.
My query describes the timetable that all the various unique parts will be
delivered.
It also identifies the SpecID (the particular specification) applicable to
each part ordered.
Here's the SQL of that query:
TRANSFORM Sum(PrimaryPartArray.QtyPerComp) AS SumOfQtyPerComp
SELECT [Purchase orders].DeliverLocnID, DrumSpecArray.SpecID, [Ordered Items].
[Ship date (est)]
FROM [Purchase orders] INNER JOIN (DrumSpecArray INNER JOIN (PrimaryPartArray
INNER JOIN [Ordered Items] ON PrimaryPartArray.CompID = [Ordered Items].
Comp_index) ON (DrumSpecArray.SpecPrimPartID = PrimaryPartArray.PrimPartID)
AND (DrumSpecArray.SpecDrumSizeID = PrimaryPartArray.DrumSizeID) AND
(DrumSpecArray.SpecBeadSizeID = PrimaryPartArray.BeadSizeID) AND
(DrumSpecArray.SpecDrumWidthID = PrimaryPartArray.DrumWidthID)) ON [Purchase
orders].[PO number] = [Ordered Items].[PO Number]
GROUP BY [Purchase orders].DeliverLocnID, DrumSpecArray.SpecID, [Ordered
Items].[Ship date (est)]
ORDER BY [Purchase orders].DeliverLocnID, DrumSpecArray.SpecID, [Ordered
Items].[Ship date (est)]
PIVOT "PD" & PrimaryPartArray.PrimPartID In ("PD1","PD2","PD3","PD4","PD5",
"PD6","PD7","PD8","PD9","PD10","PD11");
So I know what parts are coming, when they will arrive, and what
specification pertains to each part.
I need a way to report on when I will accumulate enough parts to generate
each specification.
I think I need to generate a running sum of all parts by date, and on each of
those dates, calculate how many (if any) of the associated specification can
I assemble.
I hope that is not too much detail - thanks again!
-- Doug
Is this a crosstab query which you are referring to, Doug? If not, I have a
strong suspicion that you may have a database design problem. Give an
example of the data in say P1 and S1.
I'm trying to peer through the maze of jargon but what I'd expect to see in
your table design is
TblParts
PartID (primary Key, perhaps an Autonumber)
Part
TblSet (or the thing that motivates you to add parts together, perhaps a
customer's order
SetID (PK)
Eg SetDate
TblSetPart
SPID (PK)
PartID (Foreign Key field linked from TblParts
SpecID (FK linked from TblSet
PartNums (number of that part needed for that 'spec')
Evi
I am trying to create a report on sets of parts.
Say I have 5 different parts, called A B C D E.
[quoted text clipped - 20 lines]
