On Nov 2, 2:00 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 2 Nov, 00:03, goo...@voyagercomponents.com wrote:
>
>
>
>
>
> > Or, at least it seems complex to me.
> > I need help on my "Dead Stock" report. It seemed pretty simple, but
> > I'm discovering it is MUCH more complex than I had anticipated.
> > I'm not quite sure which info you would and wouldn't want, so I'm just
> > going to lay it ALL out.
>
> > We need to list all Stocklines which are older than 01/01/2004, and
> > under Part Numbers which have neither been Quoted nor Sold after
> > 01/01/2000.
>
> > This involves these Tables.Fields:
>
> > Part.PartId
> > Part.Number
>
> > Stockline.PartId
> > Stockline.StockId
> > Stockline.DateEntered
> > Stockline.Location
> > Stockline.Quantity
> > Stockline.Manufacturer
> > Stockline.DateCode
> > Stockline.ConsignmentCode
>
> > Quote.QuoteId
> > Quote.Date
>
> > QuoteItem.QuoteId
> > QuoteItem.PartId
>
> > SalesOrder.SalesOrderId
> > SalesOrder.Date
>
> > SOItem.SalesOrderId
> > SOItem.PartId
>
> > This is what I have so far:
>
> > SELECT Stockline.PartId, Stockline.StockId, Stockline.PartId,
> > Stockline.DateEntered, Stockline.Location, Stockline.Quantity,
> > Stockline.Manufacturer, Stockline.DateCode, Stockline.ConsignmentCode
> > FROM Stockline
> > LEFT JOIN SOItem
> > ON Stockline.PartId=SOItem.PartID
> > LEFT JOIN QuoteItem
> > ON Stockline.PartId=QuoteItem.PartID
> > WHERE SOItem.PartID is NULL
> > AND QuoteItem.PartID is NULL
> > AND Stockline.DateEntered<2004-01-01
>
> > This completes nicley, without any errors, but I can't figure out how
> > to finish it off.
> > I need to include the Part.Number which coincides to each
> > Stockline.PartId, and limit it to items where the SalesOrder.Date and
> > Quote.Date are either <2000-01-01 or NULL
>
> > PLEASE HELP.
> > -Mo
>
> > Additionally, I want to make sure that the LEFT JOIN's are achieving
> > thier intended purposes.
> > Would the second LEFT JOIN join to the results of the first JEFT JOIN,
> > or the Stockline table?
>
> What would be really helpful is some phpMyAdmin exports of the table
> schemas and a few items of data in the associated INSERT statement
> accompanied by the expected results from that data.- Hide quoted text -
>
> - Show quoted text -

Forgive my nievity, but I never looked into common terminology.
By 'table schemas', do you mean you want all the fields, and a few
records for each table?
And, please clarify what you are loking for in relation to the INSERT
statement. I don't have one in my query, so I'm not sure what you
need.
-Mo