Newbie query question

I need to include data from a table which is not directly related to
the "hub" table, and am kinda stuck.
I doubted it would work (tried anyhow), but this is the code from my
attempt:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.Consig nmentCode)
LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
AND Reserve.InvoiceId=Invoice.InvoiceId
You can see why it did didn't work, and hopefully, what I am trying to
do.
I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
relate to "Stockline".
If I try to base all my relationships on "Resreve", then I end up with
ths same problem for "Part".
What's my next step?
-Mo
On Feb 22, 2:41Xpm, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> Without knowing the table structure and their relationship, I would suggest
> try the following query or something close:
> SELECT
> X X X X ConsignmentCodes.CodeDesc,
> X X X X Stockline.ConsignmentCode,
> X X X X Reserve.SalesOrderId,
> X X X X Invoice.ShipDate,
> X X X X Part.Number,
> X X X X Reserve.Shipped as Qty,
> X X X X SOItem.UnitAmount,
> X X X X (Reserve.Shipped*SOItem.UnitAmount) as Total,
> X X X X 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM X XStockline LEFT OUTER JOIN Part ON (Stockline.PartId=Part.PartId)
> X X X X LEFT OUTER JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
> X X X X LEFT OUTER JOIN ConsignmentCodes ON
> X X X X X X X X (Stockline.ConsignmentCode=ConsignmentCodes.Consig nmentCode)
> X X X X LEFT OUTER JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
> X X X X LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=Invoice..SalesOrderId)
> X X X X AND (Reserve.InvoiceId=Invoice.InvoiceId)
> Lucas
>
> "Mehile.Orl...@.gmail.com" wrote:
>
>
>
> - Show quoted text -
Thanks, I think that gets me going again.
If I run into anything else, I'll repost.
Mo
sql

Newbie query question

I need to include data from a table which is not directly related to
the "hub" table, and am kinda stuck.
I doubted it would work (tried anyhow), but this is the code from my
attempt:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
AND Reserve.InvoiceId=Invoice.InvoiceId
You can see why it did didn't work, and hopefully, what I am trying to
do.
I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
relate to "Stockline".
If I try to base all my relationships on "Resreve", then I end up with
ths same problem for "Part".
What's my next step?
-MoWithout knowing the table structure and their relationship, I would suggest
try the following query or something close:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline LEFT OUTER JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT OUTER JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT OUTER JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
LEFT OUTER JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=Invoice.SalesOrderId)
AND (Reserve.InvoiceId=Invoice.InvoiceId)
Lucas
"Mehile.Orloff@.gmail.com" wrote:
> I need to include data from a table which is not directly related to
> the "hub" table, and am kinda stuck.
> I doubted it would work (tried anyhow), but this is the code from my
> attempt:
> SELECT
> ConsignmentCodes.CodeDesc,
> Stockline.ConsignmentCode,
> Reserve.SalesOrderId,
> Invoice.ShipDate,
> Part.Number,
> Reserve.Shipped as Qty,
> SOItem.UnitAmount,
> (Reserve.Shipped*SOItem.UnitAmount) as Total,
> 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
> LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
> LEFT JOIN ConsignmentCodes ON
> (Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
> LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
> WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
> AND Reserve.InvoiceId=Invoice.InvoiceId
> You can see why it did didn't work, and hopefully, what I am trying to
> do.
> I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
> relate to "Stockline".
> If I try to base all my relationships on "Resreve", then I end up with
> ths same problem for "Part".
>
> What's my next step?
> -Mo
>|||On Feb 22, 2:41=A0pm, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> Without knowing the table structure and their relationship, I would sugges=t
> try the following query or something close:
> SELECT
> =A0 =A0 =A0 =A0 ConsignmentCodes.CodeDesc,
> =A0 =A0 =A0 =A0 Stockline.ConsignmentCode,
> =A0 =A0 =A0 =A0 Reserve.SalesOrderId,
> =A0 =A0 =A0 =A0 Invoice.ShipDate,
> =A0 =A0 =A0 =A0 Part.Number,
> =A0 =A0 =A0 =A0 Reserve.Shipped as Qty,
> =A0 =A0 =A0 =A0 SOItem.UnitAmount,
> =A0 =A0 =A0 =A0 (Reserve.Shipped*SOItem.UnitAmount) as Total,
> =A0 =A0 =A0 =A0 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM =A0 =A0Stockline LEFT OUTER JOIN Part ON (Stockline.PartId=3DPart.Par=tId)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN Reserve ON (Stockline.StockId=3DReserve.St=ockId)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN ConsignmentCodes ON
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 (Stockline.ConsignmentCode=3DConsignmentCo=des.ConsignmentCode)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN SOItem ON (Stockline.PartId=3DSOItem.PartI=d)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=3DInvoice=.SalesOrderId)
> =A0 =A0 =A0 =A0 AND (Reserve.InvoiceId=3DInvoice.InvoiceId)
> Lucas
>
> "Mehile.Orl...@.gmail.com" wrote:
> > I need to include data from a table which is not directly related to
> > the "hub" table, and am kinda stuck.
> > I doubted it would work (tried anyhow), but this is the code from my
> > attempt:
> > SELECT
> > ConsignmentCodes.CodeDesc,
> > Stockline.ConsignmentCode,
> > Reserve.SalesOrderId,
> > Invoice.ShipDate,
> > Part.Number,
> > Reserve.Shipped as Qty,
> > SOItem.UnitAmount,
> > (Reserve.Shipped*SOItem.UnitAmount) as Total,
> > 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> > FROM Stockline JOIN Part ON (Stockline.PartId=3DPart.PartId)
> > LEFT JOIN Reserve ON (Stockline.StockId=3DReserve.StockId)
> > LEFT JOIN ConsignmentCodes ON
> > (Stockline.ConsignmentCode=3DConsignmentCodes.ConsignmentCode)
> > LEFT JOIN SOItem ON (Stockline.PartId=3DSOItem.PartId)
> > WHERE Reserve.SalesOrderId=3DInvoice.SalesOrderId
> > AND Reserve.InvoiceId=3DInvoice.InvoiceId
> > You can see why it did didn't work, and hopefully, what I am trying to
> > do.
> > I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
> > relate to "Stockline".
> > If I try to base all my relationships on "Resreve", then I end up with
> > ths same problem for "Part".
> > What's my next step?
> > -Mo- Hide quoted text -
> - Show quoted text -
Thanks, I think that gets me going again.
If I run into anything else, I'll repost.
Mo

Newbie pie chart report problem

Hi, I've just started using Pie Charts and I'm stuck on a value problem (I think!). My category will be "Connection Status", and the values can be: success:human; success:machine; Connected; Null.

What I want to do is merge the "success:human" and "connected" values into one slice of pie. Is this possible?

Thanks, Dan

Sorry, I was confused, just needed to change the SQL to include both values.

Newbie needs help with homework due today

Hi Folks,
Please be gentle as this newbie is in a beginners SQL class and is stuck on the homework assignment. I would be very grateful for any help I can get.

System: MS Access2000

Problem: To write an SQL statement that will write the results of a UNION query to a new table in my database.

Where am I at? I have written the UNION query & it does return the results I expect. When I modify the query (by adding INTO Newtable) to write the result set to the new table, I get an error, "An action query cannot be used as a row source"

Code I'm using:
SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
INTO Newtable
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Exempt'
UNION
SELECT Employees_TBL.FirstName, Employees_TBL.LastName, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode AND JobTitle_TBL.Status = 'Non-exempt';

Additional Info: If I just do one part of the compound query, I can write records to Newtable with no problem.

HEre is what my instructor says on the matter:
I've had some questions about how to integrate the UNION query with the SELECT...INTO statement. So here's some syntax information. I hope it helps.

In simple terms, the syntax for the SELECT ... INTO is

SELECT fieldlist INTO newtablename FROM recordsource

Where fieldlist has the list of new field names for your table. You will need to make sure that the recordsource returns the same number of fields.

newtablename is the name you want the new table to have

recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

Example:

SELECT ItemName, LunchPrice INTO LunchMenu
FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)
:( :(

I seem to be having a problem with syntax because the query works without the INTO part and the writing of records works if I don't try to use the UNION SELECT statement.

Any ideas?Just look more closely at the syntax definition/example:

recordsource is a valid table or query that returns a valid recordset to match fieldlist. If you are using a query, then you would enclose the query in parentheses. The recordsource could be as complex as needed to get you the records you want to add. It could even be a UNION query!

Example:

SELECT ItemName, LunchPrice INTO LunchMenu
FROM (SELECT EntreeName, ItemCost*2.5 FROM RecipeList WHERE LunchFlag=1)

So, you could try this:

SELECT * INTO Newtable
FROM (
SELECT Employees_TBL.FirstName, Employees_TBL.LastName
, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
AND JobTitle_TBL.Status = 'Exempt'
UNION
SELECT Employees_TBL.FirstName, Employees_TBL.LastName
, JobTitle_TBL.JobTitle, Employees_TBL.Salary
FROM Employees_TBL, JobTitle_TBL
WHERE Employees_TBL.JobTitleCode = JobTitle_TBL.JobTitleCode
AND JobTitle_TBL.Status = 'Non-exempt');

:cool:
DISCLAIMER: This is just a suggestion due to the fact I know very little MS Access! :o