By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

SQL Getting values from 3 different tables

P: 84
Ok this is starting to hurt my head :)

I have a Invoice table that has Invoice_Id , Customer_Id and Product_Code

Invoice_Id is the primary key
Customer_Id is a foreign key
Product_Code is a foreign key

so im making a data report and i can get it to work with connecting an retrieving data from the invoice and customer table but when i try and get data from the product table it fails

this is what i have atm

InvoiceSQL = "SELECT * From Invoice INNER JOIN Customer ON Customer.Customer_Id = Invoice.Customer_Id WHERE Invoice.Invoice_ID=" & Txt_InvoiceID.Text & " INNER JOIN Product ON Invoice.Product_Code = Product.Product_Code WHERE Invoice.Product_Code =" & Txt_InvoiceProdCode.Text & ""

can anyone help ;)

btw im using vb6 and MS Access
Feb 3 '08 #1
Share this Question
Share on Google+
10 Replies


P: 84
I was looking back and i dontthink i need the

WHERE Invoice.Product_Code =" & Txt_InvoiceProdCode.Text & ""

at the end
Feb 3 '08 #2

P: 84
Still need help though :) cant go any further without sorting this ^^

sorry for being impatient
Feb 3 '08 #3

Expert 5K+
P: 8,434
Still need help though :) cant go any further without sorting this ^^

sorry for being impatient
Sorry, you're too impatient - I'm going to ban you for life... :D

Seriously, I'm going to move this thread to the Access forum, as you'll find much more SQL expertise there.

I believe it would help if you could copy and paste here the actual contents of the string passed to the SQL interpret (in other words, the srting built by the code you showed us).

And also, could you give a more detailed explanation of what "fail" means in this context? For all we know it might have retrieved the wrong data, retrieved nothing, returned an error, blown up the PC...
Feb 4 '08 #4

Delerna
Expert 100+
P: 1,134
OK
You can't have 2 different where clauses in the same query like that.

change it to something like this
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM Invoice 
  3. INNER JOIN Customer ON Customer.Customer_Id = Invoice.Customer_Id 
  4. INNER JOIN Product ON Invoice.Product_Code = Product.Product_Code 
  5. WHERE Invoice.Invoice_ID=" & Txt_InvoiceID.Text & " 
  6.        and Invoice.Product_Code =" & Txt_InvoiceProdCode.Text & ""
  7.  
Feb 4 '08 #5

P: 84
Hmm i seem to get this error


syntax error(missing Operator) in query expression 'Customer.Customer_Id = Invoice.Customer_Id INNER JOIN Product ON Invoice.Product_Code = Product.Product_Code'.
Feb 4 '08 #6

P: 84
Anyone got any ideas ?: D
Feb 9 '08 #7

Delerna
Expert 100+
P: 1,134
not from that ??
Are you sill using a dynamic sql string?
Have you tried SELECT @SQLString
and examined the contents to see if the query was constructed correctly?
Feb 10 '08 #8

Scott Price
Expert 100+
P: 1,384
Metalheadstorm,

Can you give us the SQL you are using now? Also, what version of Access are you working with?

You seem to be confusing the purpose of the WHERE clause, judging from your first posted sql statement. The WHERE clause does not provide criteria for the join, it provides criteria for the selection of the data.

Regards,
Scott
Feb 11 '08 #9

Scott Price
Expert 100+
P: 1,384
You're going to end up with something looking like this:

SELECT INVOICE.*, INVOICE.InvoiceID, *
FROM (Customer INNER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID) INNER JOIN Product ON Invoice.ProductID = Product.ProductID
WHERE (((Invoice.InvoiceID)=[Form1].[Text1]));


Regards,
Scott
Feb 11 '08 #10

P: 84
Oooo sorry i didnt see ther was some more replies, thats why i havent posted back yet , i will try that scot thx and try and sort this problem out :P
Mar 13 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.