Union Query - error message received

[LELOA]

SELECT PriceInUSD, CelPartNumber, as [relationship]
FROM q2_03 CELESTICA MASTER RFQ DATA BASE FILE
UNION SELECT PriceInUSD, CelPartNumber,
FROM q4_2003 RFQ
ORDER BY CelPartNumber;


error message
THE SELECT STATMENT INCLUDES A RESERVE WORD OR AN ARGUMENT NAME THAT IS MISSED SPELLED OR MISSING, OR THE PUNCTUATION IS INCORRECT

Help can anyone see whats wrong

 

[lespaul]

first off you have an extra comma

try this:

SELECT PriceInUSD, CelPartNumber as [relationshiptype]
FROM q2_03 CELESTICA MASTER RFQ DATA BASE FILE
UNION SELECT PriceInUSD, CelPartNumber,
FROM q4_2003 RFQ
ORDER BY CelPartNumber;

I just answered another thread just like this!! I'm pretty sure that [RELATIONSHIP] is a reserved word, try changing it like above.

Leslie

 

[KyleS]

Hi,

It's the comma between "CertNumber" and "as [relationship]"

Oh, and the table names need to be in Brackets, since they have spaces...

Your SQL should look like this:

SELECT PriceInUSD, CelPartNumber as [relationship]
FROM [q2_03 CELESTICA MASTER RFQ DATA BASE FILE]
UNION SELECT PriceInUSD, CelPartNumber as [relationship]
FROM [q4_2003 RFQ]
ORDER BY [relationship];

Kyle

 

[lespaul]

actually you don't want the AS [RELATIONSHIP] in the second SELECT query, only in the first.

And Kyle's right, the table names should be bracketed as well.

Leslie

 

[KyleS]

Nothing like both of us being half right : )

And I'm pretty sure [Relationship] is reserved too... so do as Leslie says and change to [RelationshipType]...

And leave the last line as

"ORDER BY CelPartNumber;"




Kyle

 

[LELOA]

Hi thanks

now I am getting an error SYNTAX ERROR IN FROM CLAUSE

lEE

 

[LELOA]

I am now getting a Parameter request. I thought I would see a spreadsheet showing all the records from both tables.

Am I confused.

 

[KyleS]

Did you leave the comma after CelPartNumber in the UNION SELECT line?

Kyle

 

[KyleS]

Well the parameter request is due to the "[]" that are around the word "requesttype". They tell a query to ask for a parameter if an exact match isn't found in the dB

Use this SQL and everything should be fine (doing too many jobs at once, sorry)

SELECT PriceInUSD, CelPartNumber as RelationshipType
FROM [q2_03 CELESTICA MASTER RFQ DATA BASE FILE]
UNION SELECT PriceInUSD, CelPartNumber
FROM [q4_2003 RFQ]
ORDER BY CelPartNumber;

Kyle

 

[lespaul]

If we get enough 1/2 right answers, you may eventually get a full solution!



Leslie

 

[LELOA]

Hi

Here is the code I am using

SELECT [CELESTICA PN], [PRICE IN USD]
FROM [Q4 2003 RFQ1]
UNION ALL
SELECT [CEL PART NUMBER], [PRICE IN USD]
FROM [Q2 2003 RFQ]
UNION ALL
SELECT [ALCATEL PN], [1H03 RESALE]
FROM [Q2 2003 ALCATEL ALTERA]
ORDER BY [CELESTICA PN];

The result I wanted was a spread sheet showing the cel pn for q4 with the price then price for q2 then price for alcatel all on one row if they matched.

is that possible

lee

 

[lespaul]

Depends on how all those tables are related. If you will give us some sample data from the tables, how the tables are related and what you want your final query results to look like, it may be possible.

Leslie

 

[LELOA]

Lespaul
Q4 2003 RFQ1 table
CELESTICA PN PRICE IN USD VENDOR PN COST COMMENTS
011-123-111 13.20 1258AAA1 8.50 PROGRAMMED
q2 2003 RFQ table
CEL PART NUMBER PRICE IN USD VENDOR PN COMMENTS
111-125-1225 25.34 11.22 AB12582 BLANK
ALCATEL CONTRACT table
ALCATEL PN PRICE IN USD VENDOR COST COMMENTS
111-123-111 12.20 11.50 BLANK
The tables are related by the part number
Result I would like spread sheet view showing all columns from q4 table and only pricing colums from other tables but, being able to see which table they are from. Maybe I should be using a cross tab query but, I tried and they want a value expression I don't know which or how to use.

Any help would be appreciated.

TKS

 

[lespaul]

So you want a query that takes that information from the three tables and shows it like:

PartNumber Q4.PriceInUSD Q2.PriceInUSD ALCATEL.PriceInUSD
011-123-111 13.20 12.20

But you only want the partnumber listed when it's in the Q4 table?

Is that right?

Leslie

 

[LELOA]

Leslie

In my perfect world I would like first and foremost the the ability to match the data in th Q4 table.

eventually I would like to do a form that when I input the cel part number it would get me all the pricing history.

Lee

 

[lespaul]

OK, try this:


SELECT [Q4 2003 RFQ1].[CELESTICA PN], [Q4 2003 RFQ1].[Price in USD], [Q2 2003 RFQ].[Price in USD], [ALCATEL CONTRACT].[Price in USD]
FROM [Q4 2003 RFQ1]
LEFT JOIN [Q2 2003 RFQ] ON [Q4 2003 RFQ1].[CELESTICA PN] = [Q2 2003 RFQ].[CEL PART NUM]
LEFT JOIN [ALCATEL CONTRACT] ON [Q4 2003 RFQ1].[CELESTICA PN] = [ALCATEL CONTRACT].[ALCATEL PN]

If this doesn't work, then we'll try it through the Design view.

Leslie