Mo wrote:
> 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

If you g into phpMyAdmin, as I mentioned in my post, there is an Export tab.

That allows you to export the structure and data. The structure comes in the
form of CREATE TABLE statements and the data comes in the form of INSERT
statements. That allows me (or others) to quickly setup tables just like
your ones and have the sample data in there too.